• Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle

© 2019 Excel Model Shop

3 Division forecast model

Purpose

The purpose of the model is to enable the user to generate forecast and store historical results for a business with up to three sales divisions.

 

Outputs

Integrated Financial Statements including Income Statement, Balance Sheet and Cash Flow

Dashboard including key summary financials, results, sales split, and valuation and IRR

 

Timeline

The model has a monthly timeline that runs for 8 years

There are annual summaries

A flexible model start date and flexible forecast start date

The ability to include both historical actual data and produce forecast data

The ability to enter historical Profit and Loss balance and an Opening Balance sheet

 

General

The ability to update the model units

The model provides check and commercial alert messages to assist the user

 

Sales

Enter and name up to 3 different revenue streams

Sales entered annually and spread over a 12 month period (based on a user defined seasonality trend)

 

Cost of sales

Split by those related to direct employee costs and non-employee related direct costs

Employee based direct costs are based on direct headcount staff, their average salary, social security and pension cost assumptions

Non-employee related direct costs are calculated on a gross margin assumption

 

Administration costs

Split by rent and rates costs, indirect staff costs, office costs, maintenance and repair costs, professional fees and other adjustments

Rent costs are entered annually and the user has the option of triggering prepayments or paid in the month

Business rates/taxes are entered annually, spread evenly over the year and paid monthly

Indirect staff costs are based on average annual headcount multiplied average salary, pension and bonuses

All other administration costs are entered annually and spread over the month

 

Fixed assets

Capital expenditure is entered annually and spread on a monthly basis

Depreciation is calculated on a straight-line basis

 

Working capital

Includes stock, debtors, creditors and VAT (sales tax)

Stock is calculated on a inventory days assumption based on direct non-employee related costs

Trade debtors is calculated on a debtor days assumption and based on total sales

Trade creditors is calculated on a creditor days assumption and includes direct non-employee related cost of sales, non-employee and non-prepayment related administration expenses, and capital expenditure

VAT/sales taxes are based on a percentage of non-employee related costs and are paid based on flexible payment flags

 

Income/corporation tax

Enter an effective tax rate and manually enter the tax payments

The user will also have the option to enter transaction tax benefits (refer to the transaction section within the model)

 

Interest

Interest rates are entered annually for both cash balance and overdraft balances

 

Dividend

Manually entered dividend distribution

 

Valuation

Ability to enter a date on which the valuation is taken

User enters a terminal growth rate

The discount rate is built up from multiple assumptions and is calculated using a weighted average cost of capital calculation (WACC)

Valuation is calculated using the net present value methodology

  • T&Cs

    Refer to T&Cs page for the Terms and Conditions of the sale

£50.00Price