How to Make a Financial Model – Step by Step Guide

A financial model is a tool used to forecast a company’s financial performance. It helps companies make informed decisions about their future financial performance, investment decisions, and budgeting decisions.

Financial modeling is a vital skill for anyone seeking a career in finance, and this step-by-step guide will help you understand how to create a financial model.

Step: Define the Purpose of Your Financial Model

Before creating a financial model, it’s essential to define the purpose of the model. Defining the purpose will help you understand what data to gather and what assumptions to make.

For example – if the purpose of the financial model is to forecast a company’s revenue for the next year, you’ll need to gather data on the company’s past revenue, market trends, and other relevant information.

Step: Gather Relevant Data

Once you have defined the purpose of your financial model, the next step is to gather relevant data. The data you gather will depend on the purpose of the financial model.

For example – if the purpose of the model is to forecast a company’s revenue, you’ll need to gather data on the company’s historical revenue, market trends, and economic indicators.

You can find this data through various sources, such as company financial statements, industry reports, and market research reports.

Step: Create Assumptions

Creating assumptions is a crucial step in financial modeling. Assumptions are estimates that you make about the future based on the data you have gathered.

For example – if the purpose of the financial model is to forecast a company’s revenue, you’ll need to make assumptions about the growth rate of the company’s revenue, market trends, and economic indicators.

Make realistic assumptions that are based on historical data, industry trends, and the company’s unique circumstances. To create assumptions for the sample financial model, let’s assume the following:

• Revenue: We assume that the company will experience a growth rate of 20% in Year 1, 15% in Year 2, and 10% in Year 3.
• Cost of Goods Sold: We assume that the cost of goods sold will be 50% of revenue for all three years.
• Operating Expenses: We assume that operating expenses will be 25% of revenue for all three years.
• Depreciation: We assume that the company will use straight-line depreciation over five years for any equipment purchases.
• Capital Expenditures: We assume that the company will make capital expenditures of $100 in Year 1 and $50 in Year 2.
• Working Capital: We assume that the company will need working capital of 10% of revenue for all three years.
• Interest Expense: We assume that the company will have an interest rate of 5% on long-term debt and will have a long-term debt balance of $150 in Year 1, $180 in Year 2, and $216 in Year 3.
• Tax Rate: We assume that the company will have a tax rate of 25% for all three years.

Using these assumptions, we can build the financial model and forecast the income statement, balance sheet, and cash flow statement, as shown in Steps 4, 5, and 6 of the guide.

Step: Build the Income Statement

The income statement is a financial statement that shows a company’s revenues and expenses over a specified period. It’s important because it helps evaluate a company’s profitability and financial performance.

As you can see from the table we created, the income statement provides a comprehensive overview of the company’s financial performance over the three-year period based on the assumptions made in Step 3.

Particulars	Year 1	Year 2	Year 3
Revenue	$2,000	$2,400	$2,880
Cost of Goods Sold	$500	$600	$720
Gross Profit	$1,500	$1,800	$2,160
Operating Expenses
Salaries	$300	$360	$432
Rent	$150	$180	$216
Utilities	$50	$60	$72
Insurance	$50	$60	$72
Depreciation	$50	$75	$100
Total Operating Expenses	$600	$735	$892
Operating Income	$900	$1,065	$1,268
Interest Expense	$30	$30	$36
Earnings Before Taxes	$870	$1,035	$1,232
Taxes	$174	$207	$246
Net Income	$696	$828	$986

To build the income statement, we started by using the assumptions made in Step 3 for revenue, cost of goods sold, and operating expenses.

1. We subtracted the cost of goods sold from the revenue to calculate the gross profit.
2. Then, we subtracted the operating expenses from the gross profit to calculate the operating income.
3. Finally, we subtracted the interest expense and taxes from the operating income to calculate the net income.

Step: Build the Balance Sheet

The balance sheet is a financial statement that shows a company’s assets, liabilities, and equity at a specific point in time. It’s important because it helps evaluate a company’s financial position and solvency.

As you can see from the table we created, the balance sheet provides a comprehensive overview of the company’s financial position over the three-year period based on the assumptions made in Step 3.

Particulars	Year 1	Year 2	Year 3
Assets
Current Assets
Cash and Cash Equivalents	$370	$780	$1,364
Accounts Receivable	$500	$650	$700
Inventory	$200	$250	$300
Total Current Assets	$1,070	$1,680	$2,364
Property, Plant, and Equipment
Accumulated Depreciation	($50)	($125)	($200)
Net Property, Plant, and Equipment	$750	$625	$550
Total Assets	$1,820	$2,305	$2,914
Liabilities and Equity
Current Liabilities
Accounts Payable	$250	$325	$350
Short-Term Debt	$200	$100	$130
Total Current Liabilities	$450	$425	$480
Long-Term Debt	$270	$240	$204
Shareholders’ Equity
Common Stock	$500	$600	$700
Retained Earnings	$600	$1,040	$1,530
Total Shareholders’ Equity	$1,100	$1,640	$2,230
Total Liabilities and Equity	$1,820	$2,305	$2,914

As you can see from the balance sheet, the company’s assets, liabilities, and equity change from year to year based on the assumptions we made about the company’s financial performance.

To build the balance sheet, we started by using the assumptions made in Step 3 for the company’s assets and liabilities. We then calculated the shareholders’ equity by subtracting the total liabilities from the total assets.

Step: Develop the Cash Flow Statement

The cash flow statement is a financial statement that shows the inflows and outflows of cash for a specified period. It’s important because it helps evaluate a company’s liquidity and solvency.

As you can see from the table we created, the cash flow statement provides a comprehensive overview of the company’s cash inflows and outflows over the three-year period based on the assumptions made in Step 3.

Particulars	Year 1	Year 2	Year 3
Cash Inflows
Operating Activities
Cash Sales	$1,000	$1,200	$1,440
Accounts Receivable Collections	$0	$150	$216
Total Operating Inflows	$1,000	$1,350	$1,656
Investing Activities
Sale of Equipment	$50	$0	$0
Total Investing Inflows	$50	$0	$0
Financing Activities
Long-Term Debt Issued	$150	$0	$66
Equity Issued	$100	$100	$100
Total Financing Inflows	$250	$100	$166
Total Cash Inflows	$1,300	$1,450	$1,822
Cash Outflows
Operating Activities
Cost of Goods Sold Payments	$500	$600	$720
Operating Expense Payments	$250	$300	$360
Total Operating Outflows	$750	$900	$1,080
Investing Activities
Purchase of Equipment	$100	$50	$50
Total Investing Outflows	$100	$50	$50
Financing Activities
Long-Term Debt Payments	$30	$30	$36
Dividends Paid	$50	$60	$72
Total Financing Outflows	$80	$90	$108
Net Cash Flow	$370	$410	$584
Beginning Cash Balance	$0	$370	$780
Ending Cash Balance	$370	$780	$1,364

This table provides a comprehensive overview of the company’s cash inflows and outflows over the three-year period based on the assumptions made in Step 3.

To build a cash flow statement, we used the income statement and balance sheet we created in Steps 4 and 5, respectively.

1. We started by calculating the cash inflows and outflows from operating, investing, and financing activities.
2. Then, we added up the cash inflows and subtracted the cash outflows to calculate the net cash flow.
3. Finally, we added the net cash flow to the beginning cash balance to calculate the ending cash balance.

Step: Perform Sensitivity Analysis

Sensitivity analysis is a technique used in financial modeling to test the effects of changing assumptions on the financial performance of a company. Sensitivity analysis helps identify the most significant assumptions in a financial model and their potential impact on the company’s financial performance.

By changing one or more assumptions, you can see how sensitive the financial model is to those changes and determine the best course of action for the company.

Here’s an example of a sensitivity analysis table based on the case developed in the steps above:

Particulars	Year 1	Year 2	Year 3
Revenue	$2,000	$2,400	$2,880
Cost of Goods Sold	$500	$600	$720
Gross Profit	$1,500	$1,800	$2,160
Operating Expenses	$600	$735	$892
Operating Income	$900	$1,065	$1,268
Interest Expense	$30	$30	$36
Earnings Before Taxes	$870	$1,035	$1,232
Taxes	$174	$207	$246
Net Income	$696	$828	$986
Revenue Growth (10%)	$2,200	$2,640	$3,168
Revenue Growth (-10%)	$1,800	$2,160	$2,592
Cost of Goods Sold (10%)	$550	$660	$792
Cost of Goods Sold (-10%)	$450	$540	$648
Operating Expenses (10%)	$660	$808.5	$981.2
Operating Expenses (-10%)	$540	$661.5	$802.8
Net Income (10%)	$765.6	$912.6	$1,084.2
Net Income (-10%)	$626.4	$743.4	$883.4

In this table, we have tested the effects of changing assumptions on the company’s net income. We varied the assumptions for revenue growth, cost of goods sold, and operating expenses by +/- 10%, and calculated the resulting net income.

As you can see from the table, changing the assumptions had a significant impact on the company’s net income. For example, increasing revenue growth by 10% resulted in a net income of $765.6 in Year 1, compared to the original assumption of $696. On the other hand, decreasing revenue growth by 10% resulted in a net income of $626.4 in Year 1.

This information can help decision-makers evaluate the risks associated with different scenarios and make informed decisions.

Performing sensitivity analysis is important to understand the range of outcomes that could occur based on changes to the assumptions. It helps decision-makers evaluate the risks associated with different scenarios and make informed decisions.

Review and Refine

Reviewing and refining the financial model is an ongoing process. As new data becomes available, the assumptions used in the model may need to be adjusted. Reviewing the financial model regularly and refining it as necessary can help ensure that the model remains accurate and useful for decision-making.

When refining the model, do remember test it against actual results to determine its accuracy and make any necessary adjustments.

Conclusion

In summary, financial modeling is a critical tool used in the world of finance to forecast a company’s financial performance. It involves creating a mathematical representation of a company’s financial statements based on assumptions about the company’s financial performance. The financial model includes an income statement, balance sheet, and cash flow statement, which provide insights into the company’s financial performance.

In this step-by-step guide, we’ve walked you through the process of creating a financial model. We started by gathering data and assumptions, then built an income statement, balance sheet, and cash flow statement based on those assumptions. We also performed sensitivity analysis to test the effects of changing assumptions on the financial performance of the company.

Financial modeling is an ongoing process, and the model should be reviewed and refined regularly to ensure its accuracy. This can help decision-makers evaluate the risks associated with different scenarios and make informed decisions.

Whether you’re seeking a core finance job or just want to understand the basics of financial modeling, this guide provides a comprehensive overview of the process. With this knowledge, you can create a financial model and analyze the financial performance of a company with confidence.

Frequently Asked Questions