Financial Modeling
Data Validation and Iteration
As analysts are bombarded with a lot off data, one should be able to check data veracity. This is where
the data validation functions come in handy. Few excel functions incorporate power of iterations.
Data Validation
It prevents invalid data entry in a worksheet.
o Select a cell.
o Go to Data tab, Data Tools group and click Data Validation.
o Validation tool can be selected from drop down list.
o On selecting the Data Validation from the drop down list, the dialog box named ‘Data Validation’ appears on the screen.
Data Validation-Example
In the data furnished below, there is a limitation while projecting the ‘Comission/Bonuses’. It should be whole number and should not be greater than 5000. handle the situation with Data Validation.
o Select a cell.
o Go to Data tab, Data Tools group and click Data Validation.
o Select Data Validation from the list. Fill the requirements in the dialog box.
o Go to Input Message tab and enter some message.
Actual
Projected
Employee Costs
110 Payroll
45328
60000
120 IRS/FICA/Wk comp/State/SDI
15997
25000
140 Retirement Plan
6249
8000
125 Commissions/Bonuses
2720
4500
Subtotal
70294
97500
Subcontractors & Services
201 Telecommunication Services
637
700
254 Advertising
40
Subtotal
677
700
Total
70971
98200
Data Validation-Example
o Select a cell.
o Go to Data tab, Data Tools group and click Data Validation.
o Select Data Validation from the list. Fill the requirements in the dialog box.
Iteration Functions – Goal Seek
Goal Seek works on the terms of cause and effect scenarios. It helps a user to see the effect on one item in the formula on the other.
This can be used when you know the outcome of a formula but don’t know one input in the formula.
Location of Goal Seek
o Go to Data tab, Data Tools group and click What-if Analysis.
o Select Goal Seek from the list.
o On selecting Goal Seek, a dialog box similar to one shown in right appears on the screen.
o In Set cell box we have to fill cell whose value we know and value of the cell has to filled in next box i.e. To Value.
o By changing cell should be the cell reference that changes to bring the desired output.
Goal Seek-Exercise
Calculate the EMI to be paid on a loan of Rs.1200 @ 2%/month for 6 months.
Formula:
Enter the formula as shown and copy the formula to rest of the cells in the column.
Final Outcome of the exercise.
Note:
End of Period balance becomes zero.
Iteration Functions – Scenario Manager
A scenario is a named combination of values that is assigned to one or more variable cells in a what-if model. Scenarios to forecast the outcome of a worksheet model.
Location of Scenario Manager
o Go to Data tab, Data Tools group and click What-if Analysis.
o Select Scenario Manager from the list. o On selecting Scenario Manager, a dialog box
similar to one shown in right appears on the screen.
The exercise is to see the effect of changes in Retail Price, COGS and/or Unit Seles Growth Factor on the profit of the company.
Scenario - Exercise
Copyright© - IMS Proschool Pvt. Ltd.
Steps:
o Click Add button on the dialog. o In the Add Scenario dialog box
(Shown in image), fill any Scenario name.
o In Changing cells box, write, B3:B3,B10.
o Press Ok.
Scenario Manager-Exercise
39
Steps:
o In the Scenario Values dialog box, fill the alternative figures for each of the particulars so as to see the effect of change. o Press Ok.
Copyright© - IMS Proschool Pvt. Ltd.
Steps:
o In the Scenario Manager, click Summary button.
o Press Ok.
Scenario Manager-Exercise
40
Steps:
o In the Scenario Summary dialog box, select Scenario summary radio button. o Select the Result Cells that shows the
effect of change (B15:I15). o Press Ok.
Copyright© - IMS Proschool Pvt. Ltd.
Final Output
Scenario Manager-Exercise
Iteration Functions – Data Table
A Data Table will show you, how by changing certain values in your formulas you can affect the result of your formula. Data Tables can store the results of many different scenarios in one table, so that you can analyse them to select which scenario is your best option.
Location of Data Table
o Go to Data tab, Data Tools group and click What-if Analysis.
o Select Data Table from the list.
o On selecting Data Table, a dialog box similar to one shown in right appears on the screen. o Follow the Example for better understanding
Build a Sensitivity Analysis for revenue growth and % of sales of margin.
Template for the analysis:
Data Table – Exercise
Formula: =N10-N12
Template for the analysis:
Data Table – Exercise
Shows sensitivity for % Growth of Revenues
Shows sensitivity for Cost Margins Reference to the cell that shows the effect of
changes in inputs. In this case, Gross profit of A09 (=N14).
Steps:
1. Row input cell (=N8) 2. Column input cell (=10) 3. Press OK button.
Final Output of Data Table:
Data Table – Exercise
Check:
For Revenue % Growth of 15% and Cost Margin of 70%, the Gross profit is 345.
Data Summarization – Pivot Table
It is a data summarization tool. It makes easier to arrange and summarize complex data for better analysis.
Location of Pivot Table
o Go to Insert tab, Tables group and click Pivot Table.
o Select PivotTable from the list.
o On selecting Pivot Table, a dialog box similar to one shown in right appears on the screen.
Pivot Table - Exercise
Pivot Table - Exercise
Steps:
1. Table Range A3:I25.
2. Click radio button ‘Existing Worksheet’. (One can use either option as per requirement)
3. Location , H7. (Can be changed as per requirement). Click ‘OK’.
4. A Pivot Area is created as shown
Steps:
5. Also a PivotTable Field List opens on the right side of worksheet. If it is not visible, then right click the Pivot Area and click ‘Show Field List’.
Pivot Table - Exercise
Steps:
6. Drag and drop the field from list to areas as shown in the image in the right.
7. Gives the following output in the Pivot Area as shown below.
Pivot Table - Exercise
Steps:
8. Color can be changed as per requirement. The final output of the pivot table which shows the year wise sale of products geographically.
