In this post, I am going to tell you eight ways to audit a basic 3-statement financial model and explain briefly how this specific one works, so you may find it useful for your financial modeling skills. We can apply this to other 3-statement financial models you may use now and in the future, so I hope you find this useful (open the model in the following link).
https://drive.google.com/file/d/1pPnFF2zPomaivzTqeFkFQol28B6sohHQ/view?usp=sharing
First, this model basically works with some assumptions that have been calculated historical data extracted from the three financial statements. Those historical data from the three first periods are coloured in blue except the historical subtotals such as total current assets, EBIT, or net cash flows from operating activities. Moreover, you will need to do some additional calculations for the period specified in the last section about PP&E and depreciation, retained earnings, and other additional calculations you may include in your models such as intangibles and amortization.
Once you performed all historical calculations on historical ratios, assumptions, etc. you make the assumptions for the next forecast periods according to not only historical amounts but also the research you must do about the company (annual and quarterly reports, recent news, and other sources of information a financial analyst uses to adjust these values over time and thus update the 3-statement financial model). Therefore, this assumption part is critical to forecasting the future company results.
Finally, you calculate future numbers for every financial statement and the additional calculations section based on your past data and new assumptions for these forecast periods. Now I have explained basically how the model works, I will give you some examples to help you to audit your models that will also be helpful for other models with different assumptions from the ones shown in this example.
1. 1) Firstly, you need to check the cash amount in the balance sheet is the same as the one specified in the cash flow statement for every period. You may want to use some IF function to ensure both cells coincide.
2. 2) Secondly, do the same thing for Total Assets and Total Liabilities plus Shareholders´ Equity in the Balance Sheet for every period and ensure both amounts coincide.
3. 3) Go to Special function: you may find some hard-coded numbers that are wrongly set so that these cells should be coded as formulas. You can access this function by pressing F5, then “special in the bottom left corner, and you will display a panel where you must choose “formulas” and press “OK”. Consequently, you will notice which cells are set as formulas, so you ensure you do not have any cell set as a hard-coded number that does not change accordingly when it must do.
4. 4) Show formulas feature: in the formulas panel you will find this feature. By clicking it, you will see formulas in each cell instead of the result. Hard coded numbers cells will still be shown the same way, so you may also find this kind of mistake as by using the Go to special function.
5. 5) Excel options: you can also check inconsistencies on formulas by going to Excel options, then “Formulas”. You should always have “Enable background error checking” ticked and then you can choose more error checking rules below. These features will warn you with a flag in the top left corner of any cell that may have some kind of error according to the error checking rules you have chosen in the excel options panel. One that I personally recommend is the inconsistent formulas one because you may have made some mistakes while applying the same formula to several cells in the same region.
6. 6) Modify assumptions: make sure the model works properly by modifying assumptions and checking the model works properly. For example, you can temporarily change revenue growth to 40% so you can see your gross profit, net income, etc increases sharply over the forecast periods. You may notice the cash balance will decline a lot over time, perhaps even it turns into negative, so you modify other assumptions such as increasing the capital turnover ratio and reducing the dividend payout ratio to avoid a very low cash balance. This would seem to a new management policy scenario when the company decides to reinvest its revenues instead of paying dividends to shareholders. To sum up, you can change your assumptions in such a way that you test the model works and make sense instead of discovering some inconsistencies and errors.
7. 7) Trace precedents: you need to click the cash amount for the last forecast period in the balance sheet, which is basically the last calculation you will perform in this kind of model, and then click on formulas > trace precedents. If you click it just once you will notice the cell comes from the cash balance (end of the year) in the cash flow statement in the model example I shared with you. If you click trace precedents as many times as possible, you will be able to reach the point where the precedent chain cannot go further. You need to check the forecast period assumption arrows and ensure all assumptions are flowing into the three financial statement forecast calculations. You may also find some lines go in a different direction than everything else, so you may find some errors.
Lastly, in case you have any doubt about these audit techniques and how they work, contact me either via email or any other specified on the blog's home page. I hope you find these techniques useful, so you avoid as many mistakes as possible when building your models.
.jpg)
0 Comments