FIN 360 Week 5 Scenarios and a Decision Recommendation Example

Twelve Cafés, Ten or Twelve Paced? Base, Downside and Upside Scenarios, a Two-Way Sensitivity Table and a Recommendation to the Coffee Chain's Owners and Their Bank

[Student Name]

University of Phoenix

FIN/360: Financial Data Modeling

Week 5 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.

Over four weeks, the coffee chain's model gained a structure, drivers, forecast statements and schedules with monthly covenant tests. In the base case, twelve openings over three years fit within the chain's credit line and covenants. The owners now ask how confident they can be and whether a different pace would be wiser. A single forecast answers what will happen if the assumptions are right; scenarios answer what happens if they are not, which is the question owners actually face. This paper uses the model to answer it.

Building the Scenarios

The historical analysis identified same-café growth and food costs as the least certain drivers, and new-café ramp-up as a secondary uncertainty for openings in a new city. The scenarios vary these together in plausible combinations.

The base case uses same-café growth of 2.5%, cost of goods sold of 30.5% and the historical ramp. The downside assumes a softer economy and competition: same-café growth of zero, cost of goods sold of 32% as coffee prices stay high and new cafés reaching only 90% of mature sales by year three. The upside assumes same-café growth of 4%, cost of goods sold of 29.5% as coffee prices ease and the historical ramp.

Results for Twelve Cafés

In the base case, net income rises from about $6.0 million to $8.3 million, the revolver peaks at about $2.1 million and the tightest fixed charge coverage is about 1.42. In the upside, net income reaches about $9.7 million, the revolver peaks under $1 million and coverage never falls below 1.6. In the downside, net income is about $4.1 million in year two, the revolver peaks near $5.4 million and fixed charge coverage falls to about 1.18 in the summer of year two, breaching the 1.25 covenant.

A Two-Way Sensitivity Table

To see how the two main drivers interact, the model computed the lowest fixed charge coverage for combinations of same-café growth from minus 1% to 4% and cost of goods sold from 29.5% to 32.5%. The table shows a boundary: coverage stays above 1.25 as long as same-café growth is at least 1% when food costs are 31%, or at least 2% when food costs are 32%. Below that line, twelve openings on the base schedule would breach the covenant. The owners can compare the line with current trends each quarter, since the choice between growth and safety depends mostly on where results fall relative to it (Brigham & Houston, 2022).

Three Expansion Paths

The model compared three paths. Plan A opens twelve cafés on the base schedule. Plan B opens ten, dropping two in year three, a simpler but less flexible compromise. Plan C, paced, opens four in year one and then four in each later year only if trailing same-café growth is at least 1.5% and fixed charge coverage at least 1.40 at the time of commitment; otherwise it opens two.

In the base and upside cases, Plans A and C produce identical results, and Plan B produces less profit by year three. In the downside, Plan A breaches the covenant, Plan B comes close at about 1.26 and Plan C, which would open only two cafés in each of years two and three, keeps coverage above 1.32 and the revolver below $3.5 million.

Recommendation

The owners should adopt Plan C, the paced plan. It captures the full expansion if conditions resemble the base or upside cases and protects the covenant if they deteriorate. The triggers are measurable from monthly reports the controller already prepares, so no new data are needed. The owners should also ask the bank to reset the fixed charge covenant to 1.20 for the expansion period, which the downside results suggest would provide a useful cushion and which the paced plan makes a reasonable request. Graham and Harvey (2001) found that sensitivity and scenario analysis are widely used by financial officers to evaluate projects, and presenting the paced plan with its triggers gives the bank the same kind of evidence.

Limitations

The model simplifies. It uses average ramp-up curves, assumes rent terms like current leases and treats headquarters costs as mostly fixed. It does not model a severe recession or the loss of the roastery. The downside is a plausible stress, not a worst case. Results are only as current as the inputs, so the model should be updated quarterly with actual results.

Maintaining the Model

The controller will own the model, update actuals monthly and rerun the scenarios before each opening decision. Changes to structure will follow the version control process set in week one, and the bank will receive the updated covenant projection each quarter (Benninga, 2014).

Conditions That Would Shift the Advice

This advice assumes the downside is a realistic stress, not a remote one. If the bank declines to reset the covenant, the owners should add a condition to Plan C: no year-three openings unless coverage has stayed above 1.45 for six months. If a better-capitalized competitor announces cafés in the chain's core markets, the owners should run a new scenario with lower same-café growth before committing to year two. If coffee prices fall sharply, the upside case becomes more likely and the owners could consider accelerating year-three openings.

Communicating the Result

The memo to the owners and bank is two pages: the question, the three scenarios in a small table, the sensitivity boundary, the three plans, the recommendation with triggers and the limitations. The spreadsheet supports the memo but is not the message, and the bank's analyst receives the file separately to test the assumptions.

Conclusion

In the base case, twelve cafés fit within the chain's resources, but a plausible downside would breach its main covenant. A paced plan that opens four cafés a year only when same-café growth and coverage meet set triggers keeps the upside and avoids the breach. The model's value was in comparing futures, not predicting one.

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