I need help with this assignment
Case Report #3 Worth 150 Points Total Fall 2022 NAME: ____________________________________________________ By completing and submitting this case report, I hereby certify that I have abided by the exam rules and the course Academic Integrity Policy. Please compose your case report as a MS Word file. The file should be saved as “lastname report 3”. The completed report must be submitted to the D2L Dropbox by 11:59 pm ET on Friday, December 9, 2022. Late papers will not be accepted and result in a grade of 0. The case report is to be completed independently. Only the following resources may be used: the textbook and assigned readings and other course materials linked directly through the course D2L shell or the course CengageNOW page; Microsoft Excel and Excel help files. Any questions about the report must be posted in the “Case Report 3” discussion board on D2L. Good luck! Introduction One final case for you to crack! A local apparel producer, Steel City Gear, needs help, and fast! Their operations expert quit recently to join the Peace Corps, and they were in the middle of two important tasks: a manufacturing floor reorganization project and deciding how to allocate their advertising budget between TV, radio, and online advertisements. The marketing manager also wants to take advantage of your expertise by analyzing an upcoming promotional campaign: a t-shirt giveaway for each home game that the Steelers win this year. The departed operations specialist left some spreadsheets on the office computer relating to the project, but they are unsure how to make best use of the information. The marketing manager consulted Vegas betting sites to ascertain the probability that the Steelers win each of their home games this season, but does not know how to use this data to estimate the cost of the promotion. Steel City has provided you with as much information as they have, and it is up to you to determine the best quantitative approach to answering their questions. Steel City has also made it clear that it is crucial that you explain what you are doing in a way such that a group of people unfamiliar with quantitative analysis can understand what you are doing and feel comfortable with the results. If you just provide a numerical answer to their questions, while still helpful, this will not meet their expectations for the project. Be sure to include all calculations. Any Excel files used to complete the case MUST be turned in as well, but the report itself must contain all critical information needed by the client. Deliverable Steel City has requested that you answer their questions in the form of a business report in MS Word format (hint: there are several canned report formats in MS word to spice things up a bit). Simply typing in answers to their questions below will be construed as a lack of professionalism. The writing must be free of errors and easy to understand. The exact design of the report is up to you, as long as the report format is used correctly and professionally, and all issues are addressed in an organized and coherent manner. As is your policy, you are to include any equations, calculations, and Excel files that you have used to be completely transparent. Any graphs or diagrams should be included in the body of the report and labeled properly. Detailed calculations may be included in an appendix to the main the report. THE CASE The operations manager left the following files on the computer: Project Activity Information Activity Immediate Predecessors Optimistic (weeks) Most Likely (weeks) Pessimistic (Weeks) A — 3 4 5 B A 4 6 10 C — 2 4 6 D C 3 4 6 E B, D 1 2 3 F E 2 4 6 G B, D 6 5 14 H G 6 8 10 I F, H 3 4 5 Marketing Data from Ad Agency Type of Advertisement Exposure per Ad Cost per Ad TV ads 1-6 12,000 30,000 TV ads 7 and over 5,000 30,000 Radio ads 1-12 6,000 9,000 Radio ads 13 and over 3,600 9,000 Online ads 1-18 3,000 4,000 Online ads 19 and over 2,400 4,000 Game 1 2 3 4 5 6 7 8 Probability the Steelers Win 0.5 0.6 0.45 0.4 0.35 0.55 0.65 0.7 The owner of Steel City has hired you to help with the three tasks described above. Specifically, the owner has two requests: 1. Develop some metrics surrounding the reorganization project. They want to visualize the project map, and better understand the timeline of the project. In particular, the owner is very concerned about the finish date of the project and wants to know the expected completion time and how far in advance the project should be completed in order to assure a .95 probability of completing the project on time (assuming project finish times follow a normal distribution). Be sure to document ALL STEPS needed to reach these calculations. 2. The owner wants to maximize total exposure across all types of advertisements (TV, Radio, and Online). There is one important fact that must be accounted for: the more you advertise in a certain medium, the less effective they become. For instance, consider the television ad data above. The first 6 tv ads will each reach 12,000 people. Each subsequent tv ad will only reach 5,000 people. How many of each type of ad should be purchased? How many potential customers will be reached? What is the effect of increasing the advertising budget by 10%? By 20%? You must document each step you take to solve the problem! The following guidelines must be followed when selecting the type of ads: a. The total advertising budget is $752,000 b. The ads must reach at least 300,000 customers c. Radio ads must be more numerous than TV ads. Use at least twice as many radio as TV ads d. TV ads are limited. Use no more 12 TV ads. e. The amount spent on TV ads should be at least $330,000 f. The amount spent on radio ads should be less than $300,000 g. The amount spent on online ads should be at least $90,000 3. Set up a spreadsheet simulation model with 1,000 trials for which whether Pittsburgh wins or loses each game is a random variable. Suppose for each game that the Steelers win, Steel City will spend $4,500 on a t-shirt giveaway following the game. What is the mean and standard deviation of the number of wins during the season? What is the mean and standard deviation of the cost of the promotion?