Financial Model Template

Overview

Company Name Company X

Financial year end 31-Mar
Period 2017 - 2021
Currency NZ$

The model uses Revenue, Cost and Cashflow assumptions (inputted in the 'Assumptions' and 'Cashflow' tabs) to create Profit & Loss and Cashflow summaries.
Additional notes supporting the assumptions are provided at the bottom of the 'Assumptions' and 'Cashflow' tabs.

Three tabs:
Assumptions We have prepared this template to provide a helpful starting point.  However, we are not providing it as formal business or legal advice and do not represent that it is fit for
your particular purpose.  NZTE is not responsible for any loss or damage arising from the use of this template.
Profit & Loss
Cashflow

Instructions for user

- Only change cells which are formatted: xxx

- Insert assumptions in the 'Assumptions' tab and the 'Cashflow' tab
- Provide supporting notes in the 'Assumptions' tab and the 'Cashflow' tab

A financial model is a tool used to forecast a company’s financial future. Here’s a handful of tips for you to consider.
Guidance notes

1. A financial model should enable a manager and/or investor to clearly identify:

a. Key revenue drivers and assumptions. A financial model should provide granular revenue information on the business’ current and future
revenue projections – e.g. volumes and prices by product, customer and/or geography. The more granular the breakdown, the more information
an investor has to make an informed valuation decision. Commentary should be provided regarding the key ‘step-changes’ in the model e.g. new
product launches and new markets etc.
b. The cost assumptions supporting a company’s growth strategy. The cost assumptions need to align with the company’s revenue projections
e.g. if a company is forecasting to generate revenue in a new market, there should be an increase in labour costs leading up to the step-change in
revenue. Generally, company’s incorrectly forecast the costs required to support large revenue increases – such as, research & development, sales
& marketing, administration etc. Costs should be separated into variable costs and fixed overheads and represent the costs of getting a company’s
product to the customer.
c. Potential cashflow shortfalls and capital requirements. A financial model should track a company’s cash balance, it’s cash conversion cycle and
identify when potential cashflow shortfalls may occur. This also helps identify and address/plan capital requirements.
d. Use of funds raised. For each capital raise, a financial model needs to clearly show how the invested capital is going to be used (which is
consistent with investment documents).
2. A financial model must align with a company’s business model and the long-term strategy of the business that is articulated in the investment
documents – e.g. New product launches and new markets must be reflected in both the model and supporting documents.
3. A financial model should be structured so a user can easily change key assumptions – such as volume and price forecasts – so they can see how
these assumptions affect a company’s growth prospects.
4. A financial model must be well presented, summarised and easy to use and update. For key assumptions, it is useful to record the source of the
assumption and when it was last updated.

The table, formulas and figures in the model are for guidance purposes only and are not intended to be prescriptive or relied upon. If you are unsure about
the inputs, outputs or the model itself please seek professional advice.
Company X
Assumptions 2017 2018 2019 2020 2021 Notes
Inputs
Revenue
Market 1
Product 1
Units # 200 500 1,200 3,000 7,000 1
Price NZ$ $ 500.00 $ 525.00 $ 551.25 $ 578.81 $ 607.75
Revenue NZ$ $ 100,000 $ 262,500 $ 661,500 $ 1,736,438 $ 4,254,272
Product 2 3, 4
Units # 5 100 250 2,000 4,000 1
Price NZ$ $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 1,000.00
Revenue NZ$ $ 5,000 $ 100,000 $ 250,000 $ 2,000,000 $ 4,000,000
Market 1 units # 205 600 1,450 5,000 11,000
Market 1 Revenue NZ$ $ 105,000 $ 362,500 $ 911,500 $ 3,736,438 $ 8,254,272

Market 2 2
Product 1
Units # 100 250 1,200 6,000 14,000 1
Price NZ$ $ 400.00 $ 500.00 $ 550.00 $ 600.00 $ 700.00
Revenue NZ$ $ 40,000 $ 125,000 $ 660,000 $ 3,600,000 $ 9,800,000
Product 2
Units # 10 120 250 500 1,000 1
Price NZ$ $ 800.00 $ 1,000.00 $ 1,200.00 $ 1,200.00 $ 1,200.00
Revenue NZ$ $ 8,000 $ 120,000 $ 300,000 $ 600,000 $ 1,200,000
Market 2 units # 110 370 1,450 6,500 15,000
Market 2 Revenue NZ$ $ 48,000 $ 245,000 $ 960,000 $ 4,200,000 $ 11,000,000

Product 1 Revenue NZ$ $ 140,000 $ 387,500 $ 1,321,500 $ 5,336,438 $ 14,054,272

Product 2 Revenue NZ$ $ 13,000 $ 220,000 $ 550,000 $ 2,600,000 $ 5,200,000
Other Revenue NZ$ $ 5,000 $ 5,000 $ 5,000 $ 5,000 $ 5,000 5
Total Revenue NZ$ $ 158,000 $ 612,500 $ 1,876,500 $ 7,941,438 $ 19,259,272

Revenue by product

$25,000,000

$20,000,000 $19,259,272
NZ$

$5,200,000
$15,000,000

$10,000,000
$7,941,438

$2,600,000 $14,054,272

$5,000,000
$1,876,500 $5,336,438
$612,500 $550,000
$158,000 $1,321,500
$220,000
$- $13,000
$140,000 $387,500

2017 2018
Product 2 Revenue 2019
Product 1 Revenue 2020 2021

Revenue by market

$25,000,000

$20,000,000 $19,259,272
NZ$

$15,000,000
$11,000,000

$10,000,000
$7,941,438

$4,200,000
$5,000,000
$8,254,272
$1,876,500
$158,000 $612,500 $960,000 $3,736,438
$- $48,000
$105,000 $245,000
$362,500 $911,500
2017 2018 2 Revenue
Market 2019 1 Revenue
Market 2020 2021
 $10,000,000
$7,941,438

$4,200,000
$5,000,000
$8,254,272
$1,876,500
$158,000 $612,500 $960,000 $3,736,438
$- $48,000
$105,000 $245,000
$362,500 $911,500
2017 2018 2 Revenue
Market 2019 1 Revenue
Market 2020 2021

Production costs 6

Production costs - Product 1 (% of Revenue) % 40.0% 40.0% 30.0% 30.0% 30.0%

Production costs - Product 1 NZ$ $ 56,000 $ 155,000 $ 396,450 $ 1,600,931 $ 4,216,282
Production costs - Product 2 (% of Revenue) % 30.0% 30.0% 30.0% 20.0% 20.0%
Production costs - Product 2 NZ$ $ 3,900 $ 66,000 $ 165,000 $ 520,000 $ 1,040,000
Total production costs NZ$ $ 59,900 $ 221,000 $ 561,450 $ 2,120,931 $ 5,256,282

Wages 6
Direct Wages
Sales
Member 1 NZ$ $ 111,000 $ 120,000 $ 130,000 $ 150,000 $ 150,000
Member 2 NZ$ $ 95,000 $ 100,000 $ 105,000 $ 115,000 $ 115,000
Member 3 NZ$ $ - $ - $ 105,000 $ 115,000 $ 115,000
Member 4 NZ$ $ - $ - $ - $ - $ 115,000

Team 1
Member 1 NZ$ $ 100,000 $ 100,000 $ 120,000 $ 140,000 $ 140,000
Member 2 NZ$ $ 95,000 $ 95,000 $ 100,000 $ 110,000 $ 110,000
Member 3 NZ$ $ - $ - $ 100,000 $ 110,000 $ 110,000
Member 4 NZ$ $ - $ - $ - $ 110,000 $ 110,000

Team 2
Member 1 NZ$ $ 100,000 $ 110,000 $ 120,000 $ 130,000 $ 130,000
Member 2 NZ$ $ - $ 100,000 $ 120,000 $ 130,000 $ 130,000
Member 3 NZ$ $ - $ 92,500 $ 100,000 $ 107,500 $ 107,500
Member 4 NZ$ $ - $ 70,000 $ 75,000 $ 80,000 $ 80,000
Total Direct Wages NZ$ $ 501,000 $ 787,500 $ 1,075,000 $ 1,297,500 $ 1,412,500

Indirect Wages
Management
CEO NZ$ $ 65,000 $ 75,000 $ 92,500 $ 120,000 $ 120,000
General Manager NZ$ $ - $ 100,000 $ 110,000 $ 120,000 $ 120,000
Other NZ$ $ - $ 100,000 $ 110,000 $ 120,000 $ 120,000
Total Indirect Wages NZ$ $ 65,000 $ 275,000 $ 312,500 $ 360,000 $ 360,000

Total Wages NZ$ 566,000 1,062,500 1,387,500 1,657,500 1,772,500

Expenses 6

Consultancy fees NZ$ $ 100,000 $ 100,000 $ 100,000 $ 100,000 $ 100,000

Insurance NZ$ $ 20,000 $ 20,400 $ 20,808 $ 21,224 $ 21,649
Legal & Accounting fees NZ$ $ 50,000 $ 51,000 $ 52,020 $ 53,060 $ 54,122
Ads NZ$ $ 50,000 $ 51,000 $ 52,020 $ 53,060 $ 54,122
Marketing expenses NZ$ $ 50,000 $ 100,000 $ 150,000 $ 200,000 $ 250,000
Occupancy costs NZ$ $ 30,000 $ 40,000 $ 50,000 $ 60,000 $ 60,000
Research & Development NZ$ $ 20,000 $ 20,400 $ 20,808 $ 21,224 $ 21,649 7
Other NZ$ $ 10,000 $ 10,200 $ 10,404 $ 10,612 $ 10,824

Notes:

Insert relevant commentary supporting the assumptions above e.g. market share statistics, staff numbers, manufacturing costs, Revenue channels
etc.
Example notes and questions
1. What is driving the unit numbers in the assumptions above? What are the channels used to sell units? Online, direct, distribution etc.
2. Why are you targeting the next market. Have you validated the market? Do you have a relationship with key customers? Have you completed market
research? etc.
3. What new product development (NPD) is in the pipeline? Where is the company going? What is next?
4. What is the lag time between R&D/HR investment and the flow on Revenue?
5. What are the other sources of revenue? E.g. grants etc.
6. Are costs scaled proportionately to Revenue, taking into account economies of scale.
7. Has R&D for NPD been factored in as an expense or capital item? are you committing a % of revenue to R&D? or is it on a project by project basis?
Company X
Profit & Loss 2017 2018 2019 2020 2021
Revenue
Market 1 - Product 1 NZ$ $ 100,000 $ 262,500 $ 661,500 $ 1,736,438 $ 4,254,272
Market 1 - Product 2 NZ$ $ 5,000 $ 100,000 $ 250,000 $ 2,000,000 $ 4,000,000
Market 2 - Product 1 NZ$ $ 40,000 $ 125,000 $ 660,000 $ 3,600,000 $ 9,800,000
Market 2 - Product 2 NZ$ $ 8,000 $ 120,000 $ 300,000 $ 600,000 $ 1,200,000
Other revenue NZ$ $ 5,000 $ 5,000 $ 5,000 $ 5,000 $ 5,000
Total Revenue NZ$ $ 158,000 $ 612,500 $ 1,876,500 $ 7,941,438 $ 19,259,272
% change % NA 288% 206% 323% 143%

Production costs NZ$ $ 59,900 $ 221,000 $ 561,450 $ 2,120,931 $ 5,256,282

Direct wages NZ$ $ 501,000 $ 787,500 $ 1,075,000 $ 1,297,500 $ 1,412,500
Gross profit NZ$ ($ 402,900) ($ 396,000) $ 240,050 $ 4,523,006 $ 12,590,490
Margin % NA NA 13% 57% 65%
% change % NA NA NA 1784% 178%

Expenses
Consultancy fees NZ$ $ 100,000 $ 100,000 $ 100,000 $ 100,000 $ 100,000
Indirect wages NZ$ $ 65,000 $ 275,000 $ 312,500 $ 360,000 $ 360,000
Insurance NZ$ $ 20,000 $ 20,400 $ 20,808 $ 21,224 $ 21,649
Legal & Accounting fees NZ$ $ 50,000 $ 51,000 $ 52,020 $ 53,060 $ 54,122
Ads NZ$ $ 50,000 $ 51,000 $ 52,020 $ 53,060 $ 54,122
Marketing expenses NZ$ $ 50,000 $ 100,000 $ 150,000 $ 200,000 $ 250,000
Occupancy costs NZ$ $ 30,000 $ 40,000 $ 50,000 $ 60,000 $ 60,000
Research & Development NZ$ $ 20,000 $ 20,400 $ 20,808 $ 21,224 $ 21,649
Other NZ$ $ 10,000 $ 10,200 $ 10,404 $ 10,612 $ 10,824
Total expenses NZ$ $ 395,000 $ 668,000 $ 768,560 $ 879,181 $ 932,365

EBITDA NZ$ ($ 797,900) ($ 1,064,000) ($ 528,510) $ 3,643,825 $ 11,658,125

Margin % NA NA NA 46% 61%
% change % NA NA NA NA 220%

Summary

$25,000,000

$20,000,000 $19,259,272

$15,000,000
NZ$

$11,658,125

$10,000,000
$7,941,438

$5,000,000 $3,643,825
$1,876,500
$158,000 $612,500
$-
($797,900) ($1,064,000) ($528,510)

($5,000,000)
2017 2018 2019 2020 2021
Total Revenue EBITDA
Company X
Cashflow 2017 2018 2019 2020 2021 Notes
Bank balance
Opening bank balance NZ$ $ 100,000 $ 1,302,100 $ 238,100 ($ 290,410) $ 3,353,415
+/- EBITDA NZ$ ($ 797,900) ($ 1,064,000) ($ 528,510) $ 3,643,825 $ 11,658,125
+/- Movements in working capital NZ$ $ - $ - $ - $ - $ - 1
+ Equity raise NZ$ $ 2,000,000 $ - $ - $ - $ -
+/- change in debt NZ$ $ - $ - $ - $ - $ - 2
- Interest NZ$ $ - $ - $ - $ - $ - 2
- Corporate Tax NZ$ $ - $ - $ - $ - $ -
- Capital expenditure NZ$ $ - $ - $ - $ - $ - 3
Closing bank balance NZ$ $ 1,302,100 $ 238,100 ($ 290,410) $ 3,353,415 $ 15,011,541

Movements in cash NZ$ $ 1,202,100 ($ 1,064,000) ($ 528,510) $ 3,643,825 $ 11,658,125

Free cash flow to the firm NZ$ ($ 797,900) ($ 1,064,000) ($ 528,510) $ 3,643,825 $ 11,658,125

Cashflow summary
$16,000,000
$15,011,541
$14,000,000

$12,000,000

$10,000,000
NZ$

$8,000,000

$6,000,000

$4,000,000
$3,353,415
$2,000,000
$1,302,100
$- $238,100
($290,410)

($2,000,000)
2017 2018 2019 2020 2021
+ Equity raise Closing bank balance Free cash flow to the firm

Notes:

Insert relevant commentary supporting the assumptions above

Example notes and questions
1. Are there any movements in working capital (accounts receivable/accounts payable) that are unique to your business, industry and/or
customers/suppliers?
2. Do you have any debt? What are the key debt terms?
3. What capital expenditure is planned over the next five years? How is this consistent with your growth plan?