Excel Project Each student work inde ndentl or each rou work 'ointl is re uired to submit a sin le Excel le through Titanium. The excel le should include a single worksheet as the cover page indicatin rou member's name and several worksheets one for each uestion to address each guestion with detailed analysis and short summary. Please show all your analysis supporting your solution. lGrading will be based on correctness of both analysis and nal solution to each Question. 1. {2 Points} The University of Cincinnati Center for Business Analytics is an outreach center that collaborates with industry partners on applied research and continuing education in business analytics. One of the programs offered by the center is a quarterly Business Intelligence Symposium. Each symposium features three speakers on the real-world use of analytics. Each corporate member of the center {there are currently 1B] receives five free seats to each symposium. Nonmembers wishing to attend must pay STE per person. Each attendee receives breakfast. lunch, and free parking. The following are the costs incurred for putting on this event: Rental cost for the auditorium 5150 Registration processing 58.50 per person Speaker costs [3 speakers 53m each} Continental breakfast 54110 per person Lunch SIDE] per person Parking 55.00 per person a} Build a spreadsheet model that calculates a prot or loss based on the total number of 10-0 nonmember registrants. bl Use Goal Seek to nd the number of nonmember registrants that will make the event break even. . {2.5 Points} lGrear Tire Company has produced a new tire with an estimated mean lifetime mileage of 35,5DB miles. Management also believes that the standard deviation is 5,ooo miles and that tire mileage is normally distributed. To promote the new tire, Grear has offered to refund some money if the tire fails to reach ECLBDU miles before the tire needs to be replaced. Specically. for tires with a lifetime below some miles. Grear will refund a customer $1 per 10D miles short of SQDBD. a} For each tire sold, what is the expected cost of the promotion? b] Please perform the simulation for 100!) times. what is the probability that Grear will refund more than $50 for a tire? {2.5 Points] The management of Brinkley Corporation is interested in using simulation to estimate the profit per unit for a new product. The selling price for the product will be 545 per unit. Probability distributions for the purchase cost, the labor cost, and the transportation cost are estimated as follows: Pmcuramont Probability Labor Probability Transom-tattoo Probahllty m l5} test that {$4 [5} 1o o.25 2o o.1o s p.25 11 o.45 22 0.25 5 o.25 12 oao 24 0.55 25 oao a] construct a simulation model to atlmate the mean profit per unit. b] Management believes that the project may not be sustainable if the profit per unlt is less than 55. Use simulation to dmate the probability that the profit per unit will be less than 55. a. [3 Points} Galaxy Co. distributes wireless routers to Internet service providers. Galaxy procures each router for SIG from its supplier and sells each router for $125. Monthly demand for the router is a normal random variable with a mean of 10] units and a standard deviation of 2D unit5. At the beginning If each month. Galaxy orders enough router5 from its supplier to bring the inventory level up to l router5. If the monthly demand is less than 1011'. Galaxy pays $15 per router that remains in inventory at the end of the month. If the monthly demand exceeds l, Galaxy sells only the it'll) routers in stock. Galaxy assigns a shortage cost of 53D for each unit of demand that is unsatisfied to represent a loss-of; goodwill among its customers. Management would like to use a simulation model to analyze this situation. Perform the simulation for l tim es. what is the average monthly prot resulting from its policy of stocking l routers at the beginning of each month
