BUSINFO 705 Chap.1 Decision Models and Spreadsheet Architecture
Decision Models and Spreadsheet Architecture
This chapter turns a business question into a spreadsheet that another person can audit. It separates descriptive, predictive and prescriptive analytics; identifies decision variables, parameters and outputs; and builds a clear flow from labelled inputs through formulas to a decision measure.
The worked crossover model shows how algebra and Goal Seek should agree, while the practice checks focus on units, cell references and directional interpretation. Testing is treated as part of modelling: boundary cases, unit checks and formula tracing reveal whether a plausible result actually answers the business question. Every recommendation closes with an assumption that could reverse it.
What this chapter covers
- 01
Descriptive, predictive and prescriptive analytics
- 02
Problem definition, criteria and alternatives
- 03
Decision variables and model parameters
- 04
Input, formula and output separation
- 05
One-way and two-way data tables
- 06
Goal Seek and cost crossovers
Worked example · free
Solve a make-or-buy crossover
- 1Write make cost as 48,000 + 7.60q and buy cost as 10.00q.
- 2Set the costs equal and rearrange to 2.40q = 48,000.
- 1The crossover is 20,000 units; below it buying is cheaper and above it making is cheaper.
- 2At 26,000 units, making costs $245,600 and buying costs $260,000, so making saves $14,400.
Key terms
- Decision variable
- A quantity controlled by the decision maker and changed to compare alternatives.
- Model parameter
- A supplied input or assumption that affects model outputs but is not the chosen action.
- What-if model
- A spreadsheet that recalculates outputs when selected inputs are changed.
- Goal Seek
- An Excel tool that finds one input value required to reach a target output.
- Crossover quantity
- The activity level at which two alternatives have equal total cost.
Decision Models and Spreadsheet Architecture FAQ
When should I use Goal Seek?
Use Goal Seek when one input may change and you know the target value of one output. Define the output formula first, then set that cell to the target by changing the true input cell. Check the returned value in the original relationship.
Why should inputs be separate from formulas?
Visible input cells make assumptions easy to update, review and document. Hard-coded values inside many formulas can change inconsistently and hide the cause of an output. Separation also lets a reviewer trace a recommendation without unpacking every formula.
How do I interpret a cost crossover?
The intersection gives equal cost, but the decision requires both sides. Compare the slopes and test a quantity below and above the crossover. Then state which option costs less in each region and identify non-cost factors excluded from the model.
How can I test a spreadsheet formula efficiently?
Begin with a case whose answer and direction are easy to predict, such as zero volume. Then test a threshold and values immediately on both sides. Trace the output to every referenced input, check units, and compare one calculated result with hand arithmetic before trusting a larger table.
What should an audit-ready recommendation contain?
Name the preferred alternative, quantify its advantage at the stated activity level and identify the region where that ranking holds. State the assumption most likely to reverse the result and one relevant factor outside the model, giving the reader a practical reason and trigger for review.
Assessment move
Rebuild the make-or-buy model from a blank sheet. Put assumptions in one input block, write each total-cost expression in a calculation block and display the difference in an output block. Solve the crossover algebraically, then reproduce it with Goal Seek. Finish by changing quantity to values on both sides and predicting the cheaper option before recalculation.
For spreadsheet functions, practise selecting a tool from the question: a one-way table varies one input, a two-way table varies two, and Goal Seek solves backward from one target. Audit units and cell references before formatting. Create a second version in which setup cost changes, and predict the direction of the new crossover before calculating it.
Trace the final cost difference back to every input it uses, then replace one input with an extreme but meaningful value to expose a hidden sign or reference error. Test zero quantity, the crossover itself, and a quantity far above it. For each test, write the expected direction before Excel calculates. Build a compact audit sheet listing input name, value, unit, evidence source and permitted range.
Check that monthly and annual quantities never enter the same relationship without conversion. Compare a one-way data table with five candidate quantities against five direct substitutions and explain why the outputs agree. Then use Goal Seek on the difference cell and substitute its answer into both total-cost formulas.
Practise explaining the result without cell addresses: name the equal-cost point, identify the cheaper option on either side and quantify the saving at the requested volume. End with an operational limitation, such as capacity, quality or supplier reliability, that the cost model does not measure, plus the observation that would trigger a review.
Working through Decision Models and Spreadsheet Architecture in BUSINFO 705? Sia is AskSia’s AI Management tutor — ask any BUSINFO 705 Decision Models and Spreadsheet Architecture question and get a clear, step-by-step explanation grounded in how BUSINFO 705 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.