Directions:
The homework must be submitted to Moodle as an Excel spreadsheet.
Include the Honors pledge in Worksheet 3 .
Please see instructions below regarding worksheet naming conventions.
Name your Excel document with solution as following:
BUS355_HW4_last name_first name.xlsx
Upload to Moodle by 10:00 PM on November 10 . Check one more time after you upload to ensure that your upload is successful. You are completely responsible for a successful upload. If you choose, you can upload multiple times. The second upload will overwrite the first upload.
Problem 1
Tom Smith is an MBA student at the University of Chicago. His long-term goal is to open his own restaurant. So, he plans to start with a food truck to gain some experience.
Tom conducted a profitability analysis of a sample of food trucks in Chicago. He estimated the non-labor fixed costs of operating a food truck to be $50 per day. He also estimated the variable costs of food to be $4 / meal served. Among many uncertainties, there were three uncertain variables that tend to dominate the profitability equation: the revenue per meal, the number of meals sold, and the labor costs. Based on the information collected from many food trucks, Tom was able to estimate the distributions of these three crucial uncertain variables, as follows.
The revenue per meal is normally distributed. The mean is $10; the standard deviation is $1.2.
The number of meals sold per day follows a Poisson distribution with the average being 50 meals per day.
The daily labor costs follow a continuous uniform distribution between $120 and $150.
The daily profit is calculated by the following model:
Daily profit = X ) – Daily labor costs – $50
Assume that the revenue per meal, the number of meals sold, and the labor costs are independent of each other.
Simulate 1000 values of the daily profit in Excel for the above problem. Your Worksheet 1 should be named Solution and should show all the Simulation work done in Excel.
Also in Worksheet 1 , based on the 1000 simulated values of daily profit, provide answers to
Average daily profit
Standard deviation of daily profit
Probability of loss
Hint: COUNTIF function in Excel can be used to count the number of negative daily profits.
You use Worksheet 2 to construct the histogram for the daily profits. Show the histogram in Worksheet 2. Name Worksheet 2 as Histogram.
Observe the histogram of daily profits. Answers to the questions below must be provided in Worksheet 2 .
Which probability distribution do you think can be used to approximate the distribution of the daily profits?
In order to pay off his student loans, Tom would need to earn approximately $100 per day before tax. Using the distribution that you think is relevant, find the probability that the daily profit will be greater than $100.
