Decision support system : what-if analysis
Refer to Tutorial C, p. 106 – 126 of “Advanced Cases in
MIS” - Building a Decision Support System (DSS) in
Microsoft Excel.
Problem is: You are now in late 1999, you want to forecast
your company’s net income and cash flow in the next 2
years (2000 and 2001), based on current year’s (1999) data.
Use “what-if” analysis to do the forecast, in which we
consider 2 factors (to be treated as inputs in Excel):
• Economic outlook: optimistic or pessimistic
• Purchasing price (Cost) outlook: up or down
If the economic outlook will be optimistic but the
purchasing price will go up, then how will the net profit
and cash flow be in the next 2 years?
If the economic outlook will be pessimistic but the
purchasing price will go down, then how will the net profit
and cash flow be in the next 2 years?
Thus how will the cash flow and net income be affected in
next 2 years in each of the 4 situations (scenario or case)?
Economy is Economy is
Optimistic (O) Pessimistic (P)
Cost goes UP (U) U, O U, P
Cost goes DOWN (D) D, O D, P
1
A B C D
1 Tutorial C Exercise
2 1999 2000 2001
3 CONSTANTS
4 Tax rate expected NA 0.33 0.35
5 Number of Business days NA 300 300
6 NA
7 INPUTS
Economic Outlook
(O=optimistic; NA
8 P=pessimistic)
Purchasing price outlook
NA
9 (U=Up; D=Down)
10
11 Summary of key results
12 Net income for year NA
13 Year-end cash on hand NA
14
15 Calculations
16 Number of units sold/day 1000
17 Selling price/unit 7
18 Cost of goods sold/unit 3
19 Number of units sold/year NA
20
Income statement and
21 cash flow statement
Beginning of year cash
NA
22 on hand
23
24 Sales NA
25 Cost of goods sold NA
26 Pre-tax profit margin NA
27 Tax expense NA
28 Net income NA
29
End of year cash on hand
(beginning of yr. Cash,
30 plus net income for year) 10000
2
Excel spreadsheet: designed like an income statement.
• The input values entered (by users) for economic &
purchase price outlook in cells C8 and C9 are for both
years 2000 and 2001.
A B C D
7 INPUTS
ECONOMIC OUTLOOK (O = NA O NA
8 OPTIMISTIC; P = PESSIMISTIC)
PURCHASING PRICE OUTLOOK NA D NA
9 (U = UP; D = DOWN)
• If (economic outlook is optimistic),
Then units sold per day 6% of previous year
Else units sold per day ¯ 1% of previous year
Units sold per day should be integer (whole number),
INT() removes all decimal places (no round off).
C16=If($C$8=”O”, INT(B16*1.06), INT(B16*0.99))
Horizontal Copy & paste formula to right =>
D16=If($C$8=”O”, INT(C16*1.06), INT(C16*0.99))
Absolute addressing: placing $$ around C => $C$.
When copy & paste formula, $C$8 will not change to
$D$8.
3
A B C D
16 NUMBER OF UNITS SOLD/DAY 1000 =IF($C$8="O", INT(1.06*B16),INT(0.99*B16)) =IF($C$8="O", INT(1.06*C16),INT(0.99*C16))
• If (economic outlook is optimistic),
Then selling price 7% of previous year
Else selling price remains the same as previous year
Write Excel formula for cell C17 which is year 2000’s
selling price, and for cell D17, which is 2001’s selling
price.
• If (purchase price outlook is UP)
Then cost of goods sold per unit 25% of previous year
Else cost of goods sold per unit 1% of previous year
Write Excel formula for cell C18, which is year
2000’s CGS, and for cell D18, which is 2001’s CGS.
• Average number of units sold per year (Row 19): a
function of the number of business days and average
number of units sold per day.
• Beginning of year cash on hand = cash on hand at the
end of previous year. (Row 22)
• End of year cash on hand = beginning of year cash on
hand + net income of current year. (Row 30)
4
• Revenue from Sales of current year (Row 24) is a
function of the number of units sold per year and the
selling price per unit.
Note: Cents do not worth much and revenue is not
affected much by removal of cents.
• Cost of goods sold of current year (Row 25) is the
function of the number of units sold per year and the
cost of good sold per unit.
Note: Cents do not worth much and CGS is not
affected much by removal of cents.
• Pre-tax profit margin (Row 26) is the profit before tax.
• Income tax of current year (Row 27) is only paid on
positive pre-tax profit, and tax office does not consider
cents.
• Net income (Row 28) is the profit after tax.
• Both cells: C12 and C28 hold the same net income for
year 2000. Similarly, D12 and D28 hold the same net
income for year 2001.
• C13 and C30 hold the same year-end cash on hand for
year 2000. Similarly, D13 and D30 hold same year-
end cash on hand for year 2001.
5
Forecast Outputs:
Situation (scenario or case) 1:
• Optimistic Economy (C8=”O”) and
• Purchase Cost goes DOWN (C9=”D”)
A B C D
12 NET INCOME FOR YEAR NA 950248 1084852
13 YEAR-END CASH ON HAND NA 960248 2045100
Situation (scenario or case) 2:
• Optimistic Economy (C8=”O”) and
• Purchase Cost goes UP (C9=”U”)
A B C D
12 NET INCOME FOR YEAR NA 796845 728520
13 YEAR-END CASH ON HAND NA 806845 1535365
Situation (scenario or case) 3:
• Pessimistic Economy (C8=”P”) and
• Purchase Cost goes UP (C9=”U”)
A B C D
12 NET INCOME FOR YEAR NA 646718 441919
13 YEAR-END CASH ON HAND NA 656718 1098637
Situation (scenario or case) 4:
• Pessimistic Economy (C8=”P”) and
• Purchase Cost goes DOWN (C9=”D”)
6
A B C D
12 NET INCOME FOR YEAR NA 789991 752877
13 YEAR-END CASH ON HAND NA 799991 1552868
