Integrated Financial Model
Introduction:
The integrated financial model combines information from 3 different financial statements (income statement, balance sheet, cash flow statement) to provide a single comprehensive view of an organization. The output is used to present and forecast a company’s overall financial situation.
Within the spreadsheet are some helpful calculations that show horizontal year-over-year variance, vertical analysis as a percentage of a base figure, and a scenario switching feature that projects different forecast assumptions.
Purpose and Goal:
This exercise began as a data extraction containing information on dates, financial items and amounts. The end goal for me was to become familiar with producing a 3-statement model from scratch and to include dynamic elements to forecast future results.
I found that having a “check” row under the balance sheet was key in making sure that the results of each column were balanced i.e. the assets and liabilities & equity subtotals are equal.
Problems and Explanation:
During this project, I ran into a few bumps along the way. One particularly difficult area was the organization and structure of the complete financial model. Because of the size of the model, I realized how important maintaining an organized structure would be.
I worked hard to keep formulas as reusable as possible to save time while maintaining the interconnectedness between statements. I recorded a macro for formatting every new sheet the same way, and used saved cell styles for consistency.
Lessons Learned:
I’m grateful for all the lessons I learned from this project. Some of the most important ones are the proper anchoring of cell references, creating custom cell styles, grouping columns with the shortcut “shift + alt + →”, and keeping a “check” row under the balance sheet for good measure.
As my first big project using excel like a financial analyst, I learned a lot of tips and tricks to improve my workflow. I can see how using the right formulas and shortcuts can make a big difference. I now spend a lot more time understanding the nitty gritty details of these formulas so that I can continue to produce more readable and scalable models.
