Microsoft Office Tutorials and References

In Depth Information

Exhibit 9.16
Scenario summary for mortgage problem

Padcha believes she would like to generate the highest interest deduction possible,

she may consider either scenarios E or F. If more modest interest deductions are

more appealing, then scenarios B and C are possible. Regardless, she has the entire

array of possibilities to choose from, and she may be able to generate others based

on the results she has observed, for example the Current Values shown in column D.

This ability to manage multiple scenarios is a very attractive feature in spreadsheet

analysis.

9.4.2 Example 2—An Income Statement Analysis

We now consider a slightly more complex model for scenario analysis. In this exam-

ple, we consider a standard income statement and a related set of scenarios that

are provided by a decision maker. The decision maker would like to determine the

bottom-line
(net proﬁt) that results from various combinations of input values. In

Exhibit 9.17 we can see that we have 7 input variables and each variable has two

possible values. This is not a particularly complex problem, but with a greater num-

ber of possible input values, this problem could easily become quite cumbersome.

The 7 input values represent standard inputs that are often estimated in
proforma

Income Statement analysis:

•

Sales Revenue

=

(Volume)(Price)

(percentage
4
)(Sales Revenue)

•

=

COGS

•

Variable Operating Expense

=

(percentage)(Sales Revenue)

•

Fixed Operating Expenses

•

Depreciation Expense

•

Interest Expense

4
The estimation of Cost of Goods Sold (COGS) and Variable Operating Expense as a percentage

(%) of Sales Revenue is common approach to estimation of Income Statements, but not an approach

without its detractors.

Search JabSto ::

Custom Search