1 | P a g e Common DSS Analysis Techniques What-If Analysis: Checks the impact o

1 | P a g e
Common DSS Analysis Techniques
What-If Analysis: Checks the impact of a change in a variable or assumption on the model. For example,
“What will happen to the supply chain if a hurricane in Louisiana reduces holding inventory from 30% to
10%?” A user would be able to observe and evaluate any changes to the values in the model, especially to
a variable such as profits. Users repeat this analysis with different variables until they understand all the
effects of various situations.
Sensitivity Analysis: A special case of what-if analysis is the study of the impact on other variables when
one variable is changed repeatedly. The sensitive analysis is useful when users are uncertain about the
assumptions made in estimating the value of certain key variables. For example, repeatedly changing
revenue by small increments to determine its effect on other variables would help managers understand
the impact of various revenue levels on their decision factors.
Goal-Seeking Analysis: Finds the inputs necessary to achieve a goal, such as a desired level of output. It
is the reverse of what-if and sensitivity analysis. Instead of observing how changes in a variable affect
other variables, goal-seeking analysis sets a target value (a goal) for a variable and then repeatedly
changes other variables until the target value is achieved. For example, customers must purchase a new
product to increase gross profits to $ 5 million.
Optimization Analysis: An Extension of goal-seeking analysis finds the optimum value for a target
variable by repeatedly changing other variables, subject to specified constraints. Managers can calculate
the highest potential profits by changing revenue and cost variables in an optimization analysis.
Constraints on revenue and cost variables can be considered, such as limits on the number of raw
materials the company can afford to purchase and the number of employees available to meet production
needs.
2 | P a g e
Dr. Donuts Case
Dr. Donuts House is a UNO alumni-owned and -operated bakery, with its main branch in Slidell, LA. Dr.
Li and his friend Bill opened their doors and have been serving fresh, delicious donuts and other pastries
ever since. The Donuts House now has one store on the north shore, and they plan to have a second one
around the UNO campus, too. To make better business decisions, Dr. Li invited a group of talented
business students to help him conduct a series of analyses. In return, the students get a chance to eat
FREE donuts at Dr. Donuts House once a month until they graduate from UNO.
As such, please read the below information provided by Dr. Li and do 1) what-if analysis, 2) sensitivity
analysis, 3) goal-seeking analysis, and 4) optimization analysis, respectively.
1) The sales revenue of the Donut House in 2021 was $ 150,000, and the growth rate is expected at 25%
(increased by 25%) in 2022. Dr. Li also wants to know the sales in 2022 if the growth rate ranges from
100% to 300%, i.e., 100%, 125%, 150% … 275%, 300% (what-if analysis).
2) Dr. Donuts sells about 150,000 donuts annually, and the average price per donut is $1. The cost of each
donut is about $0.20. The annual store rent is $45,000, and payroll is about $ 60,000 annually.
Specifically, Dr. Li wants to know the impact of price (i.e., 0.5, 1.0, 1.5, 2.0, 2.5) and units sold (i.e.
140,000, 150,000, 160,000, 170,000, and 180,000) on the net profit (sensitivity analysis).
3) Envisioning the massive potential of his donut business, Dr. Li hoped to earn $ 20,000 in profits in
2022. Considering the price unchanged, how many donuts should Dr. Donuts sell this year (goal-seeking
analysis)?
4) There are several factors that Dr. Donuts should consider while making more profits. The first one is
that due to limited labor, the maximum number of donuts the House can produce and sell is about
200,000. Also, the store rent is at least $30,000 in town. Also, according to relevant federal and state labor
laws, the minimum pay for the employees (so far, only two) in the Donuts House is $ 50, 000 per year.
Considering the above three constraints, calculate the maximum profit Dr. Donuts House can make this
year (optimization analysis).
Hints:
 Operation profit = Sales (Revenue) – Cost of Goods Sold (COGS) – SG&A Expenses
 Sales = Units Sold * Price per Unit
 COGS = Units Sold * Cost per Unit
Requirement:
Please generate a professional report (i.e., business memorandum) with explanations based on your
business analyses using Excel.