add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Excel also offers a number of techniques called What-if analyses for calculating profitability and enhancing decision-making. This chapter will also cover:
© Performing sensitivity analysis by using Data Tables.
© Using Goal Seeking to find solutions.
© Using Solver to find the optimal solutions under constraints.
© Creating various economic models using Scenarios.
Sensitivity Analysis (Data Table)
Chapter 24, Loans, explained how to use a Table to create two-variable Data Tables. Table generates and displays the results of the calculation for monthly loan payments when one of the three arguments in the calculation is a constant and the other two are variable.
Example: See figure below - Calculating the Break-Even Point.
The Break-Even Point in a model is the result of the following calculation: the quantity of sales required to cover both direct and fixed costs. Any quantity that is sold above the calculated break-even point will yield net income; any quantity that is sold below the calculated break-even point will yield a loss.
Data regarding sales and costs have been input into the worksheet above in columns A:C. The results of the calculations appear in cell C15 and represent the loss from the sale of 3000 units.
Creating a Data Table
Enter the sales and cost data into the worksheet (according to the example).
1. Assign the following names: cell C6 – Sales, cell C13 – Expenses.
2. Enter the following formulas into cells F6:H6, according to the list below:
Cell
Insert this:
F6
Formula: =Sales
G6
Formula: =Expenses
H6
=F6-G6 (the difference between sales and expenses).
3. Enter different quantities of sales into range E7:E15.
4. Select the table in range E6:H15. Be careful to include the header row that contains the formulas, as well as the left column with the sales quantities.
5. From the Data menu, select Table.
6. In Row input cell, select cell B4.
7. Click OK.
View the results (in the graphic in the previous page). In the cell range F7:H15, formulas of the TABLE type appear. The break-even point in the example is between 4,000 and 4,500 units(the exact result is 4,348 units).
Adding a Break-Even Point
1. Select the range E6:G15 (without the formulas in column H).
2. Click the Chart Wizard option in the Standard Toolbar.
3. In Step 1 of 4, select the Standard Type tab. Under Chart type, select Line, and click Next.
4. In Step 2 of 4, click Next.
5. In Step 3 of 4, type Break-Even Point in the Chart title box. Type Units in the Category (X) axis box. Type Sales in the Value (Y) axis box. Click Next.
6. In Step 4 of 4, click Finish.
7. Select the chart, right-click and select Cut.
8. Select another worksheet in the workbook, and click Ctrl+V (Paste).
Modify and improve the chart as you wish.
Finding a Solution by Goal Seeking
By using the Goal Seek technique, you can compute an unknown value that produces the result you want. Goal Seek varies the value in one specific cell until a formula that is dependent on that cell returns the result you want. See the examples below which clarify this principle.
Calculating the sales required to achieve desired profits
Calculate the number of units you must sell of Item 1 in order to achieve net income of $50,000. The result is 30,713 units.
The Optimal Solution for Constraints: Solver
Using goal seeking, you obtained the answer to the question: how many units do you have to sell in order to obtain net income of $50,000.
Note that the calculation in this example was carried out without constraints. Suppose, however, that there are constraints. If you are limited in the number of units that you can produce or the sale price is fixed and cannot be changed, how would you solve the problem?
The add-in called Solver seeks and returns optimal calculations subject to constraints; that is, it takes into consideration limitations in unit numbers, sale price, and more. Note that Solver is not an integral part of Excel; rather, it is an add-in that you must install.
Installing the Solver Add-In in Excel Versions 97 and 2000
1. From the Tools menu, select Add-Ins.
2. Select the Solver checkbox, and click OK.
In Excel Version 2002
The Solver Add-in does not appear in the list of Add-ins.
1. From the Tools menu, select Add-Ins.
2. Click Browse to search for and select the Solver file; it is usually located in the following directory:
c:\Program_files\MicrosoftOffice\Office\Library.
3. Select the Solver add-in check box, and click OK.
Work Techniques With the Solver
Maximize net income according to the data in the economic model that was input into the worksheet. The constraints that you determine will influence the result of the calculation.
Setting Solver parameters:
1. Type into the worksheet the data and formulas according to the model in the figure above.
2. Select the cells that contain values without formulas. Assign them Names and highlight them. (This will later help you to locate these cells in the stage when you determine constraints for the Solver.)
Tip – Select only the cells which contain values
Highlight the area with data (without column A that contains text), click F5, select Special, select the Constants option, and click OK.
3. From the Tools menu, select Solver.
4. Enter the target cell in the Set Target Cell box, for example, D9.
5. In the Equal To section, select the Max option.
6. In the By Changing Cells box, select the data that Solver is allowed to change when it finds a solution. Note that the cells with values were already highlighted by you.
7. Click Add; the Add Constraint dialog box appears.
Adding constraints:
a. In the Cell Reference box, enter the address of the cell in which Solver will insert the new value. Select the relationship you want to add or change ( <=, =, >=, Int, or Bin ) between the referenced cell and the constraint.
b. Then enter the constraint—a number, cell or range reference, or formula—in the box to the right.
c. Click Add to add additional constraints or OK to finish.
8. Click Solver to find the optimal solution. The Solver Results dialog box appears.
9. To save the solutions as a scenario, click Save Scenario. See details on Scenarios later in thischapter.
10. Select the reports from the list in the Reports box that you want Solver to create.
Creating reports with Solver
The reports are created automatically in the new worksheets in the workbook.
Answer Report
The report displays the original numbers of the reports, in contrast to the results of the constraint values.
Limits Report
This report displays the maximum values of the variables without constraints.
Sensitivity Report
This report supplies information about the sensitivity level of the target cell regarding the constraint values.
Solver Options
In the Solver Parameters dialog box, click Options.
Selecting the various options in this dialog box changes the calculations and solutions offered by the Solver.
Tip
Too many constraints, or illogical constraints, may prevent Solver from finding a solution. If Solver does not find a solution, try these two troubleshooting techniques:
1. Limit the number of constraints or determine different ceilings for the calculations.
2. Run the solver on only one constraint at a time; after you receive one solution on a single constraint, then run Solver again on another constraint, etc.
Solver on the Internet
The Solver add-in appears in its current version from Excel version 5 and on. The add-in has not been updated. If, however, you need solutions to more complex problems, a more advanced and updated version of Solver is available (for a fee) from the manufacturer. The URL is www.solver.com.
Scenarios
You can save and display various scenarios for one economic model by changing the underlying premises of the scenario. Scenarios create summary reports and PivotTable reports for review of the various scenarios.
In the process of preparing a business plan, you assume that you will sell a certain quantity of the product that the company produces and sells. The business results of the company are derived from this assumption. Economic caution, then, requires that you present at least three forecasts (regarding the number of products you hope to sell) and the effect of these different forecasts on the business results. These forecasts, or scenarios, would be: pessimistic, average and excellent forecasts, sometimes also called best/worst scenario forecasts.
Adding Different Scenarios
A profit and loss forecast model of a company is displayed in the figure below. The company sells only one product, and the input data is based on past experience. The cells highlighted in gray in columns B and C contain values (without formulas). The rest of the (non-highlighted) cells in columns B and C contain formulas for calculating results. Column E contains the list of names that were assigned to the cells of columns B and C.
Tip
Make sure to define names for all cells that contain data BEFORE using Scenarios. See below for an explanation of the importance of defining names.
Step 1: Define Names
Trick to quickly define a name for a single cell. Select cell B3 and press Ctrl+F3. The Define Name dialog box will appear and will propose using the label from A3 - Direct_Cost - as the name for the cell. Click OK. Excel will replace any spaces or dashes with an underscore character (see a list of names created in column D above).
This trick will not work in rows 10:17 where the row labels are separated from the target cells by a blank column. In this case, copy A10:A17 to B10:B17. Use the following method to assign names. Then erase the temporary cells in B10:B17.
Trick to define names for many cells. After copying A10:A17 to B10:B17, select B10:C17. Press Ctrl+Shift+F3. In the Create Names dialog box, select the Right Column checkbox, and click OK.
Step 2: Create Scenarios
1. From the Tools menu, select Scenarios.
2. In the Scenario Manager dialog box, select Add.
Add an original scenario forecast
1. In the Scenario name box, type Original Scenario.
2. In the Changing cells box, select the cells of all the values/variables in the model, and click OK. The Scenario Values dialog box appears.
3. Enter new values for the changing cells, and click Add.
Add a pessimistic scenario forecast
1. In the Scenario name box, type Pessimistic Scenario.
2. In the Changing cells box, select the cells of all the values/variables in the model and click OK. The Scenario Values dialog box appears.
3. Enter new values in the changing cells (values that indicate a pessimistic scenario).
4. Click Add.
Add an optimistic scenario forecast
1. In the Scenario name box, type Optimistic Scenario.
2. In the Changing cells box, select the cells of all the values/variables in the model, and click OK. The Scenario Values dialog box appears.
3. Enter new values in the changing cells (values that indicate an optimistic scenario).
4. Click Add.
Enter values using a temporary formula
In the Scenarios Values box, enter a function or formula, and then click OK. The formula is now replaced by values.
Example: In the Average_income box (E2), enter the formula =B2*1.25 (growth of 25%). After saving the change, the value in the Average_income cell will be greater by 25% than the value in cell B2.
Updating data in a scenario
In the Scenario Manager dialog box, select the scenario, click Edit, and update the scenario values.
Show the scenario in the worksheet
In the Scenario Manager dialog box, select the scenario, and click Show. Note that in your worksheet, the scenario values replace the previous values in the cells.
Adding a Scenario icon to your toolbar
From the View menu, select Toolbar, select Customize, and then select the Commands tab.
Under the Tools category (in the Commands tab), select and drag the Scenario icon to your toolbar.
Reports
In the Scenario Manager dialog box, click Summary. The Scenario Summary dialog box appears.
Two types of reports are available for the scenarios you create:
© Scenario summary
© Scenario PivotTable report
Scenario Summary Report
1. Select the Scenario summary option.
2. In the Result cells box, select all the cells with summary formulas and calculations. If there are too many, select only the important cells: gross income, fixed costs and net income.
3. Click OK.
Note
Note how the names have replaced the cell addresses in column B in your sheet. The importance of defining names before creating your scenarios is apparent; without names, the reports are indecipherable. Column D contains the current values of the model. Saving the original values (column E) under the name Original Scenario is recommended in order for you to refer to them for comparison purposes and to enable you to reconstruct your original data if necessary.
PivotTable Report
1. In the Scenario Summary dialog box, select the Scenario PivotTable report.
2. In the Result cells box, select all the cells with summary formulas and calculations. If there are too many, select only the important cells: gross income, fixed costs and net income.
3. Click OK.
Printing Scenarios
Use Report Manager to print all the scenarios in succession. See the Report Manager section of Chapter 11, Printing.
1. Select the Scenario area in the sheet, and define correct printing options for the selected section.
2. From the View menu, select Report Manager.
3. Click Add. The Edit Report dialog box appears.
4. In the Report Name box, type a name for the report.
5. Under Section to Add, select the Scenario check box.
6. Select the first scenario on the list, and click Add; it now appears in the Sections in this Report box. Continue to select and add all the scenarios in the drop-down text box until they all appear in the Sections in this Report box.
7. Click OK.
Printing the scenarios:
Open Report Manager (under View). Select the scenarios, and click Print. Each report is printed on a separate page.