This might be one of the most compact and universal templates ever – it includes only 3 worksheets: Balance Sheet, P&L and Cash Flow. Just itemize what they comprise of, forecast item dynamics, and your forecasts should be finalized automatically.
This template shall work well for most of the simple businesses in service, trade or even manufacturing. Sample data is for a car repair and service station having spare parts trading.
The template allows to forecast for up to 4 business segments or product/service categories, which may be count with the same UOMs (pieces, tonns, meters, hours etc.). The template may be easily supplemented with more advanced source data in separate sheets linked to the main sheets – the main sheets will balance automatically.
The forecasting is driver-based, i.e. price/volume approach for P&L, and based on days-outstanding ratios for Balance Sheet. All source data is inlined right near the forecast item that allows to control the forecast adequacy without much digging into technical details.
As we elaborated this template for finance trainings – we included additional lines with horizontal and vertical dynamics and ratio analysis for P&L lines.
Download the template:
FEATURES
Forecasting horizon
- 6 years planning, of which 2 years – monthly + 3 introductory months
- P&L, Cash Flow and KPI dashboard comprise of both monthly and year-to-date figure blocks
- Accounting actuals may be easily used for forecasting basis
- Start from any month of Year 1 is possible with monthly detailed forecast for Year 2
- Maximum features may be gained using 3 introductory months of Year 0 for accounting actuals of the previous period as a basis for the forecast
Source data
- The template provides only basic funcionality: reporting lines, financial analysis ratios, driver (price/volume) analysis and forecasting and automated balancing of the reports.
- Users are responsible for the data content and links between the source data, so a certain accuracy is required at each step
- Supplementary data (inflation, exchange rates etc.) and intermediate calculations could be placed at the bottom of main sheets
Profit & Loss
- Expense split by function and type (fixed/variable) with fully customizable expense lines
- Sales and cost of sales splt by 4 fixed business segments (sales channels or product groups)
- Additional block for general expenses allocated to other functional lines (employee entertainment events, mutual office rent etc.)
- Simplified forecasting model hybrid VAT business (where some products sold with VAT, and some – without)
- EBITDA calculated and presented on the P&L
Balance Sheet
- Automatically balanced without manual intervention: cash excess is treated as free cash, cash deficit – as a subordinate debt (loan from shareholders)
- Easy itemization of reporting lines with possibility to apply different forecasting methods to sublines
- Receivables/Payables forecaseing based on day-in-sales/purchases
- Property, Plant & Equipment (historical cost, capital investments and depreciation) forecasting right inside the Balance Sheet lines
- Adaptable automated VAT and CPT calculation
Cash Flow
- Automated indirect method report, adjusted for investment analysis (interest is presented as cash flow from financing activities net of tax)
- Easy both short-term and long-term loans planning (combined with Cash Flow)
Enterprise Value based on DCF
- DCF model at 2 dates (the beginning of the 1st and 2nd year) for dynamics growth and EV KPI monitoring
- Maximum functionality reached if using 3 introductory months of Year 0 (to assess enterprise value as at beginning of Year 1 more precisely)
- 2 methods supported: equity-based DCF and investment capital-based DCF
KPI Dashboard
- Monthly dashboard for basic KPIs (performace targets, marginality, profitability, liquidity, financial position, financial covenants)
Multilingual
3 languages supported right from the box:
- English
- Russian
- Ukrainian
Other languages may be easily added.
LIMITATIONS
- Free templates do not include extra options:
- market share and dynamics forecast
- scenario analysis
- sensitivity analysis
- budgeting per cost center
- multiple currencies
- property, plant & equipment disposals
- employee bonuses
- extended MS&D costs analysis
- Forecasting approach is defined manually for each reporting line/item
- Only one UOM per material per business segment is supported for forecasting (auxiliary UOMs could supplemented additionally manually)
- Extended pricing using price-list, discounts and returns is not supported
- Depreciation calculation per functional expense lines (production, MS&D, G&A) not supported by default (should manually configured)
- Production overheads allocated to all 4 business segments using manually configured allocation basis
- Production subdepartments costs not supported
- Vacation pay provision calculation and change forecasting not supported
- Itemized product cost calculation not supported
- Inventory days-in-stock forecasting using amounts, and not quantities
SUPPORT
Use the template is a kit to embed any other option extra inside the existing or in separate sheets, connected via direct links.
A short guide to the model is enclosed inside.
Should you have any questions – we support users in our Telegram group (you may find link this inside).
DOCUMENTATION
The documentation is in progress – join our Telegram group and write your questions there, we will notify you about the docs there!
