QSO 520 Final Case Study
Question # 00540991
Posted By:
Updated on: 06/06/2017 02:00 AM Due on: 06/06/2017
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
business open, you will need investors to provide you with $250,000 dollars. You are going to be
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
batches of cupcakes per day. You have determined that the daily demand will follow the
distribution shown in the following table:
Daily
Demand
20
25
35
40
45
50 Probability
0.08
0.12
0.25
0.20
0.20
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
bacon 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.
You are looking to open up a cupcake shop in a high-traffic tourist area. In order to get your
business open, you will need investors to provide you with $250,000 dollars. You are going to be
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
batches of cupcakes per day. You have determined that the daily demand will follow the
distribution shown in the following table:
Daily
Demand
20
25
35
40
45
50 Probability
0.08
0.12
0.25
0.20
0.20
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
bacon 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.
-
Rating:
/5
Solution: QSO 520 Final Case Study