Budget vs actual variance analysis and a monthly management pack

Bluestem Outdoor Gear sells tents, climbing kit, clothing and rentals through 9 stores in Colorado and Utah and a small online shop. The annual budget expected $18.4 million revenue and $1.48 million operating profit. After eight months, revenue is close to budget, but operating profit is $612,000 behind. The CEO, Dana Whitfield, says the monthly finance report is a 40-tab spreadsheet that tells her what happened but never why.

The new financial controller wants a management pack the leadership team can read in fifteen minutes each month. It should: - show results against budget and last year for the month and year to date; - split revenue variances into price, volume and mix, by category and by store; - flex the cost budget to actual activity, so store managers are judged on what they control; - give short, specific commentary with owners and actions; - include a reforecast for the rest of the year.

You will generate a synthetic dataset in task 1: a monthly budget and actuals for 9 stores, 5 product categories and 12 cost lines, with planted causes such as heavy discounting on tents, a mix shift from climbing kit to clothing, and higher wages in two stores.

Done looks like an Excel model with a variance engine, a six-page management pack and a reforecast. The method should work for any multi-site business, and you should be able to say, in one sentence, where the $612,000 went.