FIN 360 Week 3 Forecasting the Income Statement and Balance Sheet Example

Three Years Forward: Forecasting Revenue by Café Cohort, Margins and the Balance Sheet for a Coffee Chain Opening Twelve Cafés, With the Checks That Keep the Forecast Honest

[Student Name]

University of Phoenix

FIN/360: Financial Data Modeling

Week 3 Assignment

[Instructor Name]

[Date]

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

The coffee chain's model now has its structure and its drivers. The owners plan four new cafés in each of the next three years, opening on average at mid-year. The forecast must show whether the plan produces enough profit and cash, with the statements agreeing in every period. A forecast earns trust when every number can be traced to a driver and the statements agree with one another. This paper builds the income statement and balance sheet.

Revenue by Cohort

Revenue is built from three groups. Mature cafés, the 37 open more than two years, average $1.35 million today and grow 2.5% a year, producing about $51.2 million next year. The three cafés opened last year are in their second year and earn about 85% of a mature café's sales, about $3.5 million together. The four cafés opening next year, at mid-year on average, earn 60% of mature sales for about half a year, about $1.7 million. Total revenue next year is about $56.4 million.

In year two, last year's three cafés join the mature base, next year's four move to their second year at 85% and four more open. Revenue reaches about $63.2 million. In year three, with eight cafés maturing or ramping and four more opening, it reaches about $70.7 million. Grocery and app sales are included in the café averages because they have grown in line with café sales.

Costs and Operating Income

Cost of goods sold is 30.5% of sales and labor 34.5%. Occupancy is 9% for established cafés, but first-year cafés run above that ratio, since their rent starts in full while their sales are still building; that adds about $90,000 a year. Headquarters overhead follows the driver set last week: a fixed base with a small variable piece tied to growth. Earnings before interest, taxes, depreciation and amortization are about $12.1 million next year, a margin of 21.5%, rising to about $15.3 million by year three as new cafés mature and headquarters costs are spread over more sales.

Depreciation and Interest

Depreciation comes from the capital asset schedule: existing assets depreciate about $3.2 million a year, and each new café's $876,000 of improvements and equipment depreciates over ten and five years. Depreciation rises from $3.5 million next year to about $4.4 million in year three. Interest comes from the debt schedule, about $620,000 next year, rising as the credit line is used. Operating income next year is about $8.6 million, and net income, after interest and a 25% tax rate, about $6.0 million.

Working Capital

Inventory is twelve days of cost of goods sold, about $570,000 next year; payables are 25 days, about $1.18 million; grocery receivables are five days of grocery sales, small. Because payables exceed inventory and receivables, working capital is negative, which is typical of food service: suppliers finance the chain's stock. As the chain grows, this provides a little cash rather than consuming it.

Fixed Assets

Net property and equipment starts at $24.0 million. Each year it adds capital spending, four new cafés at about $876,000, $902,000 and $929,000 after inflation, plus maintenance spending of $22,000 per café, and subtracts depreciation. Net fixed assets rise to about $27.0 million by year three. Koller et al. (2020) note that forecasting capital spending from the operating plan, rather than as a percentage of revenue, produces more realistic balance sheets for companies whose investment is lumpy, as a café chain's is.

Debt and Equity

The chain has a $6 million term loan amortizing $1.2 million a year and a $10 million revolving credit line. The owners want no new equity. Retained earnings grow by net income less dividends of 30% of net income. The revolving credit line is the balancing item: if forecast assets exceed liabilities and equity, the model draws on the line; if cash builds, it repays the line first.

The Balance Sheet Result

Next year, capital spending of about $4.4 million exceeds the cash generated after dividends and loan repayment by a small amount, so the line reaches about $0.6 million at year end. In year two it rises to a peak near $1.5 million, then falls in year three as the second-year cohorts mature. The balance sheet balances in every month, and the checks sheet shows no errors, which confirms the linkages designed in the model's structure (Benninga, 2014).

Reasonableness Checks

Forecast margins stay within the range of the last three years. Return on assets rises modestly as new cafés mature. Sales per café in each cohort match the ramp observed historically. Nissim and Penman (2001) show that ratios such as these tend to revert toward typical levels, so a forecast in which margins jumped would need strong justification; this one does not.

What the Forecast Suggests

Under base assumptions, the chain can open twelve cafés over three years with modest use of its credit line and no new equity, and profits grow from about $6.0 million to about $8.3 million. The next step is to build the cash flow statement and supporting schedules in detail and to test covenants monthly, since peak borrowing may fall between year ends.

Monthly Pattern

The annual figures hide a seasonal pattern that matters for borrowing. In the first two years, the monthly model shows the credit line rising each spring and summer, when openings cluster and sales are seasonally lower, and falling after the holiday season. The monthly peak in year two is about $2.1 million, higher than the year-end figure, which is why covenant tests must be monthly.

Limits

The forecast depends on same-café growth and food costs, the two least certain drivers, and on new cafés ramping like past openings. Monthly detail may reveal tighter points than annual figures show.

Conclusion

The income statement and balance sheet forecast, built cohort by cohort from stated drivers, shows revenue rising to about $70.7 million, stable margins and a balance sheet that balances with the credit line as the plug. The results support the expansion in the base case and identify the credit line's peak as the figure to watch.

References

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

Koller, T., Goedhart, M., & Wessels, D. (2020). Valuation: Measuring and managing the value of companies (7th ed.). Wiley.

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