Excel Sheet needed for the simulation model

based on the below scenario please answer the following questions

Situation

A hospital is trying to decide how many new rooms to add to a new wing they are building.Each new room will contain one bed.

There are currently 100 rooms. They are considering adding anywhere from 100 to 500 additional rooms. We need to build a model that will help the hospital determine how many new rooms to add.

Fixed Cost

Each new room will cost $80,000 for construction and equipment costs.

Demand

Over the last few years, the weekly number of rooms occupied (or you could also think of this as 'number of patients' per week) has been normally distributed with an average of 150 anda standard deviation of 75.

In addition to the historic demand described above, the only other hospital in town is closing down, and it is expected thatthis will dramatically increase the number of patients (or rooms occupied)per week. We don't have a good estimate of how many to expect -- We will assume that we could see anywhere from 100 to 300 additional patients per week. HINT: Model this as a uniform distribution where all integer values within that range are equally likely to occur.

Variable Costs

There are times when the number of patients exceeds the number of available beds.When this happens, the hospital is forced to send excess patients to another hospital in the area. Each time this happens, there is a transfer cost of $10,000 in ambulance and other emergency services.

Each bed occupied by a patient requires a weekly maintenance expense of $800 to cover costs for maintenance andrestockingmedical supplies. (Unused rooms/beds don't contribute to variable costs for the week. This cost is applied to all new and exisiting rooms/beds)

Build a model that will allow you to simulate 52 weeks of patient/bed demands.

Simulation

Use the model to estimate the costs of construction plus the variable expenses outlied above. Simulate your model showing the costs for adding 100 to 500 new rooms, in increments of 50. Using a data table, run 1000 iterations of your model.

Analysis

To compare the decision options, calculate the average, standard deviation, minimum, and maximum of total 52 week cost for each. A template has been set up on the Analysis worksheet for these calcuations.

Q1- Of all of the options for Beds Added, which has the highest risk of having a total cost greater than $100,000,000?

Group of answer choices

100

150

200

250

300

Q2- Which of the choices for Added Rooms would involve the lowest level of risk?

Group of answer choices

500

450

400

350

300

For the followingquestions, make a copy of the worksheet on which you built your model. You will need to modify inputs to your model to test your calculations. Making a copy of your completed model will keep you from having to redoing your work.

Q3- This question will require you to alter your model to test your calculations.

SetTotal Patientsfor each week to 210, and set theAdded Rooms to 100.

What is the TotalCostwith these settings?

Q4- This question will require you to alter your model to test your calculations.

SetTotal Patientsfor each week to 210, and set theAdded Rooms to 100.

What is the sum of allTransfer Cost(all weeks) with these settings?

Q5- This question will require you to alter your model to test your calculations.

SetTotal Patientsfor each week to 210, and set theAdded Rooms to 100.

What is the sum of allWeekly Bed Use Maintenance Feeswith these settings?