Delete this page before submission MATH 1053 - Quantitative Methods for Business Assignment 2 (SP2 2017) DUE: Monday 8th May by 12:00 noon (lunch time! Not midnight!) Submission Instructions • Assignment submission is via Gradebook. Before submitting, save your file as follows: QMB SP2 2017 Assignment 2 FirstName Surname.docx Replacing FirstName and Surname with your first name and surname. If you have problems submitting, keep proof of your submission attempt and email me [email protected] • Once submitted, do not edit any electronic files of your work. Assignment Instructions • This assignment covers Weeks 4-7 and counts for 15% of your overall grade: Assignment component Mark Business report 60 Appendices 70 Presentation 10 TOTAL 140 • Assignments submitted late, without an extension being granted, will attract a penalty of 10 marks per each working day or any part thereof beyond the due date and time. Please refer to the Course Outline for the course policy regarding extensions. • An incorrectly or partially filled cover sheet will result in lost marks and delays in the marking and return of your assignment. • Not deleting the pages/text as requested will result in a loss of presentation marks. • Your assignment submission should be typed and observe the page limits. Hand-written answers will be disregarded. Delete this page before submission Delete this page before submission Delete this page before submission Delete this page before submission Delete this page before submission Based on your previous report to the CAO (who thankfully did not need his therapist’s number – well done Numbers Ninja!) there are a number of changes going on at Qflix and it turns out your Ninja Brilliance™ is needed once again. First and most importantly, the CAO has decided Qflix should have its own production studio. He’s created a vision board placing Qflix as the home of gritty dramas with big budgets, as well as a space for his favourite project – a show called Game of Q (although legal have suggested there could be some copyright issues with this name). Your job is to help make this dream a reality. Making content requires a lot of planning though – Qflix wants to offer 4 TV genres including Comedy, Drama, Sci-Fi/Fantasy and Films. It is important to get the mix of the number of different shows in each genre right to minimise costs while achieving a target audience of at least 2.5 million subscribers. However, you better guarantee the boss can make Game of Q with the optimal solution, otherwise those company shares of yours are going to become worthless really quickly. The CAO would also like to build Qflix’s reputation as a major sports sponsor. What started as a thought bubble over morning tea last week while eating your colleague Emma’s birthday cake (“Hey, we should broadcast sports!”) has now become a full-blown mission by the CAO, who’s since been turning up to work in his AFL Guernsey and singing the team song. The task of working out the best places to display the Qflix logo at any sporting event to maximize Qflix’s brand exposure has fallen to you. Is it better to place the logo on signage or on player’s tops? Does the best logo placement change based on the sport event being covered? Then comes the follow-up analysis. What sort of viewing exposure of the Qflix logo can be expected during a sporting match? Finally, the HR manager has been sending you scary/but polite/but scary e-mails, asking for a forecast of the increased workforce needs, should Qflix meet the target of 2.5 million subscribers. She needs to know how many personnel Qflix need to employ to meet subscriber demands and it’s never a good idea to upset the person in charge of your working conditions. You need to prepare a report for the CAO and the HR manager too, summarising your analyses. The boss has promised season tickets for the AFL, providing your analysis looks good and you support the same team as him. The HR manager just glares at you - that’s enough, really. The Numbers Ninja needs to make sure Qflix will continue to be a massive success. So one more time, with feeling ... Delete this page before submission UNIVERSITY OF SOUTH AUSTRALIA Assignment Cover Sheet – Internal An Assignment cover sheet needs to be included with each assignment. Please complete all details clearly. When submitting the assignment online, please ensure this cover sheet is included at the start of your document. (Not as a separate attachment.) Please check your Course Information Booklet or contact your School Office for assignment submission locations. Name: Student ID Email: Course code and title: MATH 1053 – Quantitative Methods for Business School: Info. Tech. & Mathematical Sciences Program Code: Course Coordinator: Dr Belinda Chiera Tutor: Day, Time, Location of Tutorial: Assignment number: 2 Due date: by 12 noon, Monday 8th May, 2017 Assignment topic as stated in Course Outline: Case Study Report Further Information: (e.g. state if extension was granted and attach evidence of approval, Revised Submission Date) I declare that the work contained in this assignment is my own, except where acknowledgement of sources is made. I authorise the University to test any work submitted by me, using text comparison software, for instances of plagiarism. I understand this will involve the University or its contractor copying my work and storing it on a database to be used in future to test work submitted by others. I understand that I can obtain further information on this matter at http://www.unisanet.unisa.edu.au/learningconnection/student/studying/integrity.asp Note: The attachment of this statement on any electronically submitted assignments will be deemed to have the same authority as a signed statement. Signed: Date: Date received from student Assessment/grade Assessed by: Recorded: Dispatched (if applicable): [ENTER REPORT TITLE INCLUDING THE NAME OF THE COMPANY] [Enter the date of submission] prepared by [Enter your name]   Introduction (3 marks) Purpose Provide a qualitative description of report contents/problems addressed in the report (covering Appendices 1-4) and what insights the analyses will provide. Write this introduction after you have a clear understanding of the content of your report. Ensure you have given specific details as to what the report will contain. Follow the length guideline (given on the previous page). Highlight and delete this message before submission Do Not Start the Report Body On This Page (Loss of 4 marks) Report Body Highlight and delete this question text before submission Use the questions below to guide your report-style discussion of the main results of your analyses in Appendices 1-4. You may use subsections, tables, graphs etc. You can also use bullet points to report key findings, providing you discuss any relevant points you wish to make. Clearly presented information will help to earn maximum presentation marks. Do not include numerical calculations or spreadsheets – these should be placed in the Appendices. Do quote quantitative results. Do include graphs when requested only. You should have no more than 2 pages of writing (figures and tables excluded) (12 Marks) Appendix 1 Discussion Points Loss of Marks: will occur if you use the terms ‘decision variables’, ‘shadow price’, ‘objective’, ‘objective function’, ‘answer report’, ‘sensitivity report’, ‘binding’, ‘optimality’, ‘feasibility’ and ‘non binding’. Use everyday language. Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Describe your linear programming solution in terms of minimum production cost for the target audience size, and the optimum number of different shows to make for each genre (Appendix 1(b)). For each genre, explain why you are rounding up/down the number of shows (hint: watch out for the impact on the constraints based on your decision!). 4 marks Discuss the potential impact on the minimum production cost for every extra Qflix subscriber and every extra film made. Based on your analysis, is it possible to make an extra film or will the optimal solution change? (Appendix 1(c)). 2 marks Explain the impact on the number of different types of shows made for each genre if the length of a Sci-Fi/Fantasy show is reduced to 30 minutes (Appendix 1(d)). 2 marks Provide a recommendation as to whether the original (Appendix 1(b)) or amended solution (Appendix 1(d)) should be pursued. Also, if you’re brave enough, suggest a better name than Game of Q so that legal will stop scaring your CAO with the thoughts of legal action! 2 marks (14 Marks) Appendix 2 Discussion Points Loss of Marks: will occur if you use the words: statistically independent, statistically dependent, conditional, joint, marginal, complement (or any variant of these). Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Summarise and discuss, using either bullet points or a table, all probabilities calculated in Appendix 2(a) with a focus on a non-technical discussion relating the results to logo placement and the sport being played. 5 marks Create a 100% stacked bar chart of the data in Table 1 (Appendix 2) to include here, with an appropriate figure number and caption. Excel instructions are provided below. 3 marks Provide one insight from the 100% stacked bar chart, quoting approximate percentages. For full marks, refer to the chart by figure number, to support your discussion. Use the 100% stacked bar chart to support a discussion of your calculations in Appendix 2(b). Does it matter which sport Qflix sponsors if the Qflix logo is to be displayed on a media backdrop? 4 marks Excel instructions to create a 100% stacked bar chart To create the 100% stacked bar chart you need the data in the Excel file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page. The worksheet for this question is called Appendix 2. Use EXCEL to obtain a 100% stacked bar chart for the data from Table 2, with the four different Sport labels shown on the horizontal axis. The steps are as follows: Step 1. Refer to Topic 6 in the Excel Booklet for instructions on how to obtain a 100% stacked column chart using data summarised in a table. Step 2. To place Sport labels on the horizontal axis, further editing is required – see the instructions below. Note! If you have a Mac and your Excel version is different to the instructions below, post on the social forum specifying which version of Excel your Mac has, and we can help you out. Windows 1. Select the chart 2. Go to the Design tab and select ‘Switch Row/Column’: Mac 1. Select the chart 2. Go to the Chart Design tab and select ‘Switch Row/Column’: Step 3. Annotate the graph with an appropriate title for both axes. Ensure that the main title of your chart ends with your network ID. (14 Marks) Appendix 3 Discussion Points Loss of Marks: will occur if you use the words: mean, median, symmetric, skewed, boxplot, standard deviation, variance, Q1, Q3, IQR, outliers, Media Equivalency Figure (or any variant of these). Use non-specialist terms – i.e. everyday language. Describe in 2-3 sentences, the aim of the analysis in this section. 2 marks Discuss the variables Number of Times Exposed and Number of Seconds including what they capture. Interpret the chosen measures of central tendency and dispersion in non-technical terms. You can use bullet points or a table to support your discussion (Appendix 3(b)). 4 marks When explaining a value for Logo Exposure to a non-technical audience, often a Media Equivalency figure is quoted, which equates logo exposure to how much would have been spent on a 30-second commercial instead, during the sporting event. To help the CAO understand Logo Exposure, use a multiplier of 1.32 to convert Logo Exposure to Australian dollars. Discuss your chosen measures of central tendency and dispersion for Logo Exposure in terms of the Media Equivalency figure. 4 marks Using the analysis from Appendix 3(d), discuss whether there are any obvious differences in the typical Logo Exposure over the season of a sport and if there is a particular point in the sports season Qflix should aim to have their logo present. Note one other interesting feature from the boxplot you wish to highlight here. A bullet point discussion is OK. 4 marks (14 Marks) Appendix 4 Discussion Points Loss of Marks: if you use the words: slope, intercept, regression, equation, correlation, coefficient of determination, R-squared, R2, r, interpolation, extrapolation (or any variant of these). Describe in 2-3 sentences, the aim of the analysis in this section. Include any limitations you can think of with regards to the data set (Appendix 4(a) will be useful here). 2 marks Include here a scatterplot of Number of Subscribers (‘000s) vs Number of Personnel without the trendline, the regression equation or coefficient of determination. Ensure the axes are properly labelled and you have an appropriate title that includes your network ID (Appendix 4(b)). 2 marks Interpret the general trend of the scatterplot and explain the average number of extra personnel needed based on an extra 1,000 subscribers (Appendix 4(c)). 3 marks Discuss the value of the R-squared coefficient of determination in everyday language and in relation to the variables Number of Subscribers (‘000s) and Number of Personnel. Explain whether the model is trustworthy to provide insight into predicting the average number of personnel required based on subscriber numbers (Appendix 4(c)). 3 marks Discuss the number of personnel predicted if the number of subscribers reach 1,000,000 and the number of subscribers one member of personnel will need to support (Appendix 4(d). 2 marks Discuss whether the model can be used reliably for the target audience of 2,500,000 subscribers. (Appendix 4(d)). 2 marks Highlight and delete the above question text before submission Conclusions and Recommendation (3 marks) Conclusion: Summarise the main findings of your report: base this on your discussion. Do not introduce new information in the conclusion. Do not use direct quotes. Indicate whether the report fulfilled the purpose as stated in the introduction. Recommendation: Base these on your conclusion. Do not introduce new information in the recommendation. Present options for resolving the issue (purpose) presented in the introduction. Be brief – use dot points. Highlight and delete this question text before submission Appendix 1 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. Minimising production costs Qflix will open its own studio called Q Studios to produce in-house content to stream to its subscribers, starting later in the year. Initially Q Studios will produce four genres of content including Comedy, Drama, Sci-Fi/Fantasy and Films (meaning the CAO will finally get to watch Game of Q!). To make Q Studios a viable venture, Qflix needs to target at least 2.5 million subscribers in the first instance. It is thought that a Comedy show can reach an audience of 152,000 subscribers, Drama will be viewed by 253,000 subscribers, 153,000 subscribers will view a Sci-Fi/Fantasy show while 188,000 subscribers will watch a Film. From subscriber viewership analysis of Qflix’s current content, Drama and Sci-Fi/Fantasy are the most popular genres and the combined number of shows in this genre should comprise at least 60% of the total number of shows produced across all four genres. Moreover, Qflix would like to make no more than 6 different Comedy and Sci-Fi/Fantasy shows combined while in order to justify the Film genre, Qflix would like to make at least three films. Pre-production time, in which the shows are filmed and produced before release, also needs to be budgeted. Qflix has allowed 182 days for pre-production with each Comedy show allocated 3% of this time, each Drama show 8% of this time, 5% of this time for each Sci-Fi/Fantasy show and 12% for each Film. The remaining time is used in other pre-production activities not under consideration here Note! when computing the number of pre-production days for each genre, round to two decimal places. Finally, there are a fixed number of production crew available. There are 365 crew members available and typically a Comedy show requires 5 crew members, Drama requires double this amount, Sci-Fi/Fantasy requires a crew of eight people while a Film requires 28 crew members. Qflix would like to know how many shows to make in each genre in order to minimise production cost whilst reaching the desired target number of subscribers. The production cost of a show is estimated to be $420,000 per hour, with each Comedy show running exactly 30 minutes, Drama for 60 minutes, Sci-Fi/Fantasy for 45 minutes and Films running 90 minutes each. (a) (7 marks) Formulate a linear programming model for this problem, filling in the template over the page. This template is taken from the Week 4 lecture (slide 14). Type up the full mathematical model in Word and include it here. For full marks fill in the template provided over the page, clearly indicating: • The decision variables. Define them precisely. • The objective. Using your decision variables, formulate the objective function. • The constraints. Using your decision variables, formulate these constraints. Linear Programming Model Template Decision Variables Objective and Objective Function Constraints (b) (3 marks) Before you begin! Part (a) needs to be completed first before entering the model in Excel or (i) you will likely get (b)-(d) wrong; and (ii) you’ll miss out on 7 marks! To complete this question now you need to edit the Excel Linear Programming template in the assignment file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page. The Excel Linear Programming template for this question is in the worksheet Appendix 1. For full marks complete the following two steps: 1. Enter your model from part (a) in the template. All EXCEL output will need to bear your e-mail ID. To ensure this, you will need to save your EXCEL file as ‘E-mail ID Assignment 2.xlsx’ BEFORE you run Solver. In addition, your constraint names should begin with your initials, e.g. BC Target Audience if your initials are BC. 2. Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report. In this Appendix, provide a screenshot of your solved EXCEL spreadsheet and the Answer and Sensitivity reports. Do not discuss the output, save this for the report body! Hint #1: If the solution does not contain whole numbers, this is OK. You will be asked to address this issue in the report write-up. Hint #2: The Excel booklet, Week 4 lecture notes and Stevenson supplement (Lecture notes page) will also be useful to help you set up your Linear Programming model, as well as interpret the output and perform the analyses in parts (c) and (d). (c) (6 marks) For the problem solved in part (b), interpret the following shadow prices: i. The shadow price for the constraint corresponding to the sentence: “To make Q Studios a viable venture, Qflix needs to target at least 2.5 million subscribers in the first instance” ii. The shadow price for the constraint corresponding to the sentence: “... in order to justify the Film genre, Qflix would like to make at least three films.” For each interpretation state: i. The shadow price; ii. The range of feasibility; and iii. The impact of the shadow price on the objective function for changes within the range of feasibility. Explain in a single generalised statement what a zero shadow price indicates and why a resource would have a zero shadow price (you do not need to explain for each constraint with a zero shadow price nor calculate a range of feasibility). (d) (4 marks) As it turns out, there is a small problem with your solution generated in part (b). You should find your solution in part (b) suggested Qflix makes exactly 0 Sci-Fi/Fantasy shows, however knowing how attached the CAO is to producing Game of Q, you would like to present him with an alternative solution that allows for at least one Sci-Fi/Fantasy show, otherwise you suspect you won’t be a Numbers Ninja for much longer. You’re also feeling nervous about those company shares you recently bought with your employee discount. In a moment of Ninja Brilliance™ you wonder whether reducing the length of time of a Sci-Fi/Fantasy show from 45 minutes to 30 minutes will result in an optimal solution involving at least one Sci-Fi/Fantasy show. Which of the Solver reports helps you answer this question? Show your calculations for the allowable decrease in cost when making a Sci-Fi/Fantasy show. Based on the proposed change, will the solution in part (b) still be optimal? Attach the new Answer Report ONLY, for the scenario in which the length of a Sci-Fi/Fantasy show is 30 minutes. Do not discuss the rest of the Answer Report – you will do this in the report body! Hint! When testing the change to a 30-minute Sci-Fi/Fantasy show, you will have to recalculate the cost of a Sci-Fi/Fantasy show running for 30 minutes and compare this new cost with the allowable decrease. TOTAL 20 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission Appendix 2 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. Qflix Sponsors Sports Aside from producing made-for-TV content in QStudios, Qflix are also keen to stream sports content to its subscribers. As a first step towards moving in to the sports genre and compete with current Free-To-Air sports broadcast and Foxtel Sports, Qflix would like to stream sponsored high profile sporting events. By sponsoring sports Qflix intend to increase awareness of their brand and present themselves as a credible sports channel competitor. Specifically, Qflix are thinking of sponsoring a combination of AFL, Rugby, Cricket and/or Tennis to increase their visibility as a brand via Qflix logo placement. However Qflix is unsure where their logo should be placed during a match to ensure their best ‘share of voice’, where ‘share of voice’ is an awareness of the sponsoring brand by sports spectators. The following data summarises the share of voice noticed by 4,219 spectators across four different sports, in which spectators were asked to list all locations they noticed a sponsor’s logo placement. Use this data to compute the requested probabilities below and over the page. Logo Placement Sport Total AFL Rugby Cricket Tennis Board Signage 181 178 386 242 987 Oval/Court 160 156 151 142 609 Media Backdrop 423 428 274 282 1407 Player’s Top (Back) 287 198 0 2 487 Player’s Top (Front) 203 335 12 179 729 Total 1254 1295 823 847 4219 Table 1: Logo Placement by Sport (a) (8 marks) Using Table 1, calculate the following probabilities. For full marks include both the appropriate probability statement and the calculation. Present your answer to 4 significant digits. You do not need to write a sentence summarising the results. i. The probability the sponsor logo was noticed on the Oval/Court and the sport was AFL. ii. Given the sport is AFL, the probability the logo was noticed on the front of the player’s top. iii. The logo was noticed on the front of the player’s top if the sport is Rugby. iv. The proportion of Cricket matches at which the logo was seen on the player’s top (front). v. Of those sporting events that were Tennis matches, the probability the logo was noticed on the front of the player’s top. (b) (6 marks) Qflix has discovered that producing a Media Backdrop is one of the most inexpensive options when having logo placement, however are unsure whether it is effective for all or only some of the four sports they are considering sponsoring. Using the data in Table 1, determine whether a sponsor’s share of voice via a Media Backdrop is statistically independent across the four sports, calculating all relevant probabilities. For all calculations include both the calculation and the appropriate probability statement. You do not need to write a sentence summarising the results nor discuss the results. Present your answer to 4 significant digits. TOTAL 14 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission   Appendix 3 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. Typical TV Audience Size and Qflix Logo Exposure Before you begin! To complete this question you need the data stored in the Excel file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page. The worksheet for this question is called Appendix 3. Further information about the data for Appendix 3 can be found in the Excel file. A complementary analysis for any sports sponsorship deal is understanding the return on investment (ROI) for Qflix logo exposure, as their motivation for sponsorship is the sales-driven goal to convert sports crowds to subscribers. Of interest to Qflix is the typical number of times a logo is shown on TV during a match or game and the typical logo exposure length, rounded to the nearest second. This data can be combined through a further calculation (explained below) to give the Logo Exposure of a sponsor’s logo at a match. The potential financial equivalence of the exposure, if Qflix were to instead pay for a commercial during the sports event, can also be worked out from this exposure (you get to do this in the report ). Qflix has purchased generalised data on the number of times a logo is shown on TV during a match or game and the logo exposure length (seconds). These statistics were from 500 previous sporting events and as usual, it falls to the Numbers Ninja to analyse it. The data can be found in the file Assignment 2 Data.xlsx’ (worksheet Appendix 3). Use this data to answer the questions below. Note: please do not include a printout of the data  (loss of 5 marks!) (a) (2 marks) The first thing Qflix needs for the analysis is the Logo Exposure at each game. In Column C in the file Assignment 2 Data.xlsx’ (worksheet Appendix 3) calculate the logo exposure for each match using the formula: Logo Exposure = Number of Times Exposed x Number of Seconds Exposed Annotate all 3 column names with your initials, e.g. Number of Times Exposed BC, Number of Seconds Exposed BC and Exposure BC (if your initials are BC!). Include a screenshot here of Columns A-C, Rows 1-10 of your completed spreadsheet. For full marks, also state the variable types of Number of Times Exposed and Number of Seconds Exposed? (I.e. Quantitative (Continuous or Discrete) or Qualitative (Nominal or Ordinal)). (b) (5 marks) Using Excel, obtain Descriptive Statistics, including Quartile 1, Quartile 3 and the IQR, of Number of Times Logo Exposed, Number of Seconds Exposed and Logo Exposure (Columns A-C). Based on the descriptive statistics, which measure of central tendency and dispersion would you use for each variable? Explain briefly. Round all output to the nearest whole number using the button circled below: For full marks include screenshots of each set of descriptive statistics here. Windows Instructions: See the EXCEL booklet, Topic 7, Summary Measures, for instructions on producing a descriptive summary including the extensions include Quartile 1, Quartile 3 and the Interquartile Range (IQR). Mac Instructions: varies depending upon whether you have the analysis toolpack. Follow these steps to see which one applies to you: 1. Start by checking if you have the analysis toolpack: Go to Tools > Excel Add-ins. In the window that pops up, if you see “Atp” select it and press OK. You can now access the toolpack via Tools > Data Analysis. Follow the Windows instructions which will work quite nicely  2. Didn’t see “Atp” (the analysis toolpack)? You will need to install StatPlus (see video on Assignments page). Once StatPlus is installed, see the 2010 EXCEL booklet (online) and go to the Mac section at the back. After producing the descriptive summary tables you will need to: (1) change the title of the table to include your network id; (2) Check whether there is the label Percentile 75% (Q2) in the table. If so, change it to Q3 (yes, Q3 – StatPlus has labelled it incorrectly – you can’t trust programmers!!! ). If you don’t see it there’s no problem  (c) (3 marks) Extend the analysis in part (b) to include outlier calculations for Logo Exposure only, using the 1.5xIQR Rule. Round the values of Q1, Q3 and IQR to the nearest whole number as in part (b) before performing the calculations. Use this rule to determine whether there are any outliers in Logo Exposure. You can perform the calculations either in Excel OR type them out in Word – either way make sure to show your full working and decision making process when you present your solution. (d) (5 marks) Below is a set of side-by-side boxplots of Logo Exposure during four different phases of any sporting season: the initial round of matches, the middle set of matches, the finals matches (e.g. semi-finals) and the grand final itself. Based on the boxplots below, use three bullet points in total to discuss: (i) Whether there are outliers(s) present – explain how the boxplots below supports your argument. (ii) The shape of the distributions (symmetric, skewed left or skewed right). Support your discussion by using the boxplot by referring to the location of the median, whisker lengths and presence/lack of outliers. (iii) The location of the medians (lower, centre, upper end of box) and their impact on the distribution shape. Note: do not present 4 different discussions – three bullet points summarising the boxplots will be sufficient. For the purposes of making comparisons which would be the single most appropriate measure of central tendency and dispersion? Simply state your choice and give a brief reason why. For full marks: before submitting your assignment annotate the boxplot below with an appropriate title that includes your network ID otherwise you will receive 0 marks for the boxplot. TOTAL 15 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission Appendix 4 - [Enter a suitable appendix name] Highlight and delete the question text below before submission. Include full details of your working out in this appendix. Workforce Planning – Hiring More Personnel to Meet Growing Subscriber Numbers Before you begin! To complete this question you need the data stored in the Excel file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page. The worksheet for this question is called Appendix 4. Further information about the data for Appendix 4 can be found in the Excel file. Growing the number of Qflix subscribers is making the head of Human Resources a little nervous. It’s all good and well to talk about securing the customer base and growing Qflix’s reach by streaming sports, but there also needs to be enough people in the Qflix workforce to handle the influx of subscribers, otherwise the reputation of Qflix could be damaged based on poor reviews of customer support and service. However, getting the balance right is not as easy as it sounds. Having too many employees will increase Qflix’s costs unnecessarily through paying excess salaries, while having too few employees would lead to high overtime costs. It also risks customers left hanging on hold for a really long time listening to horrible hold music while the Qflix employee bangs their head against a stress ball. To enable appropriate workforce planning to meet new demands based on an increased number of subscribers, Qflix has had to purchase data capturing the number of subscribers to a service (e.g. other streaming services, online shopping etc.) and the number of personnel employed at each company from which data was taken. The spreadsheet has landed in your lap (not really, they drop-boxed it to you) and you now need to perform the analyses requested below, to help with workforce planning. Considering the Head of HR oversees employment conditions, you need to make sure the analysis is performed well otherwise you’ll end up sitting somewhere where you’ll be very hot in summer and very cold in winter ... Please do not include a printout of the data  (loss of 5 marks!) (a) (2 marks) Use EXCEL to calculate the maximum Number of Subscribers (‘000s) in the data using the max() function, as shown below. Replace BC with your initials (unless you are a BC) and provide a similar screenshot as below, showing your answers including what you entered in to the max function. Unsure how to use the max function? Check the Social Forum for a hint in my top post  For full marks screenshot your completed Excel calculation here. (b) (6 marks) You are interested to predict Number of Personnel. As a first step, use EXCEL to draw a scatterplot of Number of Subscribers (‘000s) vs Number of Personnel. Annotate the axes of this plot and include a title that is descriptive and contains your network ID. Now compute the correlation between these two variables using the CORREL function (Topic 9 in the Excel Booklet). In this Word document, type the Excel calculation you performed to compute the correlation coefficient. Now save a copy of this plot for the Report. As your second step, now use EXCEL to add a trendline to the scatterplot and display the regression equation and coefficient of determination (R-squared value) (Topic 9 in the Excel Booklet). Include a copy of the scatterplot with the trendline here (the scatterplot without the line will be used in the report as it does not contain mathematics, while the plot with mathematics will appear in this appendix ). Comment briefly on the scatterplot and correlation coefficient and whether a linear model is appropriate for this data. (c) (7 marks) State which is the dependent and which is the independent variable. Briefly discuss the value of the intercept, the slope and the coefficient of determination (R-squared value). Using the interpretation of the slope from the regression model, approximately how many subscribers will a member of personnel need to support? (d) (6 marks) Use the simple regression equation from part (b) to predict the average number of personnel required if there are 1,000,000 subscribers. Comment on the accuracy of the prediction. Would there be any issues with the prediction if you were to predict the average number of personnel required if there are 2,500,000 subscribers? Discuss briefly (no calculation needed). TOTAL 21 MARKS Please provide the requested non-quantitative analysis in the report body. Highlight and delete the above question text before submission