Financial Modeling from Scratch

Now that we have scratched the surface of financial modeling, I’m going to attempt constructing it from scratch. But I don’t worry. Infinite resources are out there on the web to advise on how to go about it. After doing a bit of data excavation, I’ve found a pattern among recommended step-by-step guides.

The first step is to collect historical data. This includes putting historical financial statements from the company’s last three to five years in an Excel spreadsheet to give some context and insight into the company’s trends from the last few years. See my last blog for a great course to help you master the basics of Excel, which is quite useful for financial modeling.

The next step is to analyze this data through the three financial statements you should know: income statement, balance statement, and cash flow statement. You can start with the income statement, which shows the company’s income and spending and whether or not the company is making a loss or profit in a period of time. To calculate the income statement, you should use Excel to calculate the company’s “revenue, gross profit, cost of goods sold, and operating expenses, which will show you the company’s earnings before interest, tax, and depreciation.” After the income statement, you should fill out the balance statement using Excel and calculate “accounts receivable and inventory and accounts payable, then input the data into the Excel balance sheet.” Finally, fill out the cash flow statement, a statement that indicates how changes in the balance and income statement affect cash and cash equivalents moving in and out of a company. Are you still with me?

Next, use the data to start making assumptions for forecasting using the statements you just generated. Predict the company’s future performance by calculating numbers such as their future growth margins. We can also prepare for forecasting using the current market scenario, which is more often utilized in more cyclical industries.

After making assumptions for forecasting and calculating some data, I think we’re ready to create a forecast, which involves making a future income sheet, balance sheet, and cash flow statement. Use the assumptions we’ve made to create these statements. WallstreetMojo also lists two things we should check at the end of this step:

“The value of the total asset should match with the summation of total liabilities and shareholder’s equity.

The cash balance at the end of the cash flow statement should be equal to the cash balance in the balance sheet”

Only a few more steps to go!

Next, let’s conduct an assessment of the company’s value and assess its future risks. To calculate the company’s values, we can use the Discounted Cash Flow (DCF) model, which is a method that calculates the value of a current investment using its future expected cash flows. If the DCF is greater than the current investment cost, the investment may have positive results and thus should be considered. To assess the future risks of the business, you should summarize the output of the entire model as well as comment on whether or not the company can take care of its debt and grow consistently in the near to medium term.

The second-to-last part of the financial model should be predicting at what point the company will begin experiencing a decline. Include various scenarios in the financial model to perform sensitivity analysis, which will provide insight into how the business’ performance might change based on different potential events.

The final step is very similar to the previous, only that it requires a stress test of the forecast to assume the worst-case scenario over a period of time, such as the 2008–2009 bank crisis: often used to stress test the forecast models of US-based companies. Another worst-case scenario that could be assumed is a pandemic, such as the still present and ever-annoying Covid-19. This step is important to forecast to predict if the company could survive under the worst-case scenario and how the company would perform in this scenario.

Even though there are only a few steps outlined here that pertain to financial models in general, it is clear that each step requires extreme precision and focus. That is why I know practice–and LOTS of it–will be the key to mastering this thing. I’m excited to explore various financial statements and my Excel skills can only get better from here on out. I believe these two fundamental components are all I need…for now.

See you again, real soon! :)