Build the Starbucks procurement and roasting model step by step: name its pieces, write its formulas, then use it to answer what-if questions with data tables. Same numbers and cell colors as the class spreadsheet. MBA 60310, Modeling module.
What kind of element is each one?
Every model has decisions, parameters, constraints, calculations and an objective. Classify each piece of the Starbucks case. Getting this right first is what makes the spreadsheet easy to build.
Quick test. If you could type any number you like into the cell, is it a decision? If the world sets it, it's a parameter. If a formula can compute it from other cells, it's a calculation: never type over it.
Write the formula for each calculated cell
Pick a formula for every white cell. The sheet recalculates as you go. A formula can give the right number for Q1's inputs and still be wrong, so Check my model also tests each cell at other prices and market conditions.
Q1: Starbucks contracts 60% pre-season, charges $11/lb, and the open-market price is $1.75/lb. What is the profit?
The stress tests use prices of $14 (demand below the 200M lb roasting capacity) and $18 (no demand at all), and a mix with 45% contracted at a $2.60 open-market price.
Ask the model what-if questions
Change the decisions and parameters, and watch every cell and the profit respond. The charts are the data tables from the case: profit as one input varies, with everything else held at the values on the right.