PROBLEM SET 2-ARead & Study:Unit 3 and 4videos/notes/examples/homeworks/solution guides on analysis with simulation modeling.Prob Set 2-AFor each case below, complete thefirst three BFDA steps(Bus Issue, Bus Prof Defn, Data) and then identifya possible simulation model strucuture -- what will be represented in rows?In columns?Any other cells?Submit not later than 3 days prior to the Unit 4 class meeting.A solution guide will be provided.THEN, use the solution guide to complete PS2-B.Don't forget, PS2-B is due by 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 freezethe 1000 values of the objective measures.Case 1UD Pizza -- 3-Year Financial Analysis.The managers of UD Flyer Pizza ask you to 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 additionto 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 notshow 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 estimatedto result in sales growth that yields 108,000 units the first year and growth averaging 10% for years 2 and 3 (same stdevof growth), what are the answers to the same business questions?Assume marketing costs will not change for years 2and 3.Note, for the third question, since with advertising the first year profit is $506,000, this is the same as theprobability that 3-yr profit is <= $1,518,000.Case 2. John's Bicycle Repair Shop.The owner John wants to 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 in TWO Steps-- (Step 1) build model of 8 customerswithoutthe =IF statements that check for 8 hours and test this model for accuracy.(Step 2) Add the =IF statements to complete model and test for accuracy.NOTE:If you cannot make Step 2 work, formost of the creditjust 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.