Your Basic Guide to Creating a Financial Model
Your Basic Guide to Creating a Financial Model
Here’s a basic guide to creating a financial model. I will cut through the jargon to help you understand how to prepare a financial model for your business.
Let’s begin!
Historical results
Pull 3 years of financial statements and put them into Excel. Now, calculate items like AP days, inventory days, AR days, fixed costs, variable costs, gross margins, revenue growth rate, etc., to reverse engineer the assumptions for the historic period.
Prepare the income statement
Compute the income statement with gross profit, COGS, revenue, and operating expenses, including EBITDA. Don’t calculate the taxes, interest, amortization, and depreciation now.
Prepare the balance sheet
Calculate inventory day assumptions, AR days, inventory, and accounts receivable. Now, input accounts payable.
Create the supporting schedules
Creating a schedule for interest, debt, and capital assets such as PP&E is important before completing the balance sheet and the income statement. The PP&E schedule will subtract depreciation and add capital expenditures by pulling from the historical period. Similarly, the debt schedule will subtract repayments and add debt increases by pulling from the historical period. The debt balance on average, will decide the interest.
Finalize the balance sheet and the income statement
Complete the balance sheet and income statement using the data from the supporting schedules. The balance sheet links the closing debit balance from the schedules and the closing PP&E balance. The income statement links the debt schedule to the interest and the PP&E schedule to the depreciation. Then, calculate net income, taxes, and earnings before tax. Look at the closing balance of last year to complete shareholder’s equity. Subtract shares repurchased or dividends and add capital raised and net income.
Create the cash flow statement
Use the reconciliation method to create the cash flow statement. To obtain cash from operations, begin with net income, adding back depreciation and adjusting the non-cash working capital accordingly.
Do the DCF analysis
With the completion of the 3-statement model, you can start performing the business valuation and calculating free cash flow. The business’ free cash flow is discounted back to today at the firm’s capital cost.
Add scenarios and sensitivity analysis
Incorporating scenarios and sensitivity analysis within the model is your last step. This will help you assess the risk for business planning and investment.
Remember, compiling your financial model can be a little challenging, but it’s essential. If you have any doubts about creating a useful plan, don’t forget to consult a professional analyst who can collect your data and make sure that there are no mistakes in your projections and figures.
Get a custom business plan for PNP immigration, government grants, investors, and bank loans with Adept Business Plans
Adept Business Plans’ professional business plan writer helps established businesses and startups create customized financial, marketing, and business plans. Get in touch for more information about Vancouver business plan writer services!
