FIN 360 Week 4 Cash Flow and Supporting Schedules Example

Where the Cash Goes Each Month: The Cash Flow Statement, Capital Spending and Depreciation Schedules, a Debt Schedule With a Revolver and Monthly Covenant Tests in the Coffee Chain Model

[Student Name]

University of Phoenix

FIN/360: Financial Data Modeling

Week 4 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 forecast shows profits growing as twelve new cafés open. Profit is not cash, however: each café requires about $876,000 before it sells a single drink, the term loan must be repaid on schedule and the owners take dividends. The income statement says the plan works; the cash flow statement says whether the company can survive the months in between. This paper builds the cash flow statement and the schedules behind it.

The Cash Flow Statement

Built indirectly, the statement begins from profit and then corrects for items that used or produced no cash, including changes in working capital. Depreciation is added back. Increases in inventory and receivables are subtracted; increases in payables and accrued wages are added. The result is operating cash flow, about $9.3 million next year. Investing activities subtract capital spending for new cafés and maintenance, about $4.4 million. Financing activities subtract term loan repayments of $1.2 million and dividends of about $1.8 million and add or subtract revolver draws and repayments. The net change in cash must equal the change in the balance sheet's cash, which the model checks every month (Brigham & Houston, 2022).

The Capital Spending Schedule

Each new café's $876,000 is split into $520,000 of leasehold improvements, depreciated over ten years or the lease term if shorter, $300,000 of equipment, depreciated over five years, and $56,000 of opening costs, expensed when incurred. Maintenance spending of $22,000 per café per year is equipment. The schedule tracks each class: beginning balance, additions by month, depreciation and ending balance. Openings are scheduled for February, May, August and October each year, since the owners avoid opening during the holidays.

The Depreciation Schedule

Depreciation on existing assets continues at about $3.2 million a year, declining slightly as older equipment becomes fully depreciated. New assets begin depreciating the month the café opens. By year three, annual depreciation is roughly $0.9 million higher than next year's $3.5 million. For taxes, bonus depreciation on equipment accelerates deductions, which the tax schedule reflects, lowering cash taxes in opening years.

The Debt Schedule

The $6 million term loan amortizes $100,000 a month. The $10 million revolving line has no scheduled payments. The model's rule is simple: if cash before revolver activity falls below the $1 million minimum, the model draws enough to restore it; if cash exceeds the minimum, it repays the revolver first. Interest is 7.2% on the term loan and 7.8% on the revolver, calculated on beginning-of-month balances to avoid a circular reference, an approach modeling texts describe as a practical alternative to iterative calculation (Benninga, 2014). An unused commitment fee of 0.25% applies to the undrawn line.

When Borrowing Peaks

The monthly results show the revolver rising in the spring of each year as openings cluster before the busy season, and falling after the holidays. The peak is about $2.1 million in August of year two, when two openings coincide with the summer slowdown in sales. Year-end balances are much lower, which is why annual figures alone would understate the chain's need.

Covenant Tests

The loan agreement requires, tested monthly on a trailing twelve-month basis, a debt load no higher than two and a half times the trailing year's EBITDA and a fixed charge coverage ratio of at least 1.25, where fixed charges include rent, interest and scheduled principal. The model computes both each month. Debt to earnings peaks at about 0.7 times in August of year two, far below the limit. Fixed charge coverage is the tighter test because café rent is large: it falls to about 1.42 at its lowest point, above 1.25 but with less room.

Margin of Safety

The model's goal-seek tool shows that fixed charge coverage would reach 1.25 if earnings before interest, taxes, depreciation and amortization fell about 12% below forecast in the tightest twelve months. That is the margin of safety. A decline in same-café sales of about 4 percentage points below forecast, combined with a two-point rise in food costs, would come close.

Taxes and Timing

Taxes are paid quarterly as estimates. Bonus depreciation reduces cash taxes by about $500,000 in each opening year, helping during the build-out. The tax schedule tracks the deferred tax liability that results, since book depreciation is slower.

Liquidity Beyond the Revolver

Even at the peak, $7.9 million of the revolver remains available, and the chain holds its $1 million minimum cash. Its liquidity is ample; the constraint is the covenant, not access to funds.

What the Owners Should Watch

The owners should watch fixed charge coverage monthly and same-café sales weekly. If coverage falls toward 1.35, they should delay the next opening by one quarter, which the model shows would raise coverage by about 0.08. Graham and Harvey (2001) found that financial flexibility is among the most important factors executives consider in debt decisions; here, flexibility comes from the ability to pace openings.

A Lender's Reading

The bank's credit analyst will read the model differently from the owners. The analyst will look first at the monthly peak on the revolver, then at the covenant headroom in the tightest month and then at how sensitive both are to same-café sales. The analyst will also check that the model does not assume revolver draws beyond the committed amount or dividends while a covenant is close to its limit. The owners added a rule that suspends dividends automatically if fixed charge coverage falls below 1.35, which the analyst is likely to welcome.

Checks

All checks pass: the balance sheet balances monthly, cash on the cash flow statement ties to the balance sheet, the revolver never exceeds its limit and depreciation never exceeds asset cost.

Conclusion

The cash flow statement and schedules show the coffee chain generating strong operating cash but needing its revolver seasonally, with a peak of about $2.1 million in year two. Monthly covenant testing shows fixed charge coverage as the constraint, with a 12% cushion. The expansion is feasible in the base case, and pacing openings is the owners' main lever if results weaken.

References

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

Brigham, E. F., & Houston, J. F. (2022). Fundamentals of financial management (16th ed.). Cengage.

Graham, J. R., & Harvey, C. R. (2001). The theory and practice of corporate finance: Evidence from the field. Journal of Financial Economics, 60(2-3), 187-243. https://doi.org/10.1016/S0304-405X(01)00044-7