D553 Task 1 Predictive Analysis and Recommendation Example

Lease the Second Warehouse, Don't Build It: A Predictive Analysis of Sales and Capacity for a Composite Regional Building Supply Distributor

Student Name

School of Business, Western Governors University

D553: Data Analytics for Accountants II, Task 1

Course Instructor

Month Day, Year

Lease the Second Warehouse, Don't Build It: A Predictive Analysis of Sales and Capacity for a Composite Regional Building Supply Distributor

The Decision

Prairie Line Building Supply, a composite supplier that sells lumber, roofing, windows and other materials to contractors in central Iowa, had sales of $124 million last year. Its only warehouse, in Des Moines, operated at 92% of capacity during the spring and summer peak. Operations estimates the warehouse can support about $148 million of annual sales. The owners are considering three options: build a second warehouse in Ankeny for $6.8 million, lease a comparable building for $540,000 a year on a five-year term, or do nothing for now. They asked the accounting team to forecast sales for the next three years and recommend a course of action.

Data

The analysis used eight years of monthly sales from the general ledger, 96 observations, and monthly residential building permits for the four counties Prairie Line serves, published by the U.S. Census Bureau's building permits survey. Before modeling, two data issues were addressed. A software conversion had posted two months of sales to a single month five years ago; the amounts were reallocated using shipping records. A one-time $3.2 million order for a hospital project was removed from the history, since it would otherwise make an ordinary month look extraordinary to the model.

Candidate Models

Three models were compared, from simplest to most complex. A seasonal naive model forecasts each month as the same month last year, a benchmark any model should beat. An exponential smoothing model with trend and seasonality weights recent observations more heavily and adapts as patterns change. A regression model predicts monthly sales from the month of the year and building permits issued three months earlier, since permits lead material purchases by roughly a quarter. Standard forecasting practice recommends starting with simple benchmarks and adding complexity only when it improves accuracy on data the model has not seen (Hyndman & Athanasopoulos, 2021).

Testing Accuracy

Each model was fitted to the first seven years and used to forecast the final twelve months, which were held out. Accuracy was measured by mean absolute percentage error, the average size of each month's forecast error as a percentage of actual sales, and by mean absolute scaled error, which compares each model's errors with those of the naive benchmark and avoids problems that percentage errors have when actual values are small (Hyndman & Koehler, 2006).

Seasonal naive: 11.8% mean absolute percentage error. Exponential smoothing: 7.4%. Regression on permits: 5.9%, with a scaled error of 0.52, meaning its errors were about half the size of the benchmark's. The regression model was chosen, then refitted on all eight years.

The Forecast

Forecasting three years ahead requires assumptions about permits. Using the state housing agency's projection of moderate growth, the model's central forecast is sales growth of about 6% a year: $131.4 million next year, $139.3 million the year after and $147.7 million in year three. The uncertainty is wide. Using the model's error distribution and a range of permit scenarios, the 80% prediction interval for annual growth runs from about 1% to 11%.

Translated to capacity: in the central case, sales reach the warehouse's $148 million limit at the end of year three. In the high case, 11% growth, sales pass the limit during year two. In the low case, 1% growth, sales stay near $130 million even in year five, and a second warehouse would not be needed for many years.

Comparing the Options

Building costs $6.8 million now plus about $310,000 a year to operate, and it commits Prairie Line to capacity it may not need for five years or more in the low case. Leasing costs $540,000 a year plus the same operating costs and about $450,000 for racking and fit-out that could move with the company. Doing nothing costs nothing now but risks lost sales and overtime if growth follows the high case, when the warehouse would run short during the peak season as early as next spring.

Research on real options in strategy argues that when outcomes are uncertain, the ability to delay, expand or abandon an investment has value, and that firms should prefer staged commitments that preserve flexibility until uncertainty resolves (Trigeorgis & Reuer, 2017). A lease with renewal and purchase options is such a commitment: it adds capacity if growth is strong and limits the loss if it is weak.

Recommended Course of Action

Prairie Line should negotiate a five-year lease on a building in Ankeny, starting in about twelve months, with options to renew and to purchase at a set price. It should not build now. The team should update the forecast each quarter as new permit data are released and set clear triggers: if trailing twelve-month sales exceed $140 million, begin planning to exercise the purchase option or build; if they fall below $128 million for two consecutive quarters, sublease part of the space.

In the meantime, operations should reduce peak-season pressure on the existing warehouse by arranging direct shipments from mills to large job sites, which the data show account for about 9% of peak volume.

Limits

The forecast depends on permits, which depend on interest rates and the regional economy; a sharp change in either would move the forecast beyond the stated range. The model captures residential construction better than commercial work, which is 18% of sales. And forecasting three years ahead with eight years of history is inherently uncertain, which is why the recommendation relies on flexibility rather than on the central forecast alone. Finally, the capacity limit of $148 million is an operations estimate; a small study of peak-week throughput would test it, and a higher true limit would push every date in this analysis later.

Conclusion

The tested forecast suggests Prairie Line will likely need more space within about three years, but the range of outcomes is too wide to justify a $6.8 million building today. A lease with purchase and renewal options, combined with quarterly forecast updates and clear triggers, adds capacity when it is likely to be needed while keeping the company's options open.

References

Hyndman, R. J., & Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.). OTexts. https://otexts.com/fpp3/

Hyndman, R. J., & Koehler, A. B. (2006). Another look at measures of forecast accuracy. International Journal of Forecasting, 22(4), 679-688. https://doi.org/10.1016/j.ijforecast.2006.03.001

Trigeorgis, L., & Reuer, J. J. (2017). Real options theory in strategic management. Strategic Management Journal, 38(1), 42-63. https://doi.org/10.1002/smj.2593