Mar 31, 2016

Every management wants to increase the company’s profit over time. How can they do that? Well, by setting scenarios. Management can look at company’s Revenue and Expenses and they can play with the figures to see which scenario will give them the highest return in profit.

You can create several scenarios with Microsoft Excel using Scenario Manager feature.

Let’s say we use the spreadsheet below to set scenarios.

1. Increase revenue
2. Decrease expenses

Question is: how can we increase the revenue and how can we decrease the expenses?

We can add more money in advertising expenses, say another $10,000 and because we will reach more people, the revenue should increase. By how much? Assume we did our research and we believe that each country’s revenue will increase by 9.95%. I will name this scenario Advertising Push.

In the second scenario I will decrease some expenses. We will assume that I did a little research and I did find cheaper:

• Liability insurance
• Rental property (this was a real case scenario years ago when HP company moved from North Sydney to Rhodes and they saved lots in rental)

We can also save in office expenses by reducing waste. The Other expenses type can also be reduced by eliminating unnecessary expenses.

First of all, we have to save the original figures so we can return to the original values.

To create scenarios, follow the steps listed below:

1. Select the Revenue and the Expenses numbers/values


2. Click the Data tab on the Ribbon
3. Go to Data Tools group
4. Click the What-If Analysis button
5. Click the Scenario Manager… button
6. The following dialog box will display:


To add our first scenario, click the Add… button. You will be faced with this dialog box:
In the dialog box displayed, type the scenario name Original Projections and add a comment as shown in the figure below.



Click OK. The next dialog box opens.




Click OK to save the original values. We have our first scenario.




To add the next scenario, follow the steps listed below:

1. Click the Add… button
2. Type Advertising Push for the scenario name
3. Add the following comment: An increase of $10,000 in Advertising will cause a 9.95% increase in each country’s Revenue
4. Click OK.
The Scenario Values dialog box opens. The Advertising value is in cell G8.
5. Change the G8 value from $30,000 to $40,000. This is an increase of $10,000 in Advertising.
6. Scroll all the way up in the Scenario Values dialog box in order to display the Revenue fields and add the 9.95% increase in Revenue values.



Note: If you type in the new 9.95% increased values, you will most likely need a calculator to add this increase to each revenue stream, which is time consuming. To force Excel to calculate the new increased values, follow the steps below:

1. In the Scenario Values dialog box, Click in front of the value for $D$6 cell reference and type the equal sign (=)
2. Click after 200000 and type the star (*) sign for multiplication
3. Type 1.0995 (the 9.95 % increase)
4. Do the same for the $D$7, $D$8, $D$9 and $D$10
Note: You can select the *1.0995, then copy and paste it after the other 4 cells containing the revenue values.
5. Your dialog box should look like the one below:



6. Click OK. You will get this dialog box:


Microsoft Excel makes the calculations for you.
7. Click OK again. We have the second scenario.



Now we will create the 3rd scenario.

1. Click the Add… button
2. Type Decreased Expenses for the scenario name
3. Add the following comment: I found cheaper rental of $40,000 and cheaper Liability Insurance for $170,000. We can reduces the Office Expenses to $7,500 by eliminating waste and the Other expenses to $2,000 (no more drinks on Fridays afternoons folks)



4. Click OK. Scroll down until you see the $G$7 field for Rent/Utilities
5. Change the value from 60000 to 40000
6. Change the value for $G$9 from 9000 to 7500 (Office Expenses reduction)
7. Change the value for $G$10 from 200000 to 170000 (Liability Insurance reduction)
8. Change the value for $G$11 from 5000 to 2000 (Other Expense reduction)
9. Click OK. You have the 3 scenarios listed.




To show the scenarios:

1. Select the scenario name you want to show, say Advertising Push
2. Click the Show button


This scenario gives us $ 253,560 in Profit. The Original Scenario Profit was $176,000
1. Select the Reduced Expenses scenario
2. Click the Show button




This scenario gives us $ 230,500 in Profit. So the Advertising Push scenario will produce the highest Profit for the company. The company’s management can now make a decision on which scenario to implement.

My next blog about Scenarios will show you how to create a professional Scenario Summary report that you can print out for the management.

How do your Excel skills stack up?   

Test Now