Spreadsheet Modeling

A model is a simplification of the real world. It’s also a tool for problem solving

Why Model?

• Models help us communicate
• Models allow us to clarify and test understanding
• Models create credibility and accountability
• Models help you organize your thoughts
• Models simplify and solve problems
• Models help you understand your data

Using Spreadsheets

You have probably used a spreadsheet to see what will happen when you change a value.

The sorts of things that you might have wanted to try out could be:

‘If the cost of food rises by 10%, how much will that decrease the profit we make for the school fete?”

“If I reduce the number of staff in my business by two, what effect will that have on the profit at the end of this month?”

Analysis works well in spreadsheets because all of the calculations have been set up using formulas. So, when you change one value e.g. food cost, all of the values related to it, e.g. food cost, total costs, profit will change automatically.

Why Spreadsheets

Spreadsheets are excellent for analysis because you can keep testing out different scenarios until you find one you are happy with.

For example, you could see what effect it will have on your profit if you dismiss one, two or even three staff.

You can keep adjusting the model until you get the result that suits your business.

At this stage, it is only numbers in a spreadsheet, you are not risking your business or upsetting your staff. But you will get an idea of what might happen if you go ahead and make the changes for real.

What If Analysis

What if analysis is a technique used to evaluate the effects of changing selected factors in a worksheet. This technique is a common accounting function that has made much easier with the introduction of spreadsheet programs. By substituting different values in cells that are referenced by formulas, we can quickly see the effect of the changes when the formulas are recalculated.

Particulars	Apr	May	Jun	Total	Average
Sales
Beverage	78000	234242	34543	346785	115595
Food	54353	23424	34535	112312	37437
Internet	45645	2300	67677	115622	38541
Total Sales	177998	259966	136755	574719	191573
Expenses
Cost of goods sold	4543	5600	675	10818	3606
Payroll	67800	4535	787	73122	24374
Computers	65765	30000	8766	104531	34844
Miscellaneous	786	78686	89888	35435	675
Total Expenses	138894	118821	100116	223906	63499
Income
Net Income	39104	141145	36639	350813	128074
Profit margin	22.0%	54.3%	26.8%	61.0%

To increase the profit margin, we will need to adjust the values in the sheet. After some considerations, we decide we can most easily reduce monthly payroll expenses by carefully scheduling employee work during these three months. Reducing the monthly expense will increase the profit margin for the quarter. We want to find out the maximum payroll value we can spend during that period is for each month to accomplish this goal.

To do this, we will enter different payroll expense values for each month and see what are effect is on that month’s profit margin. The process of evaluating what the effect is on that month’s profit margin. The process of evaluating what effect reducing the payroll expenses will have on the profit margin is called what-if-analysis.

Goal Seek

It took us several tries to find the payroll value tat would achieve the profit margin objective. A quicker way to find payroll value that will achieve the desired result is to use the goal seek tool. The Goal seek tool is to find the value needed in one cell to attain a result we want in another cell. Goal Seek varies the value in the cell we specify until a formula that is dependent on that cell returns the desired result. The value of only one cell can be changed.

We will use this method to find the payroll value for April that will produce a 25 % profit margin for that month. The current profit margin value is 22.0%. In the Goal seek dialog box we need to specify the location of the cell containing the formula to be solved, the desired calculated value, and the cell containing the number that can be adjusted to achieve the result. The set cell text box correctly displays the current cell as the location of the formula to be solved. To complete the formation needed in the Goal Seek dialog box, Click in the To Value text box and enter the value 25%

Click in the by changing Cell text box and then click on the cell in the worksheet to enter the cell reference to the required cell in the text box.

Scenarios

Excel’s Scenario Manager feature enables you to analyze your data to see how changing one or more values in the worksheet affects the other cells in the worksheet. This feature comes in handy for figuring out what would happen if certain factors in your business changed.

Creating Scenarios

In many cases, you use worksheets to perform what-if analysis. After you set up a series of calculations, you can change the values of certain cells to view different scenarios. For example, “What if I sold 15% more products this year? What if I reduce inventory? How would these changes affect my total income?” Being able to anticipate the effect of changes is what makes a spreadsheet so valuable.

The Tools, Scenarios option enables you to substitute one or more values with a range of values and observe how the new values affect the rest of the data in the worksheet. You can ask Excel, “What if the value changes?” and the Scenario Manager instantly shows the substitutions and their effects directly on the worksheet. For instance, perhaps you want to see what happens to your projected income if sales rise or drop, or if you increase or decrease inventory. You can use the Scenario Manager to enter all the possibilities.

Displaying a Scenario

Once you create a scenario, you’ll want to display it on your worksheet to be able to perform a true what-if analysis.

• Choose Tools: Scenarios… from the Menu bar
• Click the name of the scenario that you want to display
• Click Show

Create a Scenario Summary

There are two types of scenario summary:

• Tabular view: Shows each scenario variable and the result produced.
• PivotTable view: Shows just the scenario names and the result produced