SOLVE IN EXCEL SHOWING ALL FORMULAS

BMW headquarters, in Munich, Germany, has asked the University of Alabama to give an estimate of their revenue in the year 2023. They want to know so they can plan for new facility changes that will increase their output capability. All the finances for 2023 are already set except for the amount of money spent on advertising. The Advertising department, along with the finance department, has provided the following data. Advertising costs money, but there is also an expected revenue gained. Specifically, the advertising portfolio selected invests a portion of the base revenues with a particular revenue growth expectation. The problem is; however, the advertising department is not set on exactly what percentage they will be investing in advertising. Depending on the percent revenue planned for investment, three portfolios are equally likely. The advertising department has stated that they will be investing anywhere between 0% and 15% of the base revenue for the given year. This is essentially a random number that is equally likely between 0% and 15%. There are three different advertising portfolios that are split up based on the percent invested in advertising and have a minimum growth rate, a maximum growth rate, and a most-likely growth rate which are all shown in the table below.

For instance, if the advertising department decides to invest 6% of the base revenue, it would select Portfolio B, and it will, most likely, have a return rate of around 9%. That, in return, would increase the base revenue by that return rate. Finance has provided some projections of what the base revenue will be, and there are two scenarios: a good year and a bad year. Both a good year and a bad year are equally likely considering the economic state of the world right now post- pandemic environment. A good year is expected to have a revenue of $127,218,000.00, while a bad year is expected to have a revenue of $101,774,400.00. Finance has also provided that the costs before advertising is expected to be distributed Normally with an average of $85,000,000.00 and a standard deviation of $10,000,000.00. a) Your task is to create a spreadsheet that calculates the expected profit using a simulation model. Please show all your steps, including base case, random number identification and generation, and incorporating randomness into the base case model. b) Replicate this Simulation 1000 times. Then take the data found in the simulations and construct a 95% confidence interval which the facilities department can use to determine a safe bet of how much money they can invest in their new facilities without going over budget. c) What is the probability that BMW will have more than $40M? Explain.

\begin{tabular}{|l|l|l|l|l|} \hline Portfolio \# & Percent invested & Min Rate & Most-likely Rate & Max Rate \\ \hline Portfolio A & 0%,>=5% & 6% & 7% & 8.5% \\ \hline Portfolio B & 5%,>=10% & 8% & 9% & 10% \\ \hline Portfolio C & 10%,>=15% & 8% & 12.5% & 15% \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|l|} \hline Portfolio \# & Percent invested & Min Rate & Most-likely Rate & Max Rate \\ \hline Portfolio A & 0%,>=5% & 6% & 7% & 8.5% \\ \hline Portfolio B & 5%,>=10% & 8% & 9% & 10% \\ \hline Portfolio C & 10%,>=15% & 8% & 12.5% & 15% \\ \hline \end{tabular}