Oct 19, 2015

Analysing data in Microsoft Excel and creating different scenarios for management will allow them to choose the best case scenario, the one that will provide the company with the highest financial return.

Let’s take the following data to analyse.

This spreadsheet lists the Revenue and Expenses of a company for the year ended 2014. The profit at 30 June 2020 was $238,500. A business analyst is searching for different ways to increase this profit.

There are a few way to do just that:

1. Increase the Advertising expense to $70,000 and since we can reach more people, we assume that all the revenues will increase by 10%
2. Decrease some expenses based on our research:
• Rent: $45,000
• Office Expenses: $10,000
• Liability Insurance: $180,000
• Other: $5,000

The steps to create those scenarios are:

1. Save the Original Report:
• Select the C4:C9 and also F4:F8 cells ranges
• Go to Data tab Ò Data Tools group
• Click the What-If Analysis button
• Click the Scenario Manager button



• Click the Add button
• Type 2014 Report in the Scenario name
• Click OK twice

1. Create the Second Report:
• Click the Add button
• Type Advertising Push Report in the Scenario name
• Add the following comment: An increase of $10,000 in Advertising will cause a 10% increase in each revenue stream
• In the Scenario values, change the $C$6 value to 70000



• Scroll down in the list until you see the F4 cell on top of the list
• Type = (equal) sign in front of the 390000 value
• Type *1.1 after the 390000 value (this is the calculation for increasing the revenue by 10%)



• Select *1.1 and copy it
• Paste *1.1 after each of the other revenue amounts
• Type the = (equal) sign in front of the other value (by doing so, Excel will calculate the 10% increase for us)
• Click OK. The following message will display:



• Click OK. Now we have the second report.

1. Create the Third Report:
• Click the Add button
• Type Decreased Expenses Report in the Scenario name
• Add the following comment:
1. Reduce the following expenses
2. Rent: $45,000
3. Office Expenses: $10,000
4. Liability Insurance: $180,000
5. Other: $5,000
2. Click OK
3. In the Scenario values, change the value in $C$5 to 45000
4. In the Scenario values, change the value in $C$7 to 10000
5. In the Scenario values, change the value in $C$8 to 180000
6. In the Scenario values, change the value in $C$9 to 5000



7. Click OK

Now we have three scenarios.

The 2014 Report shows $238,500 in Profit. To display the Advertising Push Report results, select it from the list of available reports and click Show button. This report shows $357,500 in Profit.

Display the Decreased Expenses Report. This report shows $311,000 in Profit.

To show the three scenarios next to each other for comparison purposes, click the Summary button and then OK. Excel generates an overall report showing

This Summary report would be great if the reader of the report could see what information is included in the Changing Cells column. To do that, we have to name the ranges containing the changing cells. So:

1. Go back to the original data (click the 2014 worksheet tab)
2. Select the following ranges:
• B4:C9
• E4:F8
• B11:C11
• E11:F11
• B13:C13



3. Go to Formulas tab –> Defined Names group
4. Click the Create from Selection button
5. Click OK
6. Go to Data group –> Data Tools group
7. Click the Click the What-If Analysis button
8. Click the Scenario Manager button
9. Click the Summary button
10. Click OK

Now the report clearly shows the contents of the Changing Cells.