Title: New Perspectives on Microsoft Office Excel 2003 Tutorial 9
1Microsoft Office Excel 2003
- Tutorial 9 Data Tables and Scenario Management
2Examine cost-volume-profit relationships
- Suppose you were the owner of a water store. An
advertising agency offers to guarantee that using
their ad campaign would increase the volume of
your business by 50 percent. - How could you decide if the ad campaign would be
worth the cost? - Or, suppose that you are the chairman of a group
that is considering beginning a charter school. - What kind of information would you need to figure
out if the school would be feasible? - In both of these cases, what you need is a
cost-volume-profit analysis, also called a CVP
analysis or a break-even analysis.
3What is a CVP analysis?
- A CVP analysis shows the relationship between a
business's expenses, volume of business, and
profit. - It shows how much volume the business needs to
break even, given the expenses that the business
incurs. - It also shows what happens to the profit if the
volume of business increases above the break-even
point, or decreases below the break-even point. - To do a CVP analysis, it is first necessary to
figure the expenses of the business. - Three types of expenses need to be considered
variable, fixed, and mixed.
4Expense types used for a CVP analysis
- Variable expenses are those that change with the
volume of business. For example, the cost of
renting a desk for each student increases as the
number of students increases. - Fixed expenses are those that must be paid,
regardless of the number of customers. For
example, at the school, the teachers' salaries
are a fixed cost that must be met, no matter how
many students there are - Mixed expenses are part variable and part fixed.
- Teacher expense is both a variable expense, based
on the volume of business, and a fixed expense,
in that once a teacher is hired, the salary must
be met regardless of the volume
5Calculate the income for a CVP analysis
- Once you have determined the expenses, you need
to calculate the income, based on the volume of
business. - In the case of the school, income would be the
tuition paid by each student multiplied by the
number of students. - Then, to find the CVP relationship, you would add
the fixed costs to the total variable costs and
compare that to the total income.
6An example of a total expenses chart
7An example of a revenue chart
8An example of a CVP chart
9Calculate profit or loss
- How can you calculate the profits from a
business? If you know - the fixed expenses
- the total variable expenses for a given volume of
business - the total revenue for that same volume of
business - All you have to do is add the expenses and
subtract them from the revenue.
10Calculate the net income
- For example, suppose that you want to figure the
profits from a charter school. You know that - you are going to have 60 students
- the total fixed cost per month is 25,000
- the variable cost per student is 500
- the tuition income per student is 1000
- To figure the monthly profit
- Calculate the total expenses (25,000 50060
55,000) - And the total revenue (100050 60,000)
- Then you subtract the expenses from the revenue
(60,000 - 55,000) to get the net income
(5,000)
11Use Excel to calculate net income
12Use a what-if analysis
- Suppose you think that it might be better for the
students if you limited the enrolment to 40. How
would you calculate the net income? - On the spreadsheet that you have set up to figure
the profits, change the number of students from
60 to 40, and recalculate. - The spreadsheet will show a net income of 5000.
- Thus, at only 40 students, the school would lose
5,000 per month. - This process of changing the value of a variable
in a calculation to see how the new answer
compares with the old answer is called a what-if
analysis.
13Use multiple what-if analyses
- If you are interested in doing several what-if
analyses (for example, you'd like to see what the
net income for the school would be for several
different enrolments), you could do it by
repeating the above analysis for each scenario. - That is, change the enrollment to several
different values, and see what effect this has on
the net income - Also, suppose that you would like to see what
would happen to the net income if you raised (or
lowered) the tuition. - You could do this by entering several tuition
amounts into the spreadsheet and noting the
resulting net income. - This is the principle of multiple what-if
analyses.
14Use one-variable data tables to perform a what-if
analysis
- Doing multiple what-if analyses is time-consuming
and tedious if you do it by hand. - You can ask Excel to do multiple what-if
analyses, and display the results in a table. - For example, you could ask Excel to show you how
the net income from the charter school varies as
the number of students changes from 30 to 70 - To set up a one-variable data table in Excel, you
first need to set up a spreadsheet that
calculates the result you are interested in,
based on a particular input. - Once you have the spreadsheet set up with the
proper formulas, you can proceed to create the
data table.
15An example of a one-variable data table
16Create a chart from a data table
- It is often easier to understand data if it is
displayed on a chart or graph. - Excel provides the capability of displaying the
cost-volume-profit data in a CVP chart. - If you ask Excel to plot the expenses and the
revenue against the volume, the result will be a
CVP chart. - To create the CVP chart, highlight the part of
the data table you want to chart.
17A CVP chart based on a data table
18Use two-variable data tables to perform a what-if
analysis
- Excel provides the capability to create
two-variable data tables, which allow two input
variables, and one result variable. - The data table will have the values for one input
variable across the top row of the table, and the
values for the other input variable down the
first column - Excel can create a data table that will display
the net income based on different tuition values
and different student counts. - As with a one-variable data table, to create a
two-variable data table in Excel, you must first
create a spreadsheet that calculates net income
based on the number of students and the amount of
tuition - Once the spreadsheet is set up, you can create
the two-variable data table
19An example of a two-variable data table
20Create a graph for a two-variable table
- Just as Excel can create a graph of a
one-variable data table, it can also create a
graph of a two-variable data table. - To do this, select the entire table.
- From the Insert menu, click Chart. Choose XY
(Scatter) and Scatter with data points connected
by lines without markers. - Follow the instructions on the Chart dialog boxes
to label the graph and the axes. - Tell Excel to put the graph on a new sheet.
- When you click Finish, Excel will display a graph
of the data table, with a different line for each
of the rafting fees, using the example table from
the previous slide.
21A CCP chart for a two-variable data table
22Use array formulas in Excel
- In Excel, an array is a set of cell ranges or a
collection of data values. - For example, B5B12 is an array of cell ranges
2,4,6,8 is an array of data values - Many Excel functions allow you to enter arrays as
arguments. - If you use an array argument in a formula, the
formula is an array formula. - Excel uses each value in the array to produce its
result.
23Create and use array formulas
- For example, using the Excel function SUM, you
could create the array formula SUM(B3B12C2). - To ask Excel to treat this as an array formula,
press and hold the Ctrl key and the Shift key
while you press the Enter key (rather than just
using the Enter key to enter the formula into the
cell). - This will cause Excel to put brackets around the
formula, so that it looks like this
SUM(B3B12C12). - Don't try to type in the brackets themselves if
you do, Excel will treat the formula as text - Excel will interpret the formula
SUM(B3B12C12) as a command to multiply each
of the values in B3B12 by the value in C12, and
calculate the sum of the values.
24Create formulas using multiple arrays
- Another example of an array formula using SUM is
SUM(B3B12C3C12). - Using this formula, Excel will multiply the first
element in the first array by the first element
in the second array, the second element of the
first array by the second element of the second
array, and so on, pair-wise through both arrays. - The two arrays must be the same size if not,
Excel returns an error message.
25Functions can display multiple values
- A few of the functions in Excel can display
multiple values when given array arguments. - Some examples are TABLE and TREND.
- Formulas that display several values must be
entered into a range of cells that has the same
number of rows and columns as the input array
arguments have.
26An example of Excels Trend function
27Create scenarios to perform what-if analyses
- To perform what-if analyses with more than two
input variables, you have to use scenarios, which
are - A set of values that Excel can put into a
worksheet - Created based on existing spreadsheets in Excel
- You use the Scenario Manager to set up and view
different scenarios. - Once you have the spreadsheet with one set of
values, you can create several scenarios with
different values. - As you view each scenario, Excel uses the values
in the scenario as input to calculate the
results.
28Use the Scenario Manager dialog box
- To invoke the Scenario Manager and begin creating
scenarios, start the Scenario Manager by choosing
Scenarios from the Tools menu. - In the Scenario Manager dialog box, click the Add
button, and enter a name for the scenario. - Enter the name for your first case, such as
Normal Case. - Type in the reference to each of the cells that
are going to change during the scenarios. - When you click OK, the Scenario Manager will
prompt you for a set of values, one for each of
the cells whose references you entered as
changing cells. - The original values will be entered. Since this
is the original case, you can accept these
values.
29The Scenario Manager dialog box
30Add additional scenarios
31View and edit scenarios
- When you have the scenarios defined, you can view
each one by selecting the name of the scenario
you want to see in the Scenario Manager dialog
box. - After you have selected the name, click Show and
then Close. - Excel will display the original spreadsheet, with
the values from the scenario you chose. - You can edit your scenarios from the Scenario
Manager - Select the scenario you want to edit, and click
Edit - This will bring up a dialog box in which you can
change any of the input values - You can then display the spreadsheet with the
values from the edited scenario
32Create a scenario summary report to save your
conclusions
- Using the Scenario Manager, you can display a
summary of the results from all of the scenarios
you have created. - This data can be displayed in a summary table or
a PivotTable. - To create a summary table based on the scenarios
you have created, open the Scenario Manager. - In the Scenario Manager dialog box, click Summary
to view the Summary dialog box.
33The Scenario Summary dialog box
34A Scenario Summary report
35Create a PivotTable report and chart
- To create a Scenario PivotTable report
- Open the Scenario Manager dialog box and click
Summary - From the Scenario Summary dialog box, choose
Scenario PivotTable report - Excel will create a Scenario PivotTable report
for all of the scenarios you have created - Excel can also chart the PivotTable. To do so
- Click on the Chart Wizard from the PivotTable
tool - From the Menu bar, choose Chart and select the
type of chart you want
36An example of a PivotTable report