QSO 520 Final Case Study You are looking to open up a cupcake shop in a high-tra
ID: 3270109 • Letter: Q
Question
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
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 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
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.
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:
Based on your calculations, will the business hit the revenue goal of $45,000 per month and be sustainable?
Which batch quantity would recommend and why?
Daily Demand
Probability
20
0.08
25
0.12
35
0.25
40
0.20
45
0.20
50
0.15
Explanation / Answer
In the same way, profit can be calculated for other number of days. This question can be best explained if I am able to attach an excel sheet in the answer.
Using the data values, we have tried to generate random numbers and use those numbers to calculate profits for different production values using data table in excel.
Basically, we simulate each possible production quantity (50, 55, 60, 65 or 70) many times (for example, 1000 iterations). Then we determine which order quantity yields the maximum average profit over the 1000 iterations. I’ve assigned the cell range of probability the name lookup.
I then enter a trial production quantity (50 in this example) in cell C1. Next I create a random number in cell C2 with the formula =RAND(). As previously described, I simulate demand for the card in cell C3 with the formula VLOOKUP(rand,lookup,2). (In the VLOOKUP formula, rand is the cell name assigned to cell C3, not the RAND function.)
The number of units sold is the smaller of our production quantity and demand. In cell C8, I compute our revenue with the formula MIN(produced,demand)*unit_price. In cell C9, I compute total production cost with the formula produced*unit_prod_cost.
If we produce more cards than are in demand, the number of units left over equals production minus demand; otherwise no units are left over. We compute our disposal cost in cell C10 with the formula unit_disp_price*IF(produced>demand,produced–demand,0). Finally, in cell C11, we compute our profit as revenue– total_var_cost+total_disposing_price.
We would like an efficient way to press F9 many times (for example, 1000) for each production quantity and tally our expected profit for each quantity. This situation is one in which a two-way data table comes to our rescue.
In the cell range A16:A41, I entered the numbers 1–26 (corresponding to our 26 trials). One easy way to create these values is to start by entering 1 in cell A16.
We refer to the formula for profit (calculated in cell C11) in the upper-left cell of our data table (A15) by entering =C11.
We are now ready to trick Excel into simulating 26 iterations of demand for each production quantity. Select the table range (A15:E41), and then in the Data Tools group on the Data tab, click What If Analysis, and then select Data Table. To set up a two-way data table, choose our production quantity (cell C1) as the Row Input Cell and select any blank cell (we chose cell I14) as the Column Input Cell. After clicking OK, Excel simulates 26 demand values for each order quantity.
To understand why this works, consider the values placed by the data table in the cell range C16:C1015. For each of these cells, Excel will use a value of 50 in cell C1. In C16, the column input cell value of 1 is placed in a blank cell and the random number in cell C2 recalculates. The corresponding profit is then recorded in cell C16. Then the column cell input value of 2 is placed in a blank cell, and the random number in C2 again recalculates. The corresponding profit is entered in cell C17.
By copying from cell B13 to C13:E13 the formula AVERAGE(B16:B1015), we compute average simulated profit for each production quantity.
The value of production for which maximum average profit is generated is the best value for production.
produced 50 Cutoffs Demand Probability rand 0.360607 0 20 0.08 demandcard 35 0.08 25 0.12 unit prod cost $ 45.00 0.2 35 0.25 unit price $ 100.00 0.45 40 0.2 unit disp cost $ 25.00 0.65 45 0.2 0.85 50 0.15 revenue $3,500.00 1 total var cost $2,250.00 Risk Analysis total disposing cost $ 375.00 profit $1,625.00 26 days mean (ave profit) 1,784 1,842 1,526 1,556 1,384 1,625 50 55 60 65 70 1 2375 2650 2175 1700 1600 2 1625 1900 1800 1325 100 3 875 1525 300 1700 1225 4 2000 2275 2550 1325 100 5 2000 2275 675 1700 1975 6 1625 775 1425 200 475 7 875 775 1425 1325 1975 8 2750 1900 1800 2075 1225 9 1625 775 300 1700 1975 10 2000 2650 675 1700 2350 11 1625 775 1425 2075 1600 12 1625 2650 2550 2075 2350 13 2000 2275 1425 1325 1225 14 2000 2275 2175 200 1600 15 2375 400 1800 1325 1975 16 2000 1525 1425 2075 100 17 1625 1900 1425 1700 1225 18 1625 2650 2175 1700 1225 19 2375 1525 1800 2450 100 20 875 1525 2550 200 2350 21 875 1900 1800 2075 1975 22 1625 2275 675 1700 2350 23 1625 1900 675 2075 1225 24 2375 2650 1800 1700 1600 25 2000 2650 300 2450 1600 26 2000 1525 2550 575 475Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.