3) Varying Customer Demand

A company may desire to see the range of potential outcomes based on its assumptions. In this

case, the "Scenario Manager" under the "What-If Analysis" menu is a helpful function. When

this function is selected, the following window will open up.

Problem:

A plow company is examining what its Net Income will be based on different levels of customer

demand for its services. The company does not charge a flat rate for the season, but bills

customers based on the number of times that it clears driveways. The facts are as follows:

3

o Revenue per Plowed Driveway = $50

o Variable Expenses:

o Labor Costs = $9 per driveway

o Fuel = $3 per driveway

o Maintenance = $2 per driveway

o Fixed Expenses:

o Insurance = $900

o Garage Rent = $1,300

The company is interested in what its Net Income will be under the following three scenarios:

o High Demand = 300 plowed driveways

o Most Likely Demand = 150 plowed driveways

o Low Demand = 90 plowed driveways

1) Build an Excel spreadsheet with the three scenarios outlined above by using "Scenario

Manager". Hint: You will have to first construct an income statement. Keep the tab

containing the income statement unhidden and call it "Question 3 - Income Statement".

Call the tab containing the scenario summary "Question 3 - Scenario Summary". Format

the "Scenario Summary" as per the "Criteria of a Useful Report". Keep in mind that the

formatting provided by Excel may not be the best way to present your results clearly to

management.

2) Based on these three scenarios, how would you summarize the company's prospects?

Include your answer in the workbook tab containing the scenario results.