This template is a multifunctional automated material resources and operations quantities and values planning (MRP) and product costing tool.
Intermediate calculations are automated with formula and data “keys”. It allows to add new model elements (sales segments / channels, products, materials, operations etc.) by simple copying of rows inside fixed blocks of data. The only change needed is inserting calculation “keys” for the new lines.
Download template:
Features
Forecasting horizon
- 6 years planning, of which 2 years – monthly
- Start from any month of Year 1 is possible with monthly detailed forecast for Year 2
- 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
Source data
- Prices and amounts are split to separate sheets to facilitate the automatic formula calculations
- Intermediate calculations are embeded to source data blocks for the ease of use
- Price assumptions include various inflations indices
Profit & Loss
- Expense split both by function and by nature with an easy and fine-tunable detalization using lookup formula
- EBITDA calculated and presented on the P&L
- Unlimited sales segments/channels both for sales, costs and inventory (one segment by default)
- Both quantity- and value-based on-invoice discounts per product
- Return and rebate provisions forecast (based on days-of-sales)
- Payroll tax calculations based on net salaries
- Vacation pay provision change taken into account
Production costing
- Bills of materials per product (variable monthly), automatic production program calculation and product costing
- Standard / LIFO costing – both for Cost of Sales and Inventory – with standard-to-actual variance tracking, which facilitate management decisions based on the latest available prices
- Production overheads are allocated using “consumption rate” pro-rata a single production index (facility load rate), idle capacity / over-consumption variances presented separately in the P&L
- Automated annual overheads consumption rate forecast
- Internal work centers / subdivisions cost allocation in a similar manner to production overheads
- By-product accounting
Balance Sheet
- Automatically balanced without manual intervention: cash excess is treated as free cash, cash deficit – as a subordinate debt (loan from shareholders)
- Itemized inventory forecasting based on days-in-warehouse and planned product cost
- Receivables/Payables forecaseing based on day-in-sales/purchases and commercial terms (advance/payment on invoice)
- Vacation pay provision estimation per department (production, sales, general & administrative)
- 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)
Financing Plan
- Easy short-term loans planning (combined with Cash Flow)
CapEx Plan
- Property, Plant & Equipment forecasting: historical cost, construction in progress and other capital investments, depreciation per department (production, sales, general & administrative)
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
- 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)
- Product marginality dashboard for pricing purposes
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
- Supplementary third-party products sales missing
- Only one manufacturing plant supported. Depreciation cost by default included only to production overheads (customizable)
- Itemized inventory and personalised payroll cost calculations
- Only one UOM per material is supported for forecasting (auxiliary UOMs could supplemented additionally – example enclosed)
- Auxiliary department cost allocation is configured manually (via service unit internal price calculation)
- Very simple list of MS&D costs
Support
Use the template is a kit to embed any other option extra inside the existing or in separate sheets, connected via direct links or “keys” and formula.
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!
