DOCUMENTATION

PURPOSE

HORIZON is the forecasting module of the Finpact Solutions ecosystem and includes seven different forecasting models (including indirect operating expense forecasting and revenue stream forecasting).  Following the setup tabs, the users can choose any forecast model(s) to capture future operating costs, direct costs and revenues and Refresh All to capture the uploaded inputs for inclusion in NEXUS, the reporting hub.

While the term ‘forecast’ and ‘forecasting’ is used more frequently in HORIZON, ‘budgets’ are created using the same process. The only difference is that the exported ‘forecast’ can instead be an exported ‘budget’ by pasting the exported transactions into the 4 – Import Budget tab instead of the 3 – Import Forecast tab. Best practice is to separately work on budget and forecast in separate HORIZON workbooks, as the inputs may differ, or finalize budget (and save a copy of the budget) before making revisions for a forecast. In many companies, the forecast for the upcoming budget period (usually the upcoming fiscal year) becomes the budget, and thus budget and forecast are identical before the fiscal year officially begins. Then as the year progresses, forecast may begin to differ from budget as new internal and external inputs are included.

 

Tip: As HORIZON defaults into a five year forecast time horizon, filter and copy only the months or years applicable for the new budget into the 4 – Import Budget tab in NEXUS. This means that some future months or years may have both a budget and forecast, while some may only have forecast.

 

Budgeting versus Forecasting

 

	BUDGETING	FORECASTING
PURPOSE	Sets a shorter-term financial plan to achieve an outcome by which performance is measured against.	Creates a projection of the future that can extend beyond the short term and includes updated internal and external inputs when applicable.
INPUTS	Mostly historical data and internal expectations of growth.	Includes historical data but also more external influences and trends that extend beyond the company’s control, especially in the long term.
TIME HORIZON	Shorter periods, such as annually or quarterly.	Extends usually beyond the time horizon of a budget, often 3-5 years into the future.
UPDATE FREQUENCY	Set once before the benchmarking period begins and used to benchmark performance over that period. For an annual fiscal year budget, this is usually finalized right before the start of the new fiscal year.	Regularly updated, often monthly or as soon as important changes need to be included that change the financial outlook of upcoming months or years. Expected to be more accurate than budget data as time progresses, as it includes newer updates to financial projections.

 

 Back to Top

1 – Import Lookup

STEP 1. Import the content from the Master Ledger ‘EXPORT LOOKUP’ tab.

Copy and paste the content from the MASTER LEDGER’s Export Full Lookup tab into the Import Lookup Table. This populates the yellow account and ID dropdown lists in the forecasting models.

STEP 2. Proceed to the tab ‘2 – Forecast Parameters’.

2 – Forecast Parameters

Note: Only Step 1 and 7 are required for all forecasting models. Steps 2-6 apply only to users forecasting with the FCST – By Proposal Probability model.

STEP 1. REQUIRED: Input the Forecast Start Date.

Input the updated Forecast Start Date (gold cell) to be used across all the forecast models in the HORIZON tool. Each forecast model extends 60 months from the Forecast Start Date.

Note that updating the Forecast Start Date only changes the month column headers on the forecast models without shifting the data associated with months. This leads to a few different options for updating forecast data.

Option 1 (Suggested): Continue using the same HORIZON tool without updating the Forecast Start Date. Update the amounts under the upcoming forecast months only. This works especially well for projects/products with a fixed lifespan. The benefits of this option are that ID inputs and past forecast amounts remain unchanged (locked in place) and can be compared in NEXUS under IS – Act Vs Fcst, IS – Fcst vs Bdgt, and EA (Expected Actuals) which can use historical forecast data for comparisons against actuals, budget or a combination of actuals plus forecast even for historical months. The recommended time to update the Forecast Start Date would be at the conclusion of a fiscal year when historical forecast data is no longer needed for tracking against actuals or budget (though historical forecasts can always be preserved by saving a copy of the results from 3 – Export Forecast).

Option 2: Continue using the same HORIZON tool after updating the Forecast Start Date for a fresh 60-month forecasting timeframe, removing/overwriting the inputs from the months passed by shifting the upcoming monthly inputs to match the revised Forecast Start Date. The benefit of Option 2 is that the forecast timeframe always extends a full new 60 months from the revised Forecast Start Date and does not require resetting the ID inputs, but the challenge is in carefully shifting the monthly forecast inputs. Historical forecast data would also need to be separately preserved if users want to compare historical months in IS – Act Vs Fcst, IS – Fcst vs Bdgt, and EA (Expected Actuals) in NEXUS.

Option 3: Start with a fresh HORIZON tool, resetting ID inputs (Department, Project, Product, Contact, etc) and reforecasting from scratch or copy-pasting relevant upcoming monthly inputs from a former HORIZON forecast to match the Forecast Start Date of the new HORIZON forecast. Historical forecast data would also need to be separately preserved if users want to compare actuals versus forecast  (IS – Act Vs Fcst) in NEXUS. Option 3 is only recommended for a new forecast (e.g. for a new project or product) or if an older HORIZON tool has errors.

 

STEP 2. Required for FCST – By Proposal Probability. Set the active Indirect Revenue accounts.

Skip if not relevant to your business. If relevant, select up to ten active indirect revenue accounts using the yellow dropdowns. These selected accounts should align with the indirect rate columns in Step 4.

The Indirect % of Rev (excluding Fee) is automatically calculated after completing Steps 3 and 4 and allocates the total percentage of indirect revenue across the active Indirect Revenue accounts. The total percentage should match the total percentage in the Direct Cost Spread and Indirect Rate table’s Indirect % of Rev (excluding Fee) column.

 

STEP 3. Required for FCST – By Proposal Probability. Set the Direct Cost accounts.

In the Direct Cost Spread and Indirect Rate Percentages table, use the yellow dropdown cells in the column Default Cost of Sales Account Spread to set up to ten default direct cost accounts (or leave extra rows blank). Then input percentages in the column % Cost Allocation, totaling 100% across the selected default direct cost accounts. The combination of these two columns determines the breakdown of direct costs for each proposal bid.

 

STEP 4. Required for FCST – By Proposal Probability. Set the indirect rate percentages for the Direct Cost accounts.

In the Direct Cost Spread and Indirect Rate Percentages table, input percentages in the Indirect Rate columns (which are aligned to the actively selected Indirect Revenue accounts) based on whether the row’s default direct cost account recovers revenue under that indirect rate. Skip if not relevant to your business.

These percentages determine how much indirect cost is recovered by direct cost amounts. Percentages should be 0% or higher, with 0% for direct cost accounts that do not recover indirect revenue under that indirect rate.  For more information on indirect rates and indirect cost recovery, see the INDIRECT CALCULATOR.

The Simple Multiplier column is a quick way to calculate how much total revenue a unit of direct cost will generate, which includes recovery of the direct cost base amount as well as indirect costs. A $1000 allowable direct cost with a 1.5 Simple Multiplier will ultimately generate $1500 in revenue, of which $500 is for indirect cost recovery.

In HORIZON, the Simple Multiplier independently calculates each indirect rate % on the base eligible direct cost and provides a conservative (lesser) estimate of the amount of indirect revenue that can be recovered. In some more complicated organizations, certain indirect rates can also apply on top of the amount of indirect calculated from another indirect rate (a larger multiplier effect). For example, general administrative indirect rates may be applied also on top of the amount of fringe indirect revenue generated independently from a fringe indirect rate. Please work with your financial advisor and accountant to better understand the indirect rate nuances applicable to your company.

The helper columns (DC % of Rev less Fee, Indirect % of Rev (excluding Fee)) recalculate the percentage allocation of direct costs and indirect revenue as a percentage of remaining revenue post-fee.

 

STEP 5. Required for FCST – By Proposal Probability. Allocate direct cost and indirect revenue percentages by month.

The Proposal Direct Cost and Indirect Revenue Allocation by Month table shows monthly allocations for direct costs (and calculated indirect revenue from direct costs, if relevant) by month, based on the lifespan of a proposal (minimum of 1 month and maximum of 60). Each row should total 100%, with the default manually set to distribute the percentages equally by month.

Users can choose to override the default and manually spread the direct costs throughout the lifespan of a project to match the level of expected activity, such as frontloading a project (by moving a higher percentage of the total to earlier months) or backloading (moving a higher percentage to later months).

STEP 6. Required for FCST – By Proposal Probability. Allocate fee revenue percentages by month.

The Proposal Fee Allocation by Month table shows monthly allocations for fee revenue by month, based on the lifespan of a proposal (minimum of 1 month and maximum of 60). Each row should total 100%, with the default manually set to distribute the percentages equally by month.

Users can choose to override the default and manually spread the fee throughout the lifespan of a project, such as frontloading fee for a contract signing bonus (by moving a higher percentage of the total to earlier months) or backloading for a majority of fees upon completion of deliverables (moving a higher a percentage to later months).

STEP 7. Begin forecasting and then Refresh All.

Proceed to update forecast inputs in any or all of the applicable forecast model tabs. When ready to submit forecast updates, navigate to tab 3 – Export Forecast after a Refresh All.

3 – Export Forecast

STEP 1. Finalize forecast updates and then Refresh All to submit the updated forecast data.

Once forecast inputs have been updated, navigate to the Data tab on the top Excel ribbon, then under Queries & Connections group, select Refresh All. This submits any forecast data that is set to Upload to Forecast = Upload, Account ID Name is not blank, and where there is numeric data in at least one month’s column.

STEP 2. Copy the Export Forecast table content to NEXUS.

After a Refresh All, the Export Forecast table should include the latest forecast. Copy the contents of the Export Forecast Table to NEXUS in either the 3 – Import Forecast or 4 – Import Budget tab to view the results in financial reports.

Differentiating forecast and budget: The process for creating a forecast and budget is identical in HORIZON. Users can choose to use two separate workbooks for ease of separation—one for budget, one for forecast. Then when the budget or forecast is refreshed and ready for export, the data from the Export Forecast table would be copied and pasted into either the 3 – Import Forecast or 4 – Import Budget tabs in NEXUS, after which the data would be processed separately for reports. Budgets are generally set for a locked in period of time, such as one fiscal year, while forecast is often updated more frequently including monthly or quarterly. In NEXUS, actuals can be compared against both forecast and budget.

 Back to Top

Optional Metadata Columns

Department ID Name – Optional selection. If relevant, select an internal department to allocate the forecast to. Allows for reviewing reports filtered by department. Set up departments on the MASTER LEDGER’s Department Lookup tab.

Project ID Name – Optional selection. If relevant, select a project or contract to allocate the forecast to. Allows for reviewing reports filtered by project or contract. Set up projects or contracts on the MASTER LEDGER’s Project Lookup tab.

Product ID Name – Optional selection. If relevant, select a product or service to allocate the transaction to. Allows for reviewing reports filtered by product or service. Set up products or services on the MASTER LEDGER’s Product & Inventory Lookup tab.

Contact ID Name – Optional selection. If relevant for indirect operating expense forecasts, select an employee contact to allocate the forecast to. Set up employee contacts in the MASTER LEDGER’s Employee & Consultant Lookup tab. If relevant for revenue and direct cost forecasts, select a customer/client contact to allocate the forecast to. Set up customer contacts in the MASTER LEDGER’s Customer Lookup tab. Allows for reviewing reports filtered by contact name.

Description / Comments – Optional white-background input area for users to add notes for each row.

Tracking Reference 1 / Tracking Reference 2 – Optional white-background input area for users to add additional tracking metadata (e.g. subproject, subproduct, etc.).

Required Setup Columns

Account ID Name – Required input when available. For any utilized forecast rows that show the option to select from Account ID Name, choose from the dropdown options to allocate the forecast amounts to the appropriate revenue, cost or gross profit account.

Summary View – Required selection when available. Default set to ‘Show’, and determines whether the corresponding row or section is included in the Summary total tables at the top of the forecast model tab. To exclude the row or section from the Summary totals, select ‘No’ from the dropdown instead. Helpful when wanting to group different total amounts in the summary, such as by a certain Department ID Name or Project ID Name only.

Upload to Forecast – Required selection when available. Default set to ‘Upload’ and determines whether the corresponding row’s amounts and account will be uploaded to the financial statement reports following a Refresh. If the toggle is set to ‘Upload’, then the amounts for the selected Account ID Name will be uploaded to the corresponding forecast months. If the toggle is switched to ‘No’, then that row will be excluded when uploading forecast to the financial statement reports. Subtotals within the forecast models are usually dependent on whether the included summed rows are set to ‘Upload’, and exclude those rows from subtotals when set to ‘No’.

Sensitivity Factor – Required selection when available—do not leave blank. Defaults to ‘100%’, and is an added method to model percentage increases or decreases to variables or amounts across an entire row, without needing to set formulas to the cells or overwriting the inputted values for the entire row. The default ‘100%’ means to keep the row’s inputs as is. For users who plan to ignore the sensitivity factor option, keep the Sensitivity Factor column as ‘100%’ (no change) on every relevant row and forecast as usual. For users who want to employ the sensitivity factor, input any percentage 0% (removing an amount entirely) or higher on relevant rows as needed. For example, to reduce the row’s input by 25%, input a sensitivity factor of 75% (showing -25% in the % Change column); to increase the row’s inputs by 50%, input a sensitivity factor of 150% (showing +50% in the % Change column). 0% or blank would mean removing the row’s values entirely, as the row’s monthly amounts are multiplied by the sensitivity factor.

While the best practice is to model the most likely scenario, the sensitivity factor is helpful in quickly calculating best-case and worst-case scenarios or adding risk factors without needing to retype inputs. For example, to model an increased risk factor for a particular direct cost input, you can increase the cost row’s sensitivity factor above 100%. For some input rows, a second line beneath shows the same input parameter with sensitivity factor included. This allows a visual view of the revised inputs with the sensitivity factor and also makes it easy to quickly calculate a new row of revised amounts. For example, a user that wants to model double the forecasted sales quantity can first update the Sensitivity Factor to 200% for Forecasted Sales Quantity, which then updates a second row of Sales Quantity with Sensitivity Factor showing double (200%) the original row’s inputs. The user can then either keep the 200% Sensitivity Factor along with the original typed quantity per month, or copy the 200% values and overwrite the original amounts, then reset the Sensitivity Factor to 100%.

Other General Features

Parameter column – Locked column showing the purpose of the row, if relevant.

Summary by Month table – Automated. Shows the tab’s monthly totals for parameters when Summary View = ‘Show’. Depending on the forecast model, summary parameters include Operating Expenses (OPEX), OPEX Labor, Revenue, Cost of Sales, Gross Profit, Marketing OPEX or OPEX by Headcount Quantity. Helpful for previewing the anticipated subtotals prior to upload, or for viewing separate components of an anticipated upload by switching the Summary View toggle between ‘Show’ and ‘No’.

Summary by Year table – Automated. Shows the tab’s yearly totals for parameters when Summary View = ‘Show’. Depending on the forecast model, summary parameters include Operating Expenses (OPEX), OPEX Labor, Revenue, Cost of Sales, Gross Profit, Marketing OPEX or OPEX by Headcount Quantity. Helpful for previewing the anticipated subtotals prior to upload, or for viewing separate components of an anticipated upload by switching the Summary View toggle between ‘Show’ and ‘No’.

Month columns – Automated date columns based on the Input Forecast Start Date set in the ‘Setup’ tab. Extends from Month 1 to Month 60 for a total of 60 months (5 years) of forecast. To maintain an updated 60-month forecast, revise the Input Forecast Start Date each month for the month following the latest month of actual data, and shift all inputs as needed, or start a new blank forecast. Forecast data can be migrated from one template to another, facilitating a blank forecast on a new template if desired, then migrating the revised input to a master template for consolidation into the financial statements.

Extra Workspace rows – Extra rows, usually beneath revenue and cost parameters, that users can use to type additional notes, include side calculations, or create reference cells for formulas. Any input type is allowed in this extra workspace, and the content is excluded from uploads.

 Back to Top

Purpose

Forecasting model to forecast any indirect operating expense (OPEX) by total amounts per month. This is a simplistic method of forecasting operating expenses, without any built-in calculation parameters such as quantity. Space has been provided to forecast up to 500 rows of operating costs with 20 rows of Extra Workspace at the bottom.

For more advanced indirect labor forecasting, consider using the FCST – OPEX by Headcount model which adds in headcount, salary, date and escalation assumptions to help achieve a forecasted labor cost per month, and summarizes the forecasted headcount quantity per indirect labor account to assist with calculating operating expenses that vary by headcount.

 

Model Special Columns

• N/A

Parameters

• OPEX (rows): Required.

Input operating expense amounts by month for the selected Account ID Name OPEX account in each row. Each row can be adjusted with a Sensitivity Factor, though the adjusted amounts are not shown separately. The adjusted OPEX amounts are summed in the Summary by Month and Year tables at top for rows where Summary View = ‘Show’.

 Back to Top

Purpose

Forecasting model to forecast indirect labor such as recurring full-time and part-time employees, as well as non-labor operating expenses that are dependent on headcount (e.g. software licenses and benefits). The first (upper) table specifically calculates labor cost, while the second (lower) input table is intended to facilitate the grouping of operating expenses by headcount (number of people) or quantity (e.g. days, hours) subtotals.

Space has been provided for up to 200 rows of hires in the first table, and 10 sections with 10 rows of operating expenses each in the second table. A subtotal summary Headcount by Month, between the first (upper) and second (lower) input tables, shows the headcount subtotals by indirect labor account and can be referenced while forecasting OPEX by headcount. For example, headcount under a certain type of indirect labor account may require one tier of software license compared to another.

 

First (Upper) Table: OPEX Labor

Annual Salary: Required

Input an annual salary amount, which is used to determine monthly salary for forecasting purposes.

Start Date: Required

Input start Month, Day and Year of the current or expected hire. Feeds into the Partial Start Days percentage calculation for new hires that do not begin on the first day of the month.

End Date: Required

Input end Month, Day and Year of the expected termination. Feeds into the Partial End Days percentage calculation for hires that do not end their employment on the last day of the month.

Headcount: Required

Input headcount number for the number of hires or employees being forecasted with the same parameters, such as multiple interns being hired for the same program.

Escalation %: Required

Input an expected recurring salary increase percentage, to occur on the next Escalation Month. Set as 0% or blank to ignore any salary escalation.

Escalation Month: Required 

Use the yellow dropdown cells to select an expected recurring salary increase month, repeating the escalation each year on that month until the position is terminated. To escalate one year after initial hire, select the same month as the month of hire.

Monthly Salary: Required 

Calculates the initial non-escalated full monthly salary based on annual salary.

Partial Start Days: Automated 

Calculates the percentage of the first month worked for new hires that do not start on the first of the month.

Partial End Days: Automated 

Calculates the percentage of the final month worked for hires that do not terminate employment on the last day of a month.

Second (Lower) Table: OPEX by Headcount Quantity

Headcount Quantity (row): Required

Input the headcount or quantity (such as hours or days) to use each month for calculating particular operating expenses. For example, headcount under a certain type of indirect labor account may require one type of software license compared to another, while the same benefit allowance might be applied to a headcount of all employees. Non-headcount quantity can also be forecasted in this section, such as the number of consultant hours budgeted per month. Headcount Quantity can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Cost per Headcount Quantity (rows): Required

Use the yellow dropdown cells to select up to 10 operating expense accounts in each section under the Account ID Name column, then input a per headcount or per quantity expense amount for each month. For example, input the monthly cost for a single software license. These amounts will be multiplied by the Headcount Quantity with Sensitivity Factor for each month. Can be adjusted with Sensitivity Factor.

Subtotal Cost per Headcount Quantity (row): Automated

Shows the sum of the operating expenses per headcount or quantity within the section where Upload to Forecast = ‘Upload’. Calculated as Sum of Cost per Headcount Quantity × Sensitivity Factor.

Total OPEX Cost (row): Automated

Shows the sum of the operating expenses per headcount or quantity within the section where Upload to Forecast = ‘Upload’. Calculated as Subtotal Cost per Headcount Quantity × Headcount Quantity with Sensitivity Factor.

 Back to Top

Purpose

Forecasting model to forecast revenue and cost of sales by total amounts per month. This is a simplistic method of forecasting revenue and direct costs, without any built-in calculation parameters such as quantity. For slightly more advanced forecasting, consider trying the FCST – By Unit Economics model which adds in marketing expense assumptions to help achieve a quantity of sales forecasted, and uses the forecasted sales quantity to help calculate monthly revenue and direct costs. Space has been provided to forecast up to 50 rows of revenue (first upper Revenue table) and 200 rows of direct costs (second lower Cost of Sales table).

 

Model Special Columns

• N/A

Parameters

• Revenue:

In the first upper table, input revenue amounts by month for the selected Account ID Name revenue account in each row. Each row can be adjusted with a Sensitivity Factor, though the adjusted amounts are not shown separately. The adjusted revenue amounts are summed in the Summary by Month and Year tables at top for rows where Summary View = ‘Show’, while only rows with Upload to Forecast = ‘Upload’ are included in the exported forecast.

• Cost of Sales:

In the second lower table, input direct cost amounts by month for the selected Account ID Name direct cost account in each row. Each row can be adjusted with a Sensitivity Factor, though the adjusted amounts are not shown separately. The adjusted cost of sales amounts are summed in the Summary by Month and Year tables at top for rows where Summary View = ‘Show’, while only rows with Upload to Forecast = ‘Upload’ are included in the exported forecast.

 

 Back to Top

 

Purpose

Forecasting model to forecast marketing expenses, revenue, cost of sales and gross profit by quantifiable units (like individual products sold, or individual services rendered). Forecast varies over time primarily based on forecasted sales volume, while economies of scale can be forecasted by changing the monthly unit prices or variable direct costs by unit. Space has been provided to forecast up to ten revenue streams by unit economics.

For subscription businesses with returning customers, use the FCST – By Customer Subscription model to better simulate renewal and churn of customers.

 

Model Special Columns

• N/A

Parameters

Forecasted Sales Quantity: 

Input whole numbers greater than or equal to 0. Drives the calculations by unit for revenue and direct costs. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Conversion Rate from Leads: 

Input decimal percentage between 0% and 100%. Used to help determine number of leads or traffic hits needed (Required Number of Leads) to achieve the forecasted sales quantity. For example, if only 1/10 of leads become a sale, then to forecast (convert to) 5 sales, marketing efforts should aim to produce 50 leads. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Required Number of Leads: 

Estimates the number of leads or traffic hits needed to achieve the forecasted sales quantity. Calculated as Sales Quantity with Sensitivity Factor divided by Conversion Rate with Sensitivity Factor.

Target Cost of Acquisition (CAC per Sale): 

Input any number greater than or equal to 0. Represents the estimated expense required to acquire a single customer sale, encompassing expenses including marketing, advertisements, promotions and sales team salaries. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Marketing Spend Target: 

Estimates the required budget for marketing expenses to achieve the forecasted quantity of sales. Calculated as Sales Quantity with Sensitivity Factor × Target CAC with Sensitivity Factor. Reference this target amount when allocating amounts for Marketing Expense (OPEX) per month.

Marketing Expense (OPEX): 

Input any amount by month to allocate towards spending on various marketing expense accounts (under Account ID Name). Space has been provided for up to five different marketing expense lines, either unique accounts or repeat accounts for different purposes. Can be adjusted with Sensitivity Factor.

Total Marketing Expense: 

Calculated as the monthly sum of Marketing Expense (OPEX) amounts where Upload to Forecast = ‘Upload’. Inclusive of Sensitivity Factor adjustments for each row, though individual rows do not separately show updated amounts with their sensitivity factor adjustment.

Variance against Marketing Spend Target: 

Shows the difference between allocated Total Marketing Expense and the estimated Marketing Spend Target, where a positive value indicates allocating more spending than the estimated spending target for marketing.

Calculated CAC: 

Shows the forecasted Cost of Acquisition, calculated as the Total Marketing Expense divided by Sales Quantity with Sensitivity Factor.

Variance against Target CAC: 

Shows the difference between allocated and calculated Cost of Acquisition and the Target CAC with Sensitivity Factor, where a positive value indicates a higher calculated CAC than target CAC, meaning more spending is forecasted to achieve the level of sales than the proposed target CAC goal.

Unit Price: 

Input any amount by month to indicate a revenue price component of a single unit sold, such as multiple items or services (the different unit price components) in a bundled set (the single unit sold). Can be adjusted with Sensitivity Factor. For simple revenue streams, only one unit price component is required to proceed.

Select a revenue account under Account ID Name to allocate the total revenue for that price component each month, calculated as the Unit Price × Sensitivity Factor × Forecasted Sales Quantity. The total revenue per price component per month (not directly shown in separate rows) only gets uploaded if Upload to Forecast = ‘Upload’.

Unit Revenue: 

Shows the sum of the unit price components (up to 5) per unit sold for any Unit Price row where Upload to Forecast = ‘Upload’. Calculated as Sum of Unit Price × Sensitivity Factor.

Total Revenue: 

Shows the sum total revenue forecasted per month for every Unit Price row where Upload to Forecast = ‘Upload’. Calculated as Unit Revenue × Sales Quantity with Sensitivity Factor.

Unit Cost of Sales: 

Input any amount by month to indicate a variable cost for a single unit sold, such as shipping cost to customers, Cost of Goods Sold, and sales commission.  Can be adjusted with Sensitivity Factor.

Select a direct cost account under Account ID Name to allocate the cost. The amount that gets uploaded each month includes the forecasted quantity and is calculated as the unit Cost of Sales  × Sensitivity Factor × Forecasted Sales Quantity. That monthly amount per variable (not directly shown) only gets uploaded if Upload to Forecast = ‘Upload’.

Unit Cost of Sales Subtotal: 

Shows the sum of the variable cost components (up to 15) per unit sold for any Cost of Sales row where Upload to Forecast = ‘Upload’. Calculated as Sum of Cost of Sales × Sensitivity Factor.

Total Cost of Sales:

Shows the sum total cost of sales forecasted per month for every Cost of Sales row where Upload to Forecast = ‘Upload’. Calculated as the Unit Cost of Sales Subtotal × Sales Quantity with Sensitivity Factor.

Unit Gross Profit: 

Shows the gross profit per unit sold. Calculated as Unit Revenue less Unit Variable Direct Costs. Helps determine the profitability and efficiency of producing and selling additional units, where a higher unit gross profit encourages more production and selling, while a lower gross profit may encourage finding cost efficiencies or higher price opportunities.

Total Gross Profit: 

Shows the sum total gross profit forecasted per month. Calculated as Total Revenue less Total Cost of Sales. Gross profit becomes the amount of money available to cover operating expenses, taxes and debt.

 Back to Top

Purpose

Forecasting model to forecast revenue, cost of sales and gross profit against a determined ceiling amount, usually the project or contract budget. Revenue and cost of sales can be forecasted directly per month by amount, or by quantity with revenue rates and cost rates. Space has been provided to forecast up to ten projects.

 

Model Special Columns 

Project End Date: Optional. Manually input the end date for the selected Project ID Name.

Project Budget: Optional.  Manually input the Revenue ceiling and Cost of Sales budget for the selected Project ID Name.

Actual to Date: Optional. Referencing the latest actuals data from NEXUS, manually input the actual Revenue and Cost of Sales amounts as of the start of the forecast date.

Amount Remaining: Automated. Shows the remaining amount of revenue, cost of sales and gross profit in the budget based on spending of Actual to Date. If Actual to Date is 0 (or skipped), this amount would equal Project Budget.

 Amount Forecasted: Automated. Shows the total amount forecasted for revenue, cost of sales and gross profit for the selected project. Only sums inputs where Upload to Forecast = ‘Upload’.

 Actual + Forecast: Automated. Shows the sum of Actual to Date and Actual + Amount Forecasted. Should be less than or equal to Project Budget, otherwise the project is forecasted to exceed the budget.

 Act + Fcst vs Bdgt: Automated. Shows the variance between Actual + Forecast and Project Budget. A positive (+) amount indicates Actual + Forecast exceeds Project Budget, while a negative (-) amount indicates Actual + Forecast is less than the Project Budget.

 

Parameters

Forecasted Revenue Total / Forecasted Cost of Sales Total / Forecasted Gross Profit Total: Automated.

Summary of amounts by month for the project across all sections, as impacted by Sensitivity Factor and Upload to Forecast = ‘Upload’.

Revenue by Amount: Required, if applicable. 

Input any amount by month for a selected revenue account under Account ID Name. Can be adjusted with Sensitivity Factor. To forecast by quantity with separate revenue and cost rates, use the next section Revenue & Cost by Quantity.

Revenue by Amount Subtotal: Automated.

Shows the total revenue per month for every Revenue by Amount row where Upload to Forecast = ‘Upload’. Calculated as the Sum of Revenue by Amount × Sensitivity Factor.

Revenue & Cost by Quantity (Section): Section for forecasting by quantities and rates for revenue and cost, such as for billable labor hours where the billed rate is higher than the cost to the company with the difference going to gross profit. This is also common in Time & Material (T&M) contracts.

Quantity: Required, if applicable.

Input the quantity per month in which to multiply by the revenue rate and cost rate. Can be changed with Sensitivity Factor.

Revenue Rate: Required, if applicable.

Input the monthly revenue rate used to invoice the client per unit of quantity. This is usually higher than the cost rate unless the company is taking a loss for the quantity billed or recorded. Can be changed with Sensitivity Factor.

Cost Rate: Required, if applicable.

Input the monthly cost rate to the company per unit of quantity, usually lower than the revenue rate unless the company is operating at a loss for the quantity billed or recorded. Can be changed with Sensitivity Factor.

Revenue: Automated.

The monthly revenue for the quantity, calculated as Quantity with Sensitivity Factor × Revenue Rate with Sensitivity Factor.

Cost: Automated.

The monthly cost for the quantity, calculated as Quantity with Sensitivity Factor × Cost Rate with Sensitivity Factor.

Gross Profit: Automated.

The monthly gross profit for the quantity, calculated as Revenue less Cost

Revenue by Quantity Subtotal:

The monthly gross profit for the quantity, calculated as Revenue less Cost

Revenue by Quantity Subtotal / Cost by Quantity Subtotal / Gross Profit by Quantity Subtotal:

Summary of amounts by month across the Revenue & Cost by Quantity section for the project, as impacted by Sensitivity Factor and Upload to Forecast = ‘Upload’.

Direct Cost by Amount: Required, if applicable. 

Input any amount by month for a selected direct cost account under Account ID Name. Can be adjusted with Sensitivity Factor. To forecast by quantity with separate revenue and cost rates, use the previous section Revenue & Cost by Quantity.

COGS / Cost of Sales Subtotal: 

Shows the total direct cost per month for every COGS / Cost of Sales row where Upload to Forecast = ‘Upload’. Calculated as the Sum of COGS / Cost of Sales × Sensitivity Factor.

 

 

 Back to Top

 

Purpose

Forecasting model to forecast marketing expenses, revenue, cost of sales and gross profit by number of new and returning customers, such as for subscription models. Non-subscription revenue streams can also use this forecasting model to forecast recurring business. While the forecasted number of new customers and churn rate help drive sales, monthly revenue and cost of sales are not directly calculated against number of new customers forecasted. To forecast revenue and cost of sales by unit, use the FCST – By Unit Economics instead, or by amount directly with the FCST – By Amount model. Space has been provided to forecast up to ten customer subscription revenue streams.

 

Model Special Columns

N/A

 

Parameters

Forecasted Number of New Customers: Required.

Input whole numbers greater than or equal to 0. Drives the estimate for Marketing Spend Target and initial calculations for first time promotion subscriptions. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Conversion Rate from Leads: Optional.

Input decimal percentage between 0% and 100%. Used to help determine number of leads or traffic hits needed (Required Number of Leads) to achieve the forecasted sales quantity. For example, if only 1/10 of leads become a sale, then to forecast (convert to) 5 sales, marketing efforts should aim to produce 50 leads. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Required Number of Leads: Automated.

Estimates the number of leads or traffic hits needed to achieve the forecasted number of new customers. Calculated as Number of New Customers with Sensitivity Factor divided by Conversion Rate with Sensitivity Factor.

Target Cost of Acquisition (CAC per Customer): Required.

Input any number greater than or equal to 0. Represents the estimated expense required to acquire a single new customer, encompassing expenses including marketing, advertisements, promotions and sales team salaries. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor.

Marketing Spend Target: Automated.

Estimates the required budget for marketing expenses to achieve the forecasted number of new customers. Calculated as Number of New Customers with Sensitivity Factor × Target CAC with Sensitivity Factor. Reference this target amount when allocating amounts for Marketing Expense (OPEX) per month.

Marketing Expense (OPEX): Required.

Input any amount by month to allocate towards spending on various marketing expense accounts (under Account ID Name). Space has been provided for up to five different marketing expense lines, either unique accounts or repeat accounts for different purposes. Can be adjusted with Sensitivity Factor.

Total Marketing Expense: Automated.

Calculated as the monthly sum of Marketing Expense (OPEX) amounts Upload to Forecast = ‘Upload’. Inclusive of Sensitivity Factor adjustments for each row, though individual rows do not separately show updated amounts with their sensitivity factor adjustment.

Variance against Marketing Spend Target: Automated.

Shows the difference between allocated Total Marketing Expense and the estimated Marketing Spend Target, where a positive value indicates allocating more spending than the estimated spending target for marketing.

Calculated CAC: Automated.

Shows the forecasted Cost of Acquisition, calculated as the Total Marketing Expense divided by Number of New Customers with Sensitivity Factor.

Variance against Target CAC: Automated.

Shows the difference between allocated and calculated Cost of Acquisition and the Target CAC with Sensitivity Factor, where a positive value indicates a higher calculated CAC than target CAC, meaning more spending is forecasted to achieve the level of sales than the proposed target CAC goal.

New Subscriber Promotion Parameters (Section): Section for setting up the parameters for first time subscriptions for new customers. This can be a promotional type subscription (with different durations and pricing) or match the parameters of a regular subscription if there is no promotion.

Promotion Duration: Required.

Input the duration of a first-time subscription with a minimum duration of 1 month and maximum duration of 36 months.

Monthly Payment Promotion Total: Required.

Input the total revenue price for customers who choose to pay monthly for the first-time subscription or promotion. Set to 0 if monthly payments are disabled. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor. Can be compared to the Upfront Payment Promotion Total for easy comparison of revenue value over the duration of the promotion, if different.

Monthly Payment Amount: Automated.

Shows the monthly payment amount of a promotion subscription. Calculated as Monthly Total with Sensitivity Factor divided by Promotion Duration.

% of Monthly Payment Subscribers: Required.

Input a decimal percentage between 0% and 100% estimating the percentage of new customers in that month that choose to pay monthly versus paying upfront over the duration of the promotion. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor, with a cap of 100% with Sensitivity Factor.

Upfront Payment Promotion Total: Required.

Input the total revenue price for customers who choose to pay upfront for the entire first-time subscription or promotion. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor. Is sometimes priced lower than Monthly Payment Promotion Total to encourage upfront payments. While cash flow is upfront, revenue recognition takes the straight-line Upfront Payment Monthly Revenue amount.

Upfront Payment Monthly Revenue: Automated.

Straight-line calculation for monthly revenue recognition, calculated as the Upfront Payment Promotion Total divided by the Promotion Duration.

% of Upfront Payment Subscribers: Automated.

Calculated as 100% less % of Monthly Payment Subscribers with Sensitivity Factor, so that the sum makes up 100% of new customers.

Promotion Subscriber Renewal Rate: Required.

Input a decimal percentage between 0% and 100% estimating the percentage of new customers that choose to renew their subscription in that month and become a new regular subscriber following the end of the first-time or promotion subscription. Can match or differ from the renewal rate for regular subscribers who have already resubscribed at least once. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor, with a cap of 100% with Sensitivity Factor.

Promotion Churn Rate: Automated.

Represents the percentage of first-time customers who choose not to continue subscribing following the end of the first-time or promotion subscription. Calculated as 100% less Renewal Rate with Sensitivity Factor, so that the sum makes up 100% of new customers.

Regular Subscription Parameters (Section): Section for setting up the parameters for regular subscriptions for returning customers, which can match or differ from the subscription terms for first-time customers.

Subscription Duration: Required.

Input the duration of a regular subscription with a minimum duration of 1 month and maximum duration of 36 months.

Monthly Payment Subscription Total: Required.

Input the total revenue price for customers who choose to pay monthly for the regular subscription. Set to 0 if monthly payments are disabled. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor. Can be compared to the Upfront Payment Subscription Total for easy comparison of revenue value over the duration of the subscription, if different.

Monthly Payment Amount: Automated.

Shows the monthly payment amount of a regular subscription. Calculated as Monthly Total with Sensitivity Factor divided by Subscription Duration.

% of Monthly Payment Subscribers: Required.

Input a decimal percentage between 0% and 100% estimating the percentage of new customers in that month that choose to pay monthly versus paying upfront over the duration of the regular subscription. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor, with a cap of 100% with Sensitivity Factor.

Upfront Payment Subscription Total: Required.

Input the total revenue price for customers who choose to pay upfront for the entire regular subscription. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor. Is sometimes priced lower than Monthly Payment Subscription Total to encourage upfront payments.

Upfront Payment Monthly Revenue: Automated.

Straight-line calculation for monthly revenue recognition, calculated as the Upfront Payment Subscription Total divided by the Subscription Duration.

Upfront % Payment Subscribers: Automated.

Calculated as 100% less % of Monthly Payment Subscribers with Sensitivity Factor, so that the sum makes up 100% of new customers.

Subscription Renewal Rate: Required.

Input a decimal percentage between 0% and 100% estimating the percentage of returning customers that choose to renew their subscription again in that month following the end of their subscription term. Can match or differ from the renewal rate for new customers, as these returning customers have already experienced the service and chosen to resubscribe previously. Can be adjusted with Sensitivity Factor with a separate row beneath showing the modified amount with Sensitivity Factor, with a cap of 100% with Sensitivity Factor.

Subscription Churn Rate: Automated.

Represents the percentage of returning customers who choose not to continue subscribing again following the end of their regular subscription term. Calculated as 100% less Renewal Rate with Sensitivity Factor, so that the sum makes up 100% of new customers.

Expected Customer Lifespan: Automated.

Estimates the average customer lifespan in months for regular subscribers. Calculated as 1 divided by Subscription Churn Rate, multiplied by Subscription Duration.  A lower churn rate increases Expected Customer Lifespan.

Expected Customer Lifetime Value (LTV): Automated.

Estimates the average spend of a customer over the length of their relationship with the business. Calculated as the revenue price of a subscription divided by the Subscription Churn Rate. Here, the subscription revenue price is the weighted average of the Monthly Total with Sensitivity Factor and the Upfront Payment with Sensitivity Factor.

Expected LTV:CAC Ratio: Automated.

Compares the expected revenue a customer generates over their lifetime (LTV) compared to the cost of acquiring them (CAC) and helps to determine the efficiency of marketing and sustainability of the subscription business model.

If less than 1, then the cost of acquiring a new customer is higher than the revenue expected from that new customer and the business is losing money on new customers. If the ratio is between 1 and 2, then this is considered a risky zone where the business model is barely breaking even. A ratio around 3 is considered healthy, and an even higher ratio may represent opportunities to increase marketing spending to achieve even faster growth.

Counts (Section): An automated section of helper columns and rows used in the many calculations for the number of subscribers involving the end dates of their subscriptions and renewal dates.

Historical Number of Subscribers (Section): Section for establishing historical (actual) subscriber numbers, going up to 36 months back. Assists with forecast calculations involving subscribers who subscribed earlier than the Forecast Start Date. For subscription models that have no existing customers, this section can be skipped.

Historical Starting Number of Subscribers: Required, if applicable.

Establishes the number of existing subscribers beyond the 36 month historical timeframe, and is used primarily to assist with calculating Net Subscribers.

Number of New Subscribers: Required, if applicable.

Input the number of new first-time customers subscribing to the promotional subscription.

Number of Promotion Subscribers Renewing: Required, if applicable.

Input the number of new first-time customers who chose to renew to a regular subscription following the end of their promotional subscription.

Number of Regular Subscribers Renewing: Required, if applicable.

Input the number of regular customers who chose to renew their subscription again following the end of their expired subscription term.

Number of Promotion Subscribers Lost: Required, if applicable.

Input the number of new first-time customers who chose to stop using the product or service following the end of their expired promotional subscription term.

Number of Regular Subscribers Lost: Required, if applicable.

Input the number of regular customers who chose to stop using the product or service following the end of their expired regular subscription term.

Net Subscribers: Automated.

Calculates the number of total subscribers currently using the product or service, either as a first-time new customer or as a regular customer. Calculated as the previous month’s Net Subscribers + Number of New Subscribers less Number of Promotion or Regular Subscribers Lost.

Historical Payment Parameters (Section): Section for establishing historical (actual) subscriber numbers, going up to 36 months back. Assists with forecast calculations involving subscribers who subscribed earlier than the Forecast Start Date. For subscription models that have no existing customers, this section can be skipped.

% of Monthly Payment Promotion Subscribers: Required, if applicable.

Input a decimal percentage between 0% and 100% representing the historical percentage of new customers in that month that chose to pay monthly versus paying upfront over the duration of the promotional subscription.

Determines the automated % of Upfront Payment Promotion Subscribers, calculated as 100% less % of Monthly Payment Promotion Subscribers, so that the sum makes up 100% of historical new customers.

% of Monthly Payment Regular Subscribers: Required, if applicable.

Input a decimal percentage between 0% and 100% representing the historical percentage of renewing regular customers in that month that chose to pay monthly versus paying upfront over the duration of the new regular subscription.

Determines the automated % of Upfront Payment Regular Subscribers, calculated as 100% less % of Monthly Payment Regular Subscribers, so that the sum makes up 100% of historical regular renewing customers.

Forecasted Number of Subscribers (Section): Automated section calculating the forecasted number of new and returning subscribers, and the number of new and regular subscribers lost to churn.

Forecasted Number of Payments (Section): Automated section calculating the number of monthly payments and upfront payments for both first time subscription promotions and regular subscriptions.

Total Revenue (Section): Automated section with monthly revenue recognition including for upfront payment subscribers. Shows the total forecasted revenue per month based on the number of different types of subscribers each month. The Total Revenue subtotal is broken out into separate rows showing revenue from Promotion Subscribers (Monthly), revenue from Promotion Subscribers (Upfront) calculated as the monthly revenue recognition of their upfront payment, revenue from Regular Subscribers (Monthly), and revenue from Regular Subscribers (Upfront) calculated as the monthly revenue recognition of their upfront payment. Each of the separate rows is calculated based on the monthly quantity from Forecasted Number of Payments multiplied by the price (monthly or upfront) for that particular type of payment.

Cost of Sales: Required.

Input any amount by month to indicate a variable direct cost total that applies to that month’s sales, such as hosting expenses and customer support services that vary by volume of subscribers.  Can be adjusted with Sensitivity Factor.

Select a direct cost account under Account ID Name to allocate the amounts from each row. The amount that gets uploaded each month is calculated as the Cost of Sales × Sensitivity Factor. That sensitivity factor amount (not directly shown) only gets uploaded if Upload to Forecast = ‘Upload’.

Total Cost of Sales: Automated.

Shows the sum total for Cost of Sales forecasted per month for every Cost of Sales row where Upload to Forecast = ‘Upload’. Calculated as the Sum of Cost of Sales × Sensitivity Factor.

Total Gross Profit: Automated.

Shows the total gross profit forecasted per month. Calculated as Total Revenue less Total Cost of Sales. Gross profit becomes the amount of money available to cover operating expenses, taxes and debt.

 

 Back to Top

 

Purpose

Forecasting model to forecast upcoming proposal bids and the default spread of direct costs, indirect revenue and fee on those bids. To use this model, users should follow all steps in 2 – Forecast Parameters. Space has been provided to forecast up to 100 rows of proposal bids.

Only the Forecasted Proposal Bids Table allows inputs, while the Allocated Fee Revenue by Proposal Probability and Allocated Indirect Revenue by Proposal Probability tables are reference-only and used to assist with calculations allocating fee and indirect revenue to the forecasted proposals based on the setup in 2 – Forecast Parameters.

 

Model Special Columns

 Start Date: Required.

Use the yellow dropdowns to select the anticipated starting forecast month of the proposal, if won. The options come from the list of forecast months available based on the Forecast Start Date set in 2 – Forecast Parameters.

Total Contract Value: Required.

Input the expected total proposal contract ceiling (total achievable revenue).

Fee %:Required.

Input the fixed fee % expected for the proposal. This amount is directly deducted from the Total Contract Value before allocating direct costs and indirect costs (if relevant).

Probability %: Required.

Input the probability of winning the proposal, as a percentage between 0% and 100%. The probability % is used to discount the expected revenue, fee, direct costs and indirect costs (if relevant).

Lifetime (Months, Max 60): Required.

Input the expected length of the project in number of months, beginning from the First Forecast Month selected.

Status: Required.

For tracking purposes, select a status for the proposal using the yellow dropdowns. The default statuses options are as follows:

Identified Opportunity – Default selection. Indicates a proposal has been identified as a potential bidding opportunity, though no work has necessarily begun on the submission.

Drafting Bid – Indicates work has begun on the submission for the bid.

Bid Submitted – Indicates the bid has been submitted and is pending qualification or Closed-Lost status.

Bid Qualified – Indicates the bid has been short-listed as a candidate for win.

Closed-Won – Indicates the bid has been won. Can be removed from the list after a full forecast has been added to FCST – By Project.

Closed-Lost – Indicates the bid has been lost. These should be removed from the list, or Upload to Forecast toggled to ‘No’ to exclude from forecast and Summary View should be toggled to ‘No’ to exclude from Summary tables.

First Forecast Month: Required.

Use the yellow dropdown to select the anticipated start month and year of the proposal to recognize for the first month’s revenues and costs.

Last Forecast Month: Automated.

Shows the last month in which forecast is spread for a proposal bid, based on the First Forecast Month and extending the number of months of the Lifetime.

Discounted Contract Value: Automated.

Shows the reduced total contract value, calculated as Total Contract Value × Probability %.

Discounted Fee: Automated.

Shows the reduced total fee amount, calculated as Total Contract Value × Fee % × Probability %.

Discounted Revenue less Fee: Automated.

Shows the reduced total revenue remaining after excluding fee, calculated as Discounted Contract Value less Discounted Fee.

Discounted Indirect Rev (excluding Fee): Automated.

Shows the reduced indirect revenue expected, calculated as Discounted Revenue less Fee × Indirect % of Revenue (less Fee) from 2 – Forecast Parameters.

Discounted Cost of Sales: Automated.

Shows the reduced total cost of sales expected, calculated as Discounted Revenue less Fee × Discounted Indirect Rev (excluding Fee) %. Also become the amount of non-fee and non-indirect Revenue for the proposal.

Auto Status: Automated.

Helper column for determining whether a forecast should be calculated for a proposal row, based on the First Forecast Month and Status. If a proposal’s Start Date is earlier than the official Forecast Start Date, then the proposal win or loss status should already be known and the proposal should be excluded with the auto status ‘Exclude – Started’. If a proposal’s Status is ‘Closed-Lost’, then the proposal should be removed from the model or temporarily excluded from forecast calculations.

Parameters

Account ID Name: Required.

Using the yellow dropdowns, select a revenue account to attribute the non-fee and non-indirect revenue of the proposal bid. Suggested accounts include Contract Revenue or a Grants revenue account. This revenue account takes on the residual revenue equivalent to the amount of direct costs, whereas Fee Revenue and Indirect Revenue are other revenue accounts common to projects. Indirect Revenue is dependent on direct costs and indirect rates, while Fee Revenue is typically a reward for risk in excess of any direct costs or indirect cost recovery.

 Back to Top