Statistics Solution

BUSN260 Fall 2018 Homework #6 Due date: Dec 4 2018; 11:59pm

Homework Assignment (5 Points)

Instructions: Turn in one Excel file with a tab for each question listed under Problems. Name your Ex- cel file Lastname Firstname BUSN260 HW6.xls. Make sure to add text boxes with detailed interpretation of the results to get full credit.

Problems

1. Based on historical data, daily demand of a product is normally distributed with a mean of 200 and standard deviation of 50. Develop a spreadsheet simulation model by simulating 300 observations of demand. After simulation freeze the simulated demand values. Use Analysis ToolPak → Histogram create a histogram visualizing the demand distribution using bins: 100, 125, 150, 175,...,300. Use Analysis ToolPak → Descriptive Statistics to compute the descriptive statistics metrics of the simulated values. Interpret skewness of the simulated demand.

2. Based on historical data, number of patients arriving to the Emergency Department (ED) on a given day is uniformly distributed with a minimum of 10 and maximum of 30. Each patient’s waiting time (i.e. time spent in the waiting room until being seen by a provider) is normally distributed with a mean of 40 minutes and standard deviation of 12 minutes. Develop a spreadsheet simulation model for the total daily waiting time by simulating 500 observations of daily number of patients arriving to the ED and 500 observations of waiting time for each simulated patient. (Hint: For each pair of the 500 simulated observations, create a new column to compute: Simulated total daily waiting time = simulated number of patients arriving to the ED ∗ simulated waiting time). After simulation freeze the simulated total daily waiting time values. Use Analysis ToolPak → Histogram create a histogram visualizing the sales distribution using appropriately selected bins. Use Analysis ToolPak → Descriptive Statistics to compute the descriptive statistics metrics of the simulated values. Interpret median, maximum and skewness of the simulated total daily waiting time values.

3. Based on historical data, daily demand for a product takes values 20, 40 or 65 and follows a discrete probability distribution as follows: p(20)= 0.25, p(40)= 0.6, p(65)= 0.15. Simulate 500 observations from this discrete distribution using RAND() and VLOOKUP(). Use Analysis ToolPak → Histogram create a histogram visualizing the sales distribution using appropriately selected bins. Interpret the histogram.

4. Based on historical data, of all the patients arriving to an urgent care clinic, 20% are pediatric patients (age < 18) and 80% are adult patients (age >= 18). Given 100 patients arrive to the urgent care clinic daily, simulate the number of adult patients running 500 simulation scenarios. Use Analysis ToolPak → Histogram create a his- togram visualizing the sales distribution using appropriately selected bins. Find the expected value of the simulated number of adult patients and compare to the expected value of the binomial distribution BINOM(100,0.8).

1