Expense and Cost Forecasting

Revenue forecasting tells you what comes in. Cost forecasting tells you what goes out. The gap between them — and whether that gap is growing or shrinking — is the actual health of the business. A revenue forecast without a cost forecast is half a model. This lesson shows you how to project operational costs forward using the same structural approach you applied to revenue in Lesson 4.1, but accounting for the fundamentally different behavior of fixed versus variable costs.

How Costs Behave Differently from Revenue

Revenue responds to market conditions, pricing decisions, and customer demand. Costs respond to a different set of drivers — some tied directly to revenue volume, others completely independent of it. Mixing them together in a single growth-rate assumption is one of the most common forecasting errors in business planning.

There are three cost categories your model needs to treat separately:

• Variable costs — move directly with revenue volume. In your dataset, Total_Cost (Units_Sold × Unit_Cost) is a variable cost. When revenue grows 10%, variable costs grow proportionally. The key metric is cost as a percentage of revenue — if that percentage stays stable, the variable cost forecast is simply the revenue forecast multiplied by the cost ratio;
• Semi-variable costs — have a fixed base component plus a variable component that scales with volume. Marketing spend in your dataset behaves this way — there is a baseline level of spending regardless of volume, plus incremental spend that drives additional revenue;
• Fixed costs — do not change with revenue volume within a given range. Rent, salaries, and software licenses are fixed costs. They step up when the business crosses certain thresholds (a new office, a new hire) but otherwise remain constant regardless of monthly revenue fluctuations.

Calculating Historical Cost Ratios

Before forecasting costs, establish the historical relationship between each cost type and revenue. These ratios become your primary forecast assumptions.

Cost of Goods Ratio (variable cost rate):

By year:
=SUMIF(Business_Data[Year], 2024, Business_Data[Total_Cost]) /
 SUMIF(Business_Data[Year], 2024, Business_Data[Revenue])

3-year average (more stable forecast input):
=SUM(Business_Data[Total_Cost]) / SUM(Business_Data[Revenue])

Marketing Spend Ratio:

=SUMIF(Business_Data[Year], 2024, Business_Data[Marketing_Spend]) /
 SUMIF(Business_Data[Year], 2024, Business_Data[Revenue])

Gross Margin trend (confirms cost ratio stability):

• 2022 Margin: =(Rev_2022 - Cost_2022) / Rev_2022;
• 2023 Margin: =(Rev_2023 - Cost_2023) / Rev_2023;
• 2024 Margin: =(Rev_2024 - Cost_2024) / Rev_2024.

If gross margin is stable across years, the variable cost ratio is stable and safe to use as a forecast assumption. If margin is trending — improving or deteriorating — your cost forecast needs to reflect that trend rather than using a flat average.

Detecting Cost Trends

A flat cost ratio assumption is only valid if the ratio is genuinely flat. Use SLOPE to test whether your cost ratios are trending:

Cost ratio by year in cells B2:B4 (2022, 2023, 2024):
Year numbers in A2:A4 (1, 2, 3):

=SLOPE(B2:B4, A2:A4)

A slope close to zero means the ratio is stable — use the average. A positive slope means costs are growing faster than revenue — a deteriorating efficiency trend that needs to be explicitly modeled. A negative slope means efficiency is improving — also worth capturing rather than averaging away.

If the slope is meaningful (say, more than 1 percentage point per year), project the cost ratio forward using the same FORECAST.LINEAR approach you used for revenue:

Projected Cost Ratio for 2025:
=FORECAST.LINEAR(4, B2:B4, A2:A4)

Where 4 represents 2025 (the fourth year in the sequence)

Building the Cost Forecast Structure

Create a dedicated Cost_Forecast section in your Revenue_Forecast sheet — or a separate sheet if the model is large. The structure mirrors the revenue breakdown but organized by cost type.

Variable Cost Forecast:

Monthly Variable Cost = Monthly Revenue Forecast × Projected Cost Ratio

=[@Projected_Revenue] * Cost_Ratio

Where Cost_Ratio is a named cell in your Assumptions sheet — either the flat average or the FORECAST.LINEAR projection depending on what the slope test revealed.

Marketing Spend Forecast:

Marketing spend has a more complex relationship with revenue. Rather than a simple ratio, use a two-component model:

Base Marketing Spend (fixed component):
=AVERAGE(OFFSET(Forecast_Input_Table[Marketing_Spend_Monthly],
         ROWS(Forecast_Input_Table[Marketing_Spend_Monthly])-12, 0, 12, 1))

Wait — Marketing_Spend in your dataset is at the order level, not monthly. Aggregate it first:

Monthly Marketing Spend (add to Forecast_Input_Table):
=SUMIF(Business_Data[Month_Start], [@Period_Date], Business_Data[Marketing_Spend])

Then split into base and variable components using the ratio from your assumption block:

• Fixed Marketing Base: =Monthly_Marketing_Avg * Fixed_Marketing_Pct
• Variable Marketing: =[@Projected_Revenue] * Variable_Marketing_Rate
• Total Marketing Forecast: =Fixed_Marketing_Base + Variable_Marketing

Add Fixed_Marketing_Pct and Variable_Marketing_Rate to your Assumptions sheet as named, validated input cells.

Forecasting Cost by Product Category

Just as revenue varies by product category, so does cost efficiency. Software typically carries higher margins than hardware. Services margins depend heavily on utilization rates. Forecasting costs at the category level captures these differences rather than blending them into a single ratio.

Category Cost Ratio (e.g. Software): =SUMIFS(Business_Data[Total_Cost], Business_Data[Product_Category], "Software") / SUMIFS(Business_Data[Revenue], Business_Data[Product_Category], "Software")

Build a category cost ratio table alongside your revenue breakdown from Lesson 4.1:

Category	Revenue 2024	Total Cost 2024	Cost Ratio	Projected Ratio 2025
Software	SUMIFS	SUMIFS	Cost/Rev	FORECAST or flat
Electronics	SUMIFS	SUMIFS	Cost/Rev	FORECAST or flat
Office Equipment	SUMIFS	SUMIFS	Cost/Rev	FORECAST or flat
Services	SUMIFS	SUMIFS	Cost/Rev	FORECAST or flat
Accessories	SUMIFS	SUMIFS	Cost/Rev	FORECAST or flat

The Projected Cost for each category in 2025 is simply:

=[@Revenue_Forecast_2025] * [@Projected_Ratio_2025]

And total projected cost is the sum across all categories — a bottom-up cost forecast grounded in segment-level efficiency data.

Connecting Costs to Scenarios

Your scenario assumptions directly affect costs in two ways:

Through revenue volume — higher revenue in the Best Case means higher variable costs proportionally. This is automatic if your cost formulas reference the revenue forecast rather than hardcoded numbers.

Through cost efficiency assumptions — Best Case might assume margin improvement (lower cost ratio) while Worst Case assumes margin compression (higher cost ratio). Add cost-specific scenario adjustments to your Assumptions sheet:

Cost_Ratio_Adjustment:
Worst Case:    +3 percentage points (margin compression)
Expected Case:  0 percentage points (stable margins)
Best Case:     -2 percentage points (efficiency improvement)

Apply this adjustment to the base cost ratio:

Scenario-Adjusted Cost Ratio:
=Base_Cost_Ratio + Cost_Ratio_Adjustment

Where Cost_Ratio_Adjustment is driven by the Active_Scenario toggle exactly as the growth rate assumptions were in Lesson 3.3.

Building the Profit Forecast

With revenue and cost forecasts both built at the segment level, the profit forecast assembles itself:

• Monthly Gross Profit: =[@Monthly_Revenue_Forecast] - [@Monthly_Variable_Cost_Forecast]

• Gross Margin %: =[@Monthly_Gross_Profit] / [@Monthly_Revenue_Forecast]

• Operating Profit (after marketing): =[@Monthly_Gross_Profit] - [@Monthly_Marketing_Forecast]

Operating Margin %: =[@Operating_Profit] / [@Monthly_Revenue_Forecast]

Add all four metrics as columns in your monthly forecast table. Track how margin evolves month by month across the forecast horizon — a narrowing margin in the second half of the forecast year is an early warning signal worth flagging in the dashboard.

💡 Pro tip: Always forecast margin percentage alongside absolute profit. A business growing revenue 20% while margins compress from 42% to 35% is not in as strong a position as the revenue number alone suggests. The margin trend is often the more important story.

Task 1: Analyze Historical Cost Behavior

Goal: Establish the cost ratios and trend that will drive your cost forecast assumptions.

1. In your Revenue_Forecast sheet, add a Cost Analysis block below the revenue breakdown tables.
2. Calculate Total_Cost and Marketing_Spend totals for each year (2022, 2023, 2024) using SUMIF structured references against Business_Data.
3. Calculate the Cost Ratio (Total_Cost / Revenue) and Marketing Ratio (Marketing_Spend / Revenue) for each year.
4. Use SLOPE to test whether either ratio is trending. Record the slope values next to the ratio table and flag each as "Stable", "Improving", or "Deteriorating" based on the direction and magnitude.
5. Build the category-level cost ratio table using SUMIFS for all five product categories.
6. Add a Monthly_Marketing_Spend column to your Forecast_Input_Table using SUMIF against Business_Data[Marketing_Spend]. Verify the annual total matches your SUMIF result from step 2.

Task 2: Build the Cost and Profit Forecast

Goal: Project variable costs, marketing spend, and gross profit forward 12 months and connect them to your scenario assumptions.

1. In your Assumptions sheet, add four new named cells: Base_Cost_Ratio (linked to the 3-year average), Cost_Ratio_Adjustment (driven by Active_Scenario using an IF formula), Fixed_Marketing_Pct, and Variable_Marketing_Rate.
2. In your monthly forecast table, add a Variable_Cost_Forecast column: =[@Projected_Revenue]*( Base_Cost_Ratio + Cost_Ratio_Adjustment).
3. Add a Marketing_Forecast column using the two-component formula from this lesson.
4. Add Gross_Profit, Gross_Margin_Pct, Operating_Profit, and Operating_Margin_Pct columns using the formulas above.
5. Test scenario sensitivity: switch Active_Scenario to Worst Case and confirm Cost_Ratio_Adjustment increases, compressing the Gross_Margin_Pct column. Switch to Best Case and confirm margins improve.
6. Add Gross_Margin_Pct as a secondary axis series on your master forecast chart — a line tracking margin percentage alongside the revenue bars gives an immediate visual signal of any margin compression in the forecast period.