Topic: Math/Physic/Economic/Statistic Problems
tutorthammy
Problem_Set_2_B_Directions_v2.xlsxSheet1
| PROBLEM SET 2 -B | |
| Read & Study: Unit 3 and 4 videos/notes/examples/homeworks/solution guides. | |
| Using your PS2-A -- corrected with the PS2-A solution guide, complete PS2-B. | |
| Prob Set 2-B | For each case, (1) Complete the BFDA -- build a simulation model, replicate the model, organize results in a table, |
| and provide conclusions (answers). (2) Summarize in a Business Memo. (3) Submit NLT the Unit 4 class meeting. | |
| Perform analysis work (complete BFDA) for these two cases in one excel file. For each case, use 1000 trials & after obtaining the results you will use, be sure to freeze the 1000 values of the objective measures. | |
| Case 1 | UD Pizza -- 3-Year Financial Analysis. |
| The managers of UD Flyer Pizza ask you to do a 3-year financial analysis of total profit -- based on the following data and information the managers have gathered. Sales volume in the first year is estimated to be 100,000 units and is projected to grow each year at a rate that is normally distributed with a mean of 7% and a stdev of 4%. The selling price in year 1 will be $10 (average over the total units sold) and the price increase in each of the following years is estimated to be normally distributed with a mean of $0.50 and a stdev of $0.05. Per-unit variable cost is $3 and annual fixed costs are $200,000 for year 1. Per-unit variable costs are expected to change by an amount normally distribued with a mean of 5% per year and stdev of 2%. Fixed costs are expected to increase each year estimated as normally distributed with a mean of 10% per year and stdev of 3%. | |
| In addition to estimated 3-yr profit, suppose the UD Flyer Pizza general manager also wants you to estimate | |
| (1) the likelihood that the 3-year profit will be over $1,800,000, (2) over $2,000,000, and (3) risk that the profit will not | |
| show an increase over the three years, i.e. the 2nd and 3rd year profits will, on average, be no more than the 1st year. | |
| Since the 1st year profit is estimated at $500,00, this is the same as the probability that 3-yr profit is <= $1,500,000. | |
| Extra Question: | |
| The manager asks one more question: If we spend $50,000 each of the 3 years on advertising & it is estimated | |
| to result in sales growth that yields 108,000 units the first year and growth averaging 10% for years 2 and 3 (same stdev | |
| of growth), what are the answers to the same business questions? Assume marketing costs will not change for years 2 | |
| and 3. Note, for the third question, since with advertising the first year profit is $506,000, this is the same as the | |
| probability that 3-yr profit is <= $1,518,000. | |
| Case 2. John's Bicycle Repair Shop. The owner John wants to do a study to better understand the shop. His best estimate of customer arrivals is that the time between is exponential with an average of about 1 hr 15 min. John is the only repair person. Based on past experience, he knows that about 70% of repairs are "minor" and take anywhere from 0.25 to 1.5 hours to complete (about equally likely anywhere in that range.) The other 30% are "major" and the repair time is equally likely between 1 and 3 hours. John would like to understand several measures: average time a bicycle is "in the shop" (arrival to repair complete), the 10%tile and 90%tile of "time in the shop," the average number of repairs completed in a day, the average time(hrs) he stays in the shop past 8 hours due to the way he operates (always completes repairs the same day he accepts them), and the proportion of time John is idle. Use the full BFDA: Issue, BusProblemDefinition, BusData, Analysis, Bus Concl/Insights/Rec. In your analysis, develop a model of 8 hours or 8 customers (which ever comes first) since this is the way John operates. He accepts repair jobs through 8 hours except when he gets 8 jobs and then he closes the shop. He always completes repairs in the same day -- even if he has to work extra long. Use 1000 replications of your model to develop your results and conclusions. | |
| NOTE: For Case 2, there are three random aspects of this repair shop process: (1) time to next arrival, (2) whether a repair is minor or major, and (3) the repair time. Your model will need to choose a value for each. Use different RAND values for each. | |
| HINT: For Case 2, can build a model with 8 customers; however in the "time of arrival" calculation use an =IF statement to see if the arrival time exceeds 8 hours. If time is > 8 hours, then print something like "not open." Will need to use =IF for all the other columns to print something like "na" if the time of arrival column contains "not open." Can do this in TWO Steps -- (Step 1) build model of 8 customers without the =IF statements that check for 8 hours and test this model for accuracy. (Step 2) Add the =IF statements to create a complete model and test for accuracy. NOTE: If you cannot make Step 2 work, for most of the credit just do Step 1 and answer as many of John's questions as you can. Hand-in either Step 1 or 2 (not both)-- identify which at the top of that Excel worksheet. | |
| DELIVERABLES: | |
| (1) Excel. Submit your excel file -- with one worksheet for each case. | |
| (2) Business Memo. In Excel at the bottom of your analysis work for each case, provide a business memo to the manager | |
| who requested the analysis. Use Date, To, From, Subject format with just 3 memo elements: 1. Business Questions, | |
| 2. Analysis Results (table(s)), and 3. Conclusions & Recommendations. Most of this memo can be copied/pasted from your BFDA work. | |
| PS2-B is due NLT the Module 4 class meeting. |
Problem Set 2 - Excel
