Lesson Objective: To build detailed supporting schedules for Cost of Goods Sold, Operating Expenses, and Depreciation, ensuring that these costs are dynamically linked to revenue drivers and PP&E activity, creating a realistic and integrated cost structure.

In-Depth Notes:

1. Forecasting Cost of Goods Sold (COGS) and Gross Margin:
COGS is the direct cost attributable to production. It must be forecast using a “margin-based” approach that reflects the company’s operating leverage.

• Variable Cost Approach (Direct Modeling): This involves breaking COGS down into its sub-components: Raw Materials, Direct Labor, and Manufacturing Overhead.

  • Raw Materials: Driven by units sold (volume) multiplied by the projected cost per unit (which includes inflation).

  • Direct Labor: Driven by production headcount multiplied by average hourly wage (linked to GDP/capita growth). European models must include mandatory social security contributions (which are significantly higher in Europe than in the US) as a percentage of direct labor.

• Margin-Based Approach (The Global Shortcut): When detailed sub-component data is unavailable, COGS is projected as a percentage of revenue (i.e., Gross Margin).

  • Global Standard: The modeler projects the Gross Margin percentage based on historical averages and future expectations (e.g., “Gross Margin is expected to expand by 50 basis points due to automation”).

  • The Reconciliation: The model must calculate the implied COGS = Revenue * (1 - Gross_Margin%). A critical check ensures that the projected Gross Margin remains within the historical range of the company and its peers; otherwise, the model is considered “unrealistic” by investment committees.

2. Forecasting Operating Expenses (SG&A, R&D, and Non-Recurring Costs):
Operating expenses are typically split into Cash and Non-Cash components for better cash flow modeling.

• Selling, General & Administrative (SG&A): This is often forecast as a percentage of revenue (demonstrating operating leverage) or as a fixed growth rate (e.g., 3% annual increase for inflation). In US models, SG&A often includes large stock-based compensation (SBC) packages for tech companies. The model must explicitly separate SBC from cash SG&A, as SBC is a non-cash charge that is added back in the Operating Cash Flow.

• Research & Development (R&D): For pharmaceutical and tech companies, R&D is a critical driver. Under US GAAP, R&D is expensed as incurred. Under IFRS, development costs can be capitalized if certain criteria are met. A globally compliant model must include an “R&D Capitalization Policy” toggle that automatically shifts the treatment of R&D between expensing and capitalizing, depending on the jurisdiction.

• Non-Recurring and Restructuring Costs: A dedicated line item for “Adjustments” is created. This line item is linked to a toggle that zeroes out these costs for “Adjusted EBITDA” calculations but retains them for “Reported EBITDA” to comply with accounting standards.

3. The Depreciation and Amortization (D&A) Schedule:
D&A is a non-cash charge that must be built from a detailed roll-forward of the company’s asset base (PP&E and Intangibles). It cannot be forecast as a percentage of revenue, as this would be mathematically inaccurate.

• The PP&E Roll-Forward Logic: The D&A schedule relies on the PP&E supporting schedule. The model takes the Opening PP&E Balance, adds CAPEX, subtracts Disposals/Sales, and subtracts the current year’s Depreciation to arrive at the Closing PP&E Balance.

• Depreciation Methodology: Under US GAAP, companies may use accelerated methods (Double-Declining Balance) for tax purposes but use Straight-Line for book purposes. The model must use Straight-Line depreciation for book modeling (as it is globally accepted) unless a specific tax shield model is being built.

  • Useful Life Assumptions: The modeler inputs the average useful life (e.g., 10 years for machinery, 30 years for buildings). Depreciation expense is then calculated as Opening Asset Balance / Remaining Useful Life.

• The “Age of Assets” Analysis: A critical integrity check in the model is the calculation of the average age of assets = Accumulated Depreciation / Annual Depreciation. An asset base older than 15 years signals a looming CAPEX cycle. If the model does not project a corresponding increase in CAPEX to replace aging assets, the model’s cash flow forecasts are overly optimistic and will be rejected by European credit analysts.