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
40......................................................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 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-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?