1. Financial modeling in Excel
We will set up a model to analyze the financial performance of Pink Elephants, a fictional start-up that paints elephants pink and sells them to zoos.
Notice that these cells are references to another sheet. We can see the green font is referencing the income statement worksheet, which is the last 12 months of financial data for Pink Elephants.
We need to calculate our subtotals to keep track of our model's financial performance. Remember that net income is revenue minus expenses. Since expenses are already negative, we can just add them. We can also use the SUM function to sum separate ranges by using a comma in between the cell references.
In the footnotes of the income statement, Pink Elephants notes that they sold two elephants last year. We’ll create a new line item to record this information, and we’ll format it in blue since it will become an input. We can use that information to find the average income and cost for each line item.
Now we can start forecasting total sales by multiplying the units sold by the average sales and delivery fees. Let’s assume that the sales price and delivery fees stay the same for the next 5 years. We’ll reference the numbers here and make sure to lock the cell reference so it doesn’t move.
Now, let’s assume that sales double each year. We’ll make sure to format these in blue since these are inputs.
We can calculate the sales and delivery fees earned and drag the formula across for each year.
Most models have a section with assumptions that allow us to easily adjust different parts of the equation. Named ranges are a great tool to use in financial modeling, as they can take the place of referencing a cell.
To create a named range, go to the formula ribbon bar, then select define name. We type a name and make sure the correct data range is selected and click OK. To review the named ranges in the workbook, go to the Name Manager.
Now we can use this cost-growth rate assumption to increase our costs over time. To use a growth rate, we should multiply the starting value by one plus the growth rate. We can reference our named range by beginning to type it in. See how it appears.
We can now drag this formula across and down, and there we go!
Let’s finish calculating our total expenses and net income by copying our SUM formula across.
Let’s add a dynamic element to our model. Let’s say that we want to find the net income of the specified year here. We can use HLOOKUP to match the year here with the year in the cash flow model. We’ll select our array. HLOOKUP stands for horizontal lookup, so it will return the row that matches on our column. For example, we can see that when we lookup the value 4, this matches on column F and will therefore return the 20th row in column F. If we change the year input to 3, then this would match to column E and return the 20th row in column E.
It’s your turn to give it a try!
2. Let's practice!