FIN 360 Week 2 Historical Analysis and Forecast Drivers Example

Three Years of Café Data Turned Into Forecast Drivers: Same-Café Growth, New-Café Ramp-Up, Cost Ratios and Working Capital Days for a Composite Coffee Chain Model

[Student Name]

University of Phoenix

FIN/360: Financial Data Modeling

Week 2 Assignment

[Instructor Name]

[Date]

The coffee chain and its figures are composites written for a model paper; methods and research findings come from the sources listed.

The coffee chain's model needs drivers, and drivers need history. The chain's accounting system and café reports supply three years of data: revenue grew from $44.8 million to $48.9 million to $52.0 million while the number of cafés rose from 34 to 37 to 40. History does not decide the forecast, but a forecast that cannot be explained by history needs a very good reason. This paper analyzes that record and sets the drivers.

Checking the Data First

Before analyzing anything, the analyst reconciled the café sales reports from the point-of-sale system with revenue in the general ledger for each year. They agreed within 0.3%, the difference being gift card sales recorded as revenue only when redeemed. Café counts were checked against lease records. Clean data matter: a driver computed from a report that does not tie to the books would carry an error into every forecast year.

Decomposing Revenue

Total revenue grew about 9% and then 6%, but that rate mixes two sources. Cafés open at least two full years, the comparable base, grew sales 4%, then 3%, then 2.5% as price increases slowed and traffic softened. The rest of the growth came from new cafés. Separating the two matters because the owners' question is about new cafés, and extending total growth would double count them.

New-Café Ramp-Up

Opening records for the nine cafés opened in the past three years show a consistent pattern: in their first twelve months, new cafés averaged about 60% of a mature café's sales; in the second year, about 85%; by the third, they matched mature cafés. A mature café currently averages $1.35 million in annual sales. The model will therefore forecast each opening cohort's sales by applying this ramp to the mature average, grown by same-café growth (Benninga, 2014).

Cost Ratios

Cost of goods sold, mainly coffee, milk and food, rose from 29% to 30% to 31% of sales, driven by a spike in green coffee prices and dairy costs. Labor rose from 33% to 34% to 34.5% as wages increased faster than prices. Occupancy, rent and utilities for cafés, held near 9% of sales, and general and administrative costs fell slightly from 7.4% to 7.0% as the chain grew. Research on forecasting finds that margins tend to revert toward typical levels over time rather than continuing extreme trends (Nissim & Penman, 2001), which argues against extending the recent rise in food costs indefinitely.

Unusual Items

Last year's results include a $600,000 loss from closing an underperforming café and a $350,000 insurance recovery for flood damage at another. Both are removed from the ratios used for forecasting, since neither will recur.

Working Capital in Days

The chain collects most sales immediately, so receivables are small, mostly grocery accounts, about five days of grocery sales. Inventory of beans, milk and supplies averaged twelve days of cost of goods sold. Payables averaged 25 days of cost of goods sold, since suppliers extend credit. Expressing these in days lets the model scale working capital with activity.

Capital Spending per Café

Each new café cost an average of $850,000 for leasehold improvements, equipment and opening costs, with little variation. Maintenance capital spending on existing cafés, replacing espresso machines and furniture, averaged about $22,000 per café per year. The roastery needed a new roaster in the second year at $900,000, a one-time investment.

Setting the Drivers

Same-café sales growth is set at 2.5% a year, matching the most recent year, since price increases have slowed. New cafés follow the 60%, 85% and 100% ramp. Cost of goods sold is set at 30.5%, between the last two years, on the expectation that coffee prices ease somewhat but not fully. Labor is set at 34.5%, since wage pressure continues. Occupancy stays at 9% for mature cafés, but new cafés carry higher occupancy in their first year, about 14%, because rent is fixed while sales ramp up. General and administrative costs are set at $3.6 million plus 2% of sales growth, reflecting fixed headquarters costs. Working capital uses the historical days. Capital spending is $850,000 per new café, inflated 3% a year, plus $22,000 per café for maintenance.

Seasonality

Because the first two years of the model are monthly, the drivers need a seasonal pattern. Mature cafés' sales run about 12% above the monthly average in October through December, when holiday drinks and gift cards sell, and about 8% below in July and August, when office workers vacation. The model applies these monthly weights to each café's annual sales. Opening a café just before the holidays therefore produces stronger early months than opening one in June, a timing effect the owners can test.

Back-Testing the Drivers

As a test, the analyst applied the proposed drivers to the prior year's starting point and compared the result with actual results. The drivers would have forecast revenue of $51.6 million against actual revenue of $52.0 million and operating income within 4% of actual, close enough to trust the approach while recognizing that any single year can differ.

Drivers Least Certain

Two drivers carry the most risk: same-café growth, which depends on traffic trends and competition, and cost of goods sold, which depends on commodity prices. Both will be tested in scenarios. The new-café ramp is based on nine openings and is fairly reliable, but openings in a new city might ramp more slowly.

Why Drivers Matter More Than Formulas

Fairfield and Yohn (2001) found that separating profitability into margin and asset turnover improves forecasts of future profitability, an idea the model applies by forecasting sales, costs and investment separately rather than extrapolating profit. A model built on explicit drivers can be tested and updated as conditions change.

Conclusion

Three years of history show modest same-café growth, a reliable new-café ramp, rising but likely peaking food and labor costs, small working capital needs and consistent capital spending per café. The drivers set here, each with a stated basis, will feed the income statement and balance sheet forecast next week.

References

Benninga, S. (2014). Financial modeling (4th ed.). MIT Press.

Fairfield, P. M., & Yohn, T. L. (2001). Using asset turnover and profit margin to forecast changes in profitability. Review of Accounting Studies, 6(4), 371-385. https://doi.org/10.1023/A:1012430513430

Nissim, D., & Penman, S. H. (2001). Ratio analysis and equity valuation: From research to practice. Review of Accounting Studies, 6(1), 109-154. https://doi.org/10.1023/A:1011338221623