Reference no: EM131676055

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? Can you show the formuals in excel format please. And and the Monto crystall ball graph.