Preparing a reliable plan requires consistent source data, explicit assumptions and linked financial schedules. A profitable sales forecast can still require borrowing when collections arrive after production costs and other payments.
Structured data stores observations in defined fields, such as invoice dates, customer identifiers and quantities. Unstructured data stores meaning in documents, correspondence or images that require extraction before forecasting models can use the information.
Cleaning, scrubbing and preparation describe actions that may overlap across organizations. The relevant decision is whether a field requires correction, standardization, extraction, mapping or investigation before its values enter the budget.
Preparing a forecasting dataset begins by specifying its grain, meaning the activity represented by one row. An invoice-line table cannot be joined directly to multiple collection records without controlling the resulting duplication.
Source-to-model transformation exhibit
Preparing joined data requires several additional checks:
- Join transactions to customer or product masters using stable identifiers, rather than names that may change or collide.
- Test unmatched identifiers and duplicate master keys before accepting the join.
Common mistakes
- Purchases substituted for consumption. Example 1's $23,000 material purchases exceed its $22,000 consumption by the $1,000 increase in raw materials inventory.
- Sales substituted for production. Using 1,000 sales units as production understates required output by 100 units and omits the planned finished goods increase.
- Revenue treated as collections. Collecting all $100,000 of January sales in Example 1 would incorrectly produce $55,150 ending cash: $10,000 + $30,000 + $100,000 − $64,850 − $20,000.
Bottom line
- Data preparation preserves source traceability, corrects confirmed errors and investigates unexplained missing values, truncation and genuine outliers.
- A supportable master budget links sales, inventory, production, purchases, staffing, expenses, cash and financing assumptions.
- Production differs from sales when finished goods changes, and purchases differ from consumption when materials inventory changes.
- Forecasts use expected conditions; projections use disclosed hypothetical assumptions, and neither guarantees future results.
Exam shortcut
Start with the requested output: units, expense, cash, financing, a method choice or a threshold. Mark the relevant period before calculating. For production and purchases, write “needs plus ending inventory less beginning inventory,” then label every quantity's units. In cash-budget stems, circle collection percentages, payment lags, minimum cash and interest dates. Calculate cash before financing first. Read “holding other assumptions constant” as sensitivity.
The full lesson (about 9,951 words, 66 min read) adds 4 worked examples, all 6 common mistakes, a self-check, free in the app.
Learning objectives
- I.B1
Browse all free CPA BAR lessons or jump into free CPA BAR practice questions.