Home / Case Studies / CPG Integrated Planning & Forecasting Model
CASE STUDY
InteractiveCPG Integrated Planning & Forecasting Model
Monthly plan-to-cash model for a consumer goods company: gross-to-net, SKU and customer profitability, capacity, working capital and cash.
Business challenge
A consumer goods manufacturer needed one model linking volume, price, trade spend and costs to profit and cash, and a clear story for the board on why gross profit moved against budget.
Outcome
Management sees budget variance by driver, which SKUs and customers earn their keep, and how much cash and funding the plan needs.
Overview
Monthly plan-to-cash model for a consumer goods company: gross-to-net, SKU and customer profitability, capacity, working capital and cash. Inside: gross profit bridge vs budget; SKU, customer, channel and regional P&L; promotion ROI and trade spend; production capacity and inventory days; integrated three statements with revolver; forecast accuracy and management summary. How to use this model: on the Executive Dashboard sheet, use the drop-down in cell D5 to pick the scenario: Base, Upside or Downside. On the Capex & Capacity sheet, use the drop-downs in cells L7 to L11 to set each investment decision: Approve, Review, Defer or Reject. On the Risks & Actions sheet, use the drop-downs in cells K7 to K12 to update each action's status.
Approach
Volume and price drive gross sales; trade spend and returns give net sales; bill of materials, conversion and freight give gross profit. SKU, customer, channel and regional views, capex, people and working capital feed the three statements. A revolver covers any shortfall below minimum cash.
Capabilities
Skills
LET'S WORK TOGETHER
Need something like this for your business?
Tell me what you are trying to solve. I’ll respond with a practical view of how I can help.