An advanced financial model template for IT- and other professional service businesses (consulting, audit, legal, engeneer and similar), as well as for telecommunication companies. It is based on Basic template and inherits its similicity, but contains some additional features for professional services. The template supports evaluation and forecasting for 4 business segments with a single service UOM (personnel hours, active users, downloads, messages sent, minutes of talking etc) per each segment.
The template supports the planning logics, which we described in our blog post. Sample data is provided for a typical IT company with all 4 business segments: oustaffing, oursourcing and fixed-price on-order development, SaaS licenses and services, and lifetime licenses sales (downloads etc.).
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. 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 template allows to forecast for up to 4 business segments or product/service categories (per-project forecasting may be added manually) with 4 personnel departments involved in service rendering (i.e. offices, locations, hubs or other personnel groups unitied per certain criteria, e.g. grade as in the sampel data). Department cost allocation base is configured manually (e.g. per service hours in the sample data).
The template also allows to account and estimate staff costs per non-core business functions (MS&D, sales, internal R&D, i.e. internal software development etc.). Cost-per-hour may be precisely estimated taking into account general allocatable overheads: office rent, employee entertainment events, intrafirm recruitment, provision of IT equipment, mobile phones, any fringe benefits or other employment-related general expenses.
Similar to the Basic this template also includes additional lines with horizontal and vertical dynamics and ratio analysis for P&L lines. Moreover, it includes simple unit economics analysis, e.g. calculations of revenue/hour or user, cost/hour or user, cost/customer and new customer acquisition costs, which might be helpful in assessing fixed fee service projects.
Download template:
Features
Forecasting horizon
- 6 years planning, of which 2 years – monthly + 1 introductory year
- 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 introductory Year 0 for accounting actuals of the previous period as a management reporting and 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 on separate supplementary data sheets
Profit & Loss
- Expense split by function and type (fixed/variable) including an additional functional line – R&D costs (e.g. non-capitalized commercial software development costs).
- Fully customizable expense lines using cost element dictionaries per functional cost line
- Sales, cost of sales, as well as M&SD costs splt by 4 fixed business segments
- 4 professional service departments (personnel groups / service cost centers) and 3 shared expense cost centers supported with the possibility to reallocate cost center expenses to other functions (other service departments, to MS&, G&A or R&D costs)
- Simple unit economics covered: revenue / cost of sales per 1 service unit (hour, user, download etc.), per 1 customer, and for MS&D costs – per 1 new customer (customer acquisition cost)
- 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
- Additional functionality for personnel bonus provision forecasting: plan provision as an expense subline and use days-in-expense at the end of each period to forecast the balance
- Additional functionality for personnel vacation pay provision forecasting: use unpaid days to plan the balance and the P&L entry will be calculated automatically
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 introductory 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
- EV estimate based on a market multiplier (provided manually) is added to KPI dashboard
KPI Dashboard
- Monthly dashboard for basic KPIs (performace targets, marginality, profitability, liquidity, financial position, financial covenants)
Breakeven analysis (CVP)
- This templates includes a separate sheet for segment-level breakeven analysis
Multilingual
3 languages supported right from the box:
- English
- Russian
- Ukrainian
Other languages may be easily added.
Limitations
- This emplate contains functionality not supported by Google Sheets (for shared work please use One Drive)
- Free templates do not include extra options:
- market share and dynamics forecast
- scenario analysis
- sensitivity analysis
- multiple currencies
- property, plant & equipment disposals
- Forecasting approach is defined manually for each reporting line/item
- Only one UOM 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)
- Project or particular service cost calculation not supported (only total segment cost of sales)
- Basic VAT forecasting is enclosed, which requires individual customization depending on a country and a company business profile (ready to use for companies in Ukraine for domestic services chargeable with VAT – the forecasting logis is the same as in the Basic model)
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!
