A model is a simplification of the real world. It’s also a tool for problem solving
- 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
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.
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.
|Cost of goods sold||4543||5600||675||10818||3606|
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.
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.
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.
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