QSO 520 Final Case Study
You are looking to open up a cupcake shop in a high-traffic tourist area. In order to get your
usiness open, you will need investors to provide you with $250,000 dollars. You are going to
e making a pitch to a local bank for a portion of the money. The business environment you are
looking to operate in is one in which there is a heavy amount of seasonal business. However,
there is not enough non-seasonal business to support long-term growth. Based on this
information, before you submit your business plan to the bank for consideration, you will need
to perform a simulation analysis to determine the optimal model for your business.
You have made the following assumptions: Your equipment will allow you to only produce 50
atches of cupcakes per day. You have determined that the daily demand will follow the
distribution shown in the following table:
Daily
Demand
Probability
20 0.08
25 0.12
35 0.25
40 0.20
45 0.20
50 0.15
You will need $45,000 per month for your business to remain solvent. You are going to develop
a business plan for the bank based on your top selling item: the bacon chocolate cupcake.
There are 12 cupcakes in every batch for a total of 600 cupcakes made per day. Each batch of
acon chocolate cupcakes costs $45 dollars to make and the entire batch can be sold for $100.
You are able to sell any unsold batches for $25 the next day. As part of your analysis, you will
use Monte Carlo simulation and Scenario Manager in MS Excel to perform a simulation on your
data. Refer to the examples in Chapter 10, pages 454–462, for additional reference.
Assignment Deliverables, Part 1
1. Use a Monte Carlo simulation in MS Excel to simulate 1 month (26 days) of operation to
calculate monthly profit. Replicate this simulation for 156 days, 312 days, 624 days, and
936 days to calculate average monthly profit.
2. You are looking to expand production to see if you can increase profits by baking 55, 60,
65, and 70 batches per day. On a separate tab of the same spreadsheet, use Scenario
Manager to create a scenario summary for each batch.
Assignment Deliverables, Part 2
Write a 2- to 3-page paper summarizing your findings along with a recommendation either to
move forward with a formal business plan or to re-evaluate the business model. Be sure to
address the following:
1. Based on your calculations, will the business hit the revenue goal of $45,000 per month
and be sustainable?
2. Which batch quantity would recommend and why?
1
QSO 520 Final Case Study Guidelines and Ru
ic
Overview
The final project for this course is the creation of a simulation analysis that includes an Excel spreadsheet accompanied by a written 2- to 3-page business
ecommendation. In this project, you have been tasked with creating a Monte Carlo simulation and projecting business profit with Microsoft Excel Scenario
Manager. Based on your analysis, you will formulate a recommendation to either move forward with a formal business plan or re-evaluate the business.
In this assignment, you will demonstrate your mastery of the following course outcomes:
Craft solutions to business problems by combining management science techniques and spreadsheet tools to optimize decision-making
Plan for contingencies and future business activities by interpreting management information captured on spreadsheets
Prompt
Perform a simulation analysis using the Final Case Study. Your analysis will consist of completing a spreadsheet and will be accompanied by a written summary of
your findings and a business recommendation.
Specifically, the following critical elements must be addressed:
I. Simulation Analysis
A. Create a spreadsheet that has a functional Monte Carlo simulation.
B. Enter the appropriate data to calculate monthly profit.
C. Obtain results that will help you formulate a business recommendation.
II. Scenario Summary
A. Create a spreadsheet that has a functional scenario summary.
B. Enter the appropriate data to determine profits for each batch quantity.
C. Obtain results that will help you formulate a business recommendation.
III. Recommendation
A. Based on your calculations, will the business hit the revenue goal of $45,000 per month and be sustainable?
B. Which batch quantity would you recommend and why?
C. Would you move forward with the business or re-evaluate the business?
https:
learn.snhu.edu/d2l/lo
viewe
view.d2l?ou=6606&loIdentId=4927
2
Final Submission
Final Project Case Study
In Module Nine, you will submit your final project. It should be a complete, polished artifact containing all of the critical elements of the final product. It should
eflect the incorporation of feedback gained throughout the course. This submission will be graded with the Final Case Study Ru
ic.
Final Case Study Ru
ic
Guidelines for Submission: Your spreadsheet must contain two tabs: one for the Monte Carlo simulation and one for the scenario summary calculation. Your
ecommendation must be 2–3 pages in length (plus a cover page) and must be written in APA format. Use double spacing, 12-point Times New Roman font, and
one-inch margins.
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Simulation Analysis:
Spreadsheet
Creates spreadsheet that has a
functional Monte Carlo
simulation
(100%)
Creates spreadsheet that has a
partially functional Monte Carlo
simulation
(70%)
Does not create a spreadsheet
(0%)
10
Simulation Analysis:
Data
Enters the appropriate data to
calculate monthly profit
(100%)
Enters inco
ect data to
calculate monthly profit
(70%)
Does not enter data
(0%)
10
Simulation Analysis:
Results
Obtains results that that will
allow formulation of a business
ecommendation
(100%)
Obtains results that that will
allow partial formulation of a
usiness recommendation
(70%)
Does not obtain any results
(0%)
10
Scenario Summary:
Spreadsheet
Creates spreadsheet that has a
functional scenario summary
(100%)
Creates spreadsheet that has a
partially functional scenario
summary
(70%)
Does not create a spreadsheet
(0%)
10
Scenario Summary:
Data
Enters the appropriate data to
determine profits for each
atch quantity
(100%)
Enters inco
ect data to
determine profits for each
atch quantity
(70%)
Does not enter data
(0%)
10
Scenario Summary:
Results
Obtains results that will allow
formulation of a business
ecommendation
(100%)
Obtains results that will allow
partial formulation of a
usiness recommendation
(70%)
Does not obtain any results
(0%)
10
3
Recommendation:
Revenue Goal
Meets “Proficient” criteria, and
esponse demonstrates keen
insight into relationship
etween calculated profits and
usiness sustainability
(100%)
Recommends and justifies
whether business will hit the
evenue goal in a way that is
sustainable, supported by
calculated profits
(90%)
Recommends whether business
will hit the revenue goal in a
way that is sustainable, but
justification is not supported by
calculated profits
(70%)
Does not recommend and
justify whether business is
sustainable
(0%)
10
Recommendation:
Batch Quantity
Meets “Proficient” criteria, and
esponse demonstrates keen
insight into relationship
etween calculated profits and
ecommended batch quantity
(100%)
Recommends and justifies a
atch quantity, supported by
calculated profits
(90%)
Recommends a batch quantity,
ut justification is not
supported by calculated profits
(70%)
Does not recommend and
justify a batch quantity
(0%)
10
Recommendation:
Move Forward
Meets “Proficient” criteria, and
esponse demonstrates keen
insight into relationship
etween calculated profits and
ecommendation to move
forward or re-evaluate the
usiness
(100%)
Recommends and justifies a
decision to either move forward
or re-evaluate the business,
supported by calculated profits
(90%)
Recommends decision to either
move forward or re-evaluate
the business, but justification is
not supported by calculated
profits
(70%)
Does not recommend and
justify a decision to move
forward or re-evaluate the
usiness
(0%)
10
Articulation of
Response
Submission is free of e
ors
elated to citations, grammar,
spelling, syntax, and
organization and is presented in
a professional and easy-to-read
format
(100%)
Submission has no major e
ors
elated to citations, grammar,
spelling, syntax, or organization
(90%)
Submission has major e
ors
elated to citations, grammar,
spelling, syntax, or organization
that negatively impact
eadability and articulation of
main ideas
(70%)
Submission has critical e
ors
elated to citations, grammar,
spelling, syntax, or organization
that prevent understanding of
ideas
(0%)
10
Total 100%