Profit & Loss, Balance Sheet and Cash Flow statements for each entity and Consolidated for the Group.
Each individual account from General Ledger is linked and utilized in the model.
All accounts are grouped in sections by function they represent to provide one cost for the function, regardless of the kind they represent.
For example office expenses as one of administration expenses, include rent, servicing, cleaning, electricity, ect. providing one aggregated cost and also a full breakdown.
Another example is marketing, where direct advertising, design services, promotional materials, staff expense is grouped under one marketing expense and can be compared to generated leads, defining short and long term cost.
Using full ledger exports and transformation formulas with checks, the model, management accounts and financial statements always remains consistent, reconciled and accurate.
In order to fully explain accounts, all relevant sources of operational information are added and analytically transformed into meaningful relations driving each account. Some of the supplementary sources of information can include:
Legal / Corporate (group level consolidation)
Finance (contracts)
Operations (resource utilization)
Sales (multilevel product/price breakdowns)
Marketing (leads, efforts)
R&D (projects, budgets, timelines)
HR (headcounts, compensations, teams, social charges)
and more
Using the above information to supplement accounts and applying business logic, most P&L and Balance Sheet positions can be explained operationally in a volume / price / relationship format.
For example:
Revenue
Current Recurring Clients x Average Spent
Volume x Price x Cross-sale Ratio for revenue
Sales/marketing expense x Cost per acquisition x Average Basket for revenue
Costs
Expected deliveries x Utilization for final product or service x Price of one unit
Headcount x Employment Cost
That powerful feature helps you drive forecasts in a operationally parametrized way changing relevant and manageable inputs only.
Vertical groups
Model is designed in monthly intervals and usually covers 7 years. Two years of history and five years of forecast. These are vertically grouped into full financial years and recent quarters, to provide high level picture, while maintaining the visibility into monthly developments.
Horizontal groups
Any level of any financial statement can be expanded to provide underlying build up. To support clarity of information, aggregated lines are always visible. Breakdowns of those are added in the second layer, followed by supporting operational data and KPIs in the third layer.
This functionality, provide instant assessment on any required level.
Different data sources come in different layouts, formats, style. The input section of the model will convert them into a model friendly format and import relevant information to the final version.
Sections of sophisticated, multistep calculations such as (i) financing (ii) capex, intangible assets & depreciations, (iii) revenue recognition, (iv) customer/business segments, related costs and gross profits, are grouped together for transparent logical flow of computation and after being summarized in aggregated values linked back with relevant P&L or Balance Sheet lines.
This layout provides clarity of the logic, assumptions, steps and results.
Model can be extended from most aggregated numbers, through semi aggregated layers of information, into most detailed information available. This allows for deep dive into any number or relation.
Every line of the model has continuous monthly flow from history to the future and can be characterized and defined as:
- Historicals, representing past periods and defining trends
- Actuals, representing recent history and quantifying current relations between data
- Detailed forecast representing short term plans, and
- Long Term forecast, visualizing long term objectives.
Marinating historical-forecast continuity on individual and aggregated levels supports consistency and accuracy of the forecasts.
Additionally, most information fall into categories that either have distinct features or behave differently from others.
For example services / products sales
may be repeatable while other be discretionary one-offs.
May address different customer needs.
May require different amount of work or resources to provide them.
May be paid in different schemes or sold at different price points.
Employees are assigned to teams and bands.
Different R&D project focus on different areas, follow different timelines, require different resources, ect.
Analyzing, defining granularity and parametrizing these unique features accordingly to observable characteristics, enables very accurate business monitoring and forecasting.
Detailed operational and financial information lead to discovery of multiple trends and relations in everyday operations. For example, customer behavior, margin build up, resources utilization, operational efficiency and so on.
Applied to all meaningful categories, it helps reveal relationships and KPIs, that will show either health, progress or deterioration of business on one hand, and also will allow for planning, improvement and targeting though OKRs on the other.
They also allow for simple yet powerful and accurate forecasting. For example instead of tediously calculating employment expense per person for months ahead, payroll can grouped into teams, bands and average compensation levels. This way, employment expense is clear, functional, anonymized and easily forecastable.
Finalized, discussed and accepted forecast is stored in corresponding section as budget. It consist of every single line just as the forecast, and serves as reference for performance tracking and variation explanation.
Rolling forecast represent a concept of updating model, after each new actuals, to incorporate new developments into next periods.
It provides higher degree of confidence what can be expected (from last actuals onwards) and compared with a static annual budget, becomes a valuable tracking and adjustment tool.
After every monthly update, a set of most important values are summarized in dashboard for quick evaluation versus multiple reference points. These include,
monthly actuals vs budget vs, previous month, next month expectations,
year to date actuals vs previous year, vs budget, rolling forecast vs budget
quarter performance vs quarter, vs corresponding previous quarter, next quarter
Separate high-level extract is derived from the model and presented in a standalone aggregated version, ready to be shared with third parties without any additional work, version validation, or error checking.
Advanced formulation, designed to represent each category unique behavior. Designed in one or multiple transparent steps.
Parametrized for change of circumstances and scenario creation.
Even though, information are presented month by month in every line, actuals and forecasts cells are driven by different formulas. Actuals section is designed to import information from available sources, while forecast section utilizes parametrized business metrics to quantify next periods performance.
This feature, allows for smooth rolling of formulas from one period to another and replacing forecasted numbers with currently delivered actuals in one move.
Moreover the forecast will update itself creating rolling forecast, based on most recent actuals and be ready for any adjustments.
Model includes reconciliation and consistency checks working in multiple directions, such as:
data inputs vs final report,
data inputs vs other data points,
final report vs original data sources.
This design intends to guarantee that all information are included, reconciled and correct.
After every month update, model will automatically choose right timeframes and provide variations' explanation between actuals and forecasted or budgeted numbers at every stage of calculation, either aggregated or detailed, giving quick and error free quantification of over or underperforming areas.
Given high level of sophistication and automation, model can be quickly recalculated to present different scenarios.
For example, in a subscription business, increasing prices of monthly plans, will result in increased revenue, increase churn, operating margins, payment processing, net profit, cash flow, financing needs. A simple (yet well assessed) adjustment to first two factors will be sufficient to visualize overall impact. This helps to focus on quality of inputs and prior assessment, rather than tedious calculations.
Scenarios can be compared and evaluated against each other in a VAR (value at Risk, investment) framework, to provide real transparency to results sensitivity in a full three financial statements framework.
Not statistical, but discretionary representation of risk in the business.
Continuing the previous example, subscription price increase will generate additional 10% to revenue, and while most customers will accept the change, it might lead to one-off churn of 5%. Therefore 100m revenue is expected to grow to 110m, with VAR of -5m.
Due to previously mentioned features, VAR would be calculated on every single line of the model. For clarity of impacts, only most important metrics such as revenue, gross profit, gross margin, net profit, cash flow and cash at the end of the next period, will be presented.
This risk assessment, can be developed either in cooperation with your team or as an independent outlook, challenging your assumptions.
From cash balances, through balance sheet movement, to P&L objectives, explained by supporting operational data points with clearly defined relations between data, in one sentence... data driven decision making has found its rightful meaning.
Most of the businesses have all the necessary information available already and simply require to organize and connect them together in a meaningful way to see one complex business picture.
Besides an obvious benefit of managing a business in more informed way, it most likely will be the case that uncover missing or extensive items which adjustments will increase current value of your business.
Adding VAR scenario analysis helps business be prepared for disadvantageous situations with operational plan to be implemented as soon as needed.