ACCT 209 Week 6 Excel Project Example

Ridgeline Roasters Next Year: Building Pro Forma Financial Statements in Excel From a Set of Business Assumptions

Fact Situation

Ridgeline Roasters is a small coffee roasting company that sells beans to cafés and online customers. In the year just ended, it reported sales of $840,000. The owners plan to grow sales 10% next year, buy a second roaster for $60,000 financed with a new loan at 7% interest, repay $20,000 of principal on existing debt and take $40,000 in owner distributions. They want pro forma statements to see whether the plan leaves enough cash. The company has no other debt, pays rent on a single building and sells mostly on 30-day terms to cafés, with online orders paid immediately. Its owners have asked for a projection simple enough to update themselves as the year unfolds.

Assumptions

Income Statements

Balance Sheets

Pro Forma Cash Flow Statement

How the Workbook Is Built

All assumptions sit on an Inputs sheet, and every statement cell refers to them. Pro forma sales, for example, is =Current_Sales(1+Growth), and cost of goods sold is =Pro_SalesCOGS_Pct. Receivables use the days ratio, =Pro_Sales/365*AR_Days, and inventory and payables use cost of goods sold in the same way. Equity equals beginning equity plus net income minus distributions. Cash is calculated last as total liabilities and equity minus all non-cash assets, and the cash flow statement independently rebuilds the change in cash; a check cell confirms that the two ending cash figures match, returning zero. A model is only as good as the assumption cells at its top, because every number below them is borrowed.

Analysis

The plan improves results and cash. Gross margin stays at 45%, net margin holds near 9.8% because higher interest and depreciation absorb much of the growth, and cash rises by $50,200 even after the roaster purchase and distributions. Driving each line from a ratio follows the approach recommended in research on forecasting from financial statements, which structures projections around profitability and asset-use ratios (Nissim & Penman, 2001). Two cautions apply. Profitability tends to revert toward average levels over time, so unusually strong margins should not be assumed to persist (Fama & French, 2000), and rapid growth in operating assets has been associated with lower future returns on assets (Fairfield et al., 2003), which argues for checking whether the second roaster will be used fully. If sales grow 5% instead of 10%, pro forma net income falls to about $76,800 and ending cash to about $103,000, still comfortably positive.

Conclusion

The pro forma statements show that Ridgeline Roasters can fund the new roaster, repay debt and pay distributions while increasing cash, provided sales grow as planned. Because every figure traces to an assumption cell, the owners can test other scenarios in seconds. The largest risks to the plan are slower sales growth and a rise in bean prices, which would push cost of goods sold above 55% of sales; both can be tested by changing a single input, and the check cell will confirm the statements still agree.

References

Fairfield, P. M., Whisenant, J. S., & Yohn, T. L. (2003). Accrued earnings and growth: Implications for future profitability and market mispricing. The Accounting Review, 78(1), 353-371. https://doi.org/10.2308/accr.2003.78.1.353

Fama, E. F., & French, K. R. (2000). Forecasting profitability and earnings. The Journal of Business, 73(2), 161-175. https://doi.org/10.1086/209638

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