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 follow 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
20 0.20
45 0.20
50 0.15
You will need $45,000 per month in order 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 Ch. 10 pp. 454-462 for additional reference.
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.