This is the fourth and last post for awhile on Excel modeling. Otherwise I will lose my tech readership. Readers have emailed me with questions about balance sheets, how to build balance sheets that are dependent on events or acquisitions and how to build self-balancing models. Let's start with how to build a self-balancing model first because it may help to answer the other questions. The diagram below may help.
From Excel Model Tips II we know that we should start every model by first defining the business model, the growth drivers and the assumptions related to those growth drivers. This work makes it very easy to then build the P&L. From the P&L we link the net income for the period to the net income for the same period in the net worth part of the balance sheet. This insures that net worth rolls forward properly. (Remember that retained earnings period 1 plus net income period 1 equals retained earnings period 2.)
Next we build out the rest of the balance sheet except cash. I usually use a days on hand method to build AR, inventory, AP and accrued expense. Plant, property and equipment is developed separately and usually comes from the business model, it either being a growth driver or directly correlated to a growth driver. I link in my financing from the sources and uses statement to model debt and equity, if any, and complete my balance sheet. (I usually model simple balance sheets and the effort goes into clearly showing the debt and the working capital components.) With everything on the balance sheet completed except cash I then prepare the cash flow statement. The ending cash for the period is then linked to the balance sheet cash for the same period. If you did the cash flow properly the ending cash value will create a balance sheet where total assets equals total liabilities plus net worth, which I guess is why they call it "self-balancing". Last step is to look at cash balances on the balance sheet to make sure that cash is sufficient in each period. If it is not, go back to the sources and uses statement and increase debt or equity. If sources are linked to the balance sheet debt and equity, the changes in them automatically flows through to increase cash and the final sources and uses statement defines the total financing need. Effectively, by managing the cash balances you determine the financing need. (Obviously there are constraints on the amount of debt based on EBITDA, cash flow and leverage (EBITDA/debt, debt/equity).
Now what we have seen so far is that the P&L basically drives the balance sheet in my model building method. So how do we handle uncertain events that affect the balance sheet? The answer is that we reflect them on the P&L and then they automatically flow through to the balance sheet in a linked model. How do we model uncertain events on the P&L? We use an on-off switch. For each event we use a 0,1 indicator where "0" means the event did not happen and "1" indicates that we expect the event to happen. Download the file to see a simple example. Download Event.xls (Assumptions are in Bold Blue.)
By building a matrix using various combinations of events and the financing required we would be able to determine the range of financing required for the planning period. Possible acquisitions would use the same indicator logic (0,1). You would build the model with each acquisition P&L and balance sheet on a separate tab. Each tab would have an indicator cell which would link to the revenue and expenses (and thereby to the balance sheet for the acquisition). Start the year with the acquisitions as "0" and turn them on when you close the deal. Build the assumptions for the acquisition with a close date for each acquisition and when you switch the indicator to "1" the P&L will start in the correct month. An example of this using event dates is in the model you already downloaded.
Download this file to see the simple balance sheet template I use. Download bal_sht.xls
In closing this four part series, remember to build your model once with assumptions so you do not have to rebuild it. 15 minutes to figure out the logic of assumptions is much better than an investor who can not do sensitivity analysis for a change in assumptions.
