Workplace_Software_and_Skills_-_WEB_IlfJtcP-161

Prévia do material em texto

Figure 11.60 The z-Test function is used when datasets are known to be normal. (Used with permission from Microsoft)
You will need to select the two ranges for analysis and include a Hypothesized Mean Difference if desired. In
addition, you will need to enter the variance for each of the variable ranges. If you don’t already know the
variance, you can use the Descriptive Statistics tool previously discussed or the variance function in the Insert
Function library to obtain it. The variance you enter must be a number, not a cell reference. Check the Labels
box, if appropriate, and then click OK. The output obtained from the z-Test will be much like the output for the
t-Tests.
The Solver Add-in
The Solver add-in offers additional ways to analyze a dataset. This section will just cover the basics, but you can
research the tool further if you want to know more about Solver and its uses for data analysis. Solver appears
with Data Analysis on the Data tab (Figure 11.61).
Figure 11.61 The Solver add-in is found on the Data tab. (Used with permission from Microsoft)
The Solver tool allows you to change levels in a dataset according to specific constraints you set up (Figure
11.62). Then, you can see the various solutions under those constraints. This is a helpful tool if you have
multiple variables within a dataset. The output provides an optimal solution with the dataset given the
constraints you establish.
11.2 • Statistical Functions 791
Figure 11.62 The Solver tool allows you to change levels in a dataset according to specific constraints. (Used with permission from
Microsoft)
To use the tool, you need to determine the target cell for optimization and the criteria (maximum, minimum,
or a specific value). Then, you add the various constraints to the dataset. You can add up to 100 constraints to
the Solver tool. Solver will find a solution to meet the criteria for the target cell assuming there is a solution.
You can apply the solution to your dataset by choosing “Keep Solver Solution” with the output. There are many
applications in business for Solver. For example, you could use the tool to determine the optimal level needed
to minimize costs or maximize revenues in a business. You can use it to find the optimal solution for
allocations in a retirement fund to enable you to retire at your desired age. You can also use it to determine
the conditions necessary to pay off a mortgage loan in half the time. Take a look at the data in the "solvsamp"
tabs of the downloadable Chapter 11 data file (https://openstax.org/r/78Ch11DataFile) ("solvsamp1-7") for
some great examples of Solver and applications of the tool to various problems.
SPOTLIGHT ON ETHICS
Data Integrity
There are many ways that information and data can be manipulated to tell a different story from what is
factual or to influence your interpretation of the information to slant a certain way. Simple changes such as
adjusting the scaling on a chart or adding formatting changes to numbers can alter the way people
interpret the information. For instance, you may want to show a larger than actual impact. You might
decide to use a baseline number other than zero along the x-axis to show a much larger trend in the data
than what is real. Or, perhaps you can choose to emphasize information and impacts by using very small
units of measure. With today’s endless access to data, the influence of the media, and the ever-present
“fake news” online, it is important to be diligent about making sure that data is accurately reported,
analyzed, and presented to the intended audience.
792 11 • Advanced Excel Spreadsheets: Statistical and Data Analysis
Access for free at openstax.org
https://openstax.org/r/78Ch11DataFile
11.3 What-If Analysis
Learning Objectives
By the end of this section, you will be able to:
• Use Scenario Manager to evaluate different variables
• Determine a desired result using Goal Seek
Having a backup plan is helpful when things do not go as expected. The plan offers an alternative course of
action to pursue in case the first path does not work out. In business, this practice is called contingency
planning, and it is essential for the business to be successful. This planning process can involve investigating
alternatives if sales are lower than expected. It can involve planning for the eventual retirement of key
personnel in the business. It could also address items such as the long-term strategic direction of the
company. The contingency plan is a proactive attempt to anticipate some situations that might be different
from your expected course of action and that might negatively impact the success of your business. Having
this plan in place certainly does not guarantee that nothing outside of the plan will happen, but it helps a
business to deal with specific situations they have identified as well as some degree of uncertainty. You cannot
always plan for the unexpected, such as natural disasters or shifts in consumer preferences, but there are
some things that you can plan around. For example, the business can have a plan in place in the event sales
are lower than expected. This plan could involve budget cuts or enhanced marketing efforts to boost sales.
There is a process to developing a contingency plan for a business. These steps might also be helpful in your
personal life as you plan for your future career. The first step is to list the risks or the events that have some
uncertainty associated with them. For example, if you are unsure of what the utility costs might be for a new
building, that could be a risk for your business. Some areas where a business may experience risks are
unplanned issues with time or schedule, cost adjustments, availability of resources, or technical problems.
Next, you will need to rank the risks according to the impact to the business. For example, if your business is
an e-commerce site, a technical issue that crashes your website would be a significant priority over increased
office supply expenses. Next, you need to set a plan for each event that you have identified. Each plan should
be detailed enough to provide an alternative course of action for the business. Your business should revisit the
contingency plan often and make any adjustments necessary, based on any changes in the business. There are
many templates online that can help you with contingency planning in business. Some Excel templates use
formatting to make the plan easy to understand and professional so that you can easily share it with others in
the company.
Using Scenario Manager
The Scenario Manager tool in Excel provides a way to look quantitatively at contingency planning. It allows the
user to change attributes without altering live data to examine the resulting impact on key variables. You can
change up to 32 cells and view the results of those changes all at the same time. This feature can be quite
helpful for contingency planning. For example, you might already have metrics that identify the best- and
worst-case scenarios for sales. Using this range and the Scenario Manager tool, you can look at the impact to
the overall profit picture for the business. You could also use the tool for planning your personal finances. You
could investigate the impact of increasing your monthly mortgage payment on the time it will take you to pay
off the loan. The tool can be instrumental in helping a business navigate multiple courses of action to
determine the best approach moving forward. You can also merge the scenarios from a single or multiple
worksheets together for more in-depth analysis.
What Is a Scenario?
The Scenario Manager is part of the What-If Analysis tools suite in the Forecast command group. The tool is
accessed from the Data tab and is in the Forecast command group (Figure 11.63).
11.3 • What-If Analysis 793
Figure 11.63 The Scenario Manager tool provides a way to look quantitatively at contingency planning. (Used with permission from
Microsoft)
A scenario determines the values that Excel will use to changethe input cells. You can save different scenarios
with different values to view the impact on your target cell(s) at the same time. When you choose Scenario
Manager, an input window will appear (Figure 11.64). From here, you will set the changing cells that define the
scenario and the target cell for the results.
Figure 11.64 To use the Scenario Manager, first set the changing cells and target cell. (Used with permission from Microsoft)
Let’s begin with a simple example of obtaining a home mortgage. The initial mortgage is for $150,000 for 30
years with an interest rate of 5 percent. You want to find a better interest rate and see the impact that rate will
have on your payment and the total amount you will pay. Using the PMT (payment) and FV (future value)
functions from the Function Library, you can quickly determine the status of the loan (Figure 11.65). With these
values at the current rate of 5 percent, you will pay just over $800 per month and at the end of the life of the
loan, you will have paid nearly $260,000. The functions by default return a negative number for the results, as
indicated by the parentheses and the red font color. You will need to adjust the equation to convert the
number to a positive number. You can either put a “-” in front of the function or use the ABS function at the
beginning of the formula to use the absolute value of the number. You can use the Scenario Manager to
investigate different interest rates and different payment terms if you decide you want to pay the loan over 15
years or a time frame other than the standard 30-year mortgage term. You will use the same mortgage
example to investigate different levels for the interest rate and repayment terms.
794 11 • Advanced Excel Spreadsheets: Statistical and Data Analysis
Access for free at openstax.org
Figure 11.65 The functions by default return a negative number for the results as indicated by the parentheses and the red font
color. (Used with permission from Microsoft)
Adding and Deleting a Scenario
Using the mortgage example, you can create a scenario to look at the impact of lowering the interest rate on
both the payment and the total amount paid for the loan. First, to add a scenario, go to the Data tab, choose
What-If Analysis, and select Scenario Manager. Click Add and the Add Scenario dialog box will appear (Figure
11.66). Here, you will define the changing cells and name the scenario. You can name this scenario “rate
changes.”
Figure 11.66 To add a scenario, click Add in the Scenario Manager window and define your scenario in the dialog box. (Used with
permission from Microsoft)
Next, select the cell you want to change. To change the rate, you would select cell A3. The Scenario Manager
does allow you to add more than one cell in the scenario. In this example, you will also change the length of
time for the loan. Select A3:B3 for Changing cells. You can add comments if desired to explain the scenario,
such as details about the reason for the selected ranges and other relevant information. Then, click OK. Next,
you will define the levels or range of values that Excel will use to change the value in cells A3 and B3. Assume
you want to compare the impact of a rate higher than the original 5 percent and a loan term shorter than the
original 360 months to the impact of a lower rate and longer loan term (Figure 11.67). Add two separate
scenarios at the various levels for each cell, A3 and B3. Click OK. This will take you back to the Scenario
Manager window. Add the other scenario in the same way.
11.3 • What-If Analysis 795
	Chapter 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
	11.3 What-If Analysis