Date 27-Jul 28-Jul 29-Jul 30-Jul 31-Jul 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug 11-Aug 12-Aug Day 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Momma Mia! Boxoffice (millions $) 11.0878 25.8976 34.5510 37.0481 39.8750 41.9167 47.2878 53.8078 63.7142 70.9621 74.7443 76.6116 78.0833 79.6025 83.6954 90.3642 93.6196 Type your name (Last Name, First Name) in cell E2 Go to the next worksheet, called Problem 1 Worksheet , and do all your anlysis for problem 1 on that sheet. Date 27-Jul 28-Jul 29-Jul 30-Jul 31-Jul 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug 11-Aug 12-Aug 13-Aug 14-Aug 15-Aug 16-Aug 17-Aug 18-Aug 19-Aug 20-Aug 21-Aug 22-Aug 23-Aug 24-Aug 25-Aug 26-Aug 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep Day 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Momma Mia Boxoffice 11.0878 25.8976 34.5510 37.0481 39.8750 41.9167 47.2878 53.8078 63.7142 70.9621 74.7443 76.6116 78.0833 79.6025 83.6954 90.3642 93.6196 Linear Fit Ln(Momma Mia) 2.4058 Exponential Fit 1/Time 1.0000 S Curve Fit Time 1 Time^2 Time^3 1 1 Third Order Poly Fit Ln(Time) 0.00 Logarithmic Fit Weibull Gompertz Pearl-Reed Perhaps this Table Can Come In Handy Forecasts Linear Exponential S Curve Polynomial Logarithmic Aug. 19, 2018 Sept. 6, 2018 R Square f) I believe that, of the eight possible models above, the most accurate forecast comes from the following model(s): Because: Weibull he following model(s): Gompertz Pearl-Reed Date 1/31/2012 2/29/2012 3/31/2012 4/30/2012 5/31/2012 6/30/2012 7/31/2012 8/31/2012 9/30/2012 10/31/2012 11/30/2012 12/31/2012 1/31/2013 2/28/2013 3/31/2013 4/30/2013 5/31/2013 6/30/2013 7/31/2013 8/31/2013 9/30/2013 10/31/2013 11/30/2013 12/31/2013 1/31/2014 2/28/2014 3/31/2014 4/30/2014 5/31/2014 6/30/2014 7/31/2014 8/31/2014 9/30/2014 10/31/2014 11/30/2014 12/31/2014 1/31/2015 2/28/2015 3/31/2015 4/30/2015 5/31/2015 6/30/2015 7/31/2015 8/31/2015 9/30/2015 10/31/2015 Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 Salary 19,714 20,381 21,298 19,108 18,882 30,034 26,279 28,162 30,902 33,550 33,460 20,997 29,173 25,848 31,253 24,533 22,056 31,781 27,880 30,058 34,085 38,571 36,828 25,435 31,351 28,437 31,701 25,822 25,527 36,920 35,662 35,337 40,029 43,678 41,080 29,497 36,278 33,519 35,153 30,729 29,157 42,606 37,835 40,343 43,420 46,971 11/30/2015 12/31/2015 1/31/2016 2/29/2016 3/31/2016 4/30/2016 5/31/2016 6/30/2016 7/31/2016 8/31/2016 9/30/2016 10/31/2016 11/30/2016 12/31/2016 1/31/2017 2/28/2017 3/31/2017 4/30/2017 5/31/2017 6/30/2017 7/31/2017 8/31/2017 9/30/2017 10/31/2017 11/30/2017 12/31/2017 1/31/2018 2/28/2018 3/31/2018 4/30/2018 5/31/2018 6/30/2018 7/31/2018 8/31/2018 9/30/2018 10/31/2018 11/30/2018 12/31/2018 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 43,061 32,885 35,941 34,694 35,518 31,625 29,369 42,022 39,193 40,384 43,199 45,565 42,484 32,325 37,586 36,239 36,652 33,150 30,002 43,261 40,883 42,731 45,554 48,320 47,149 36,720 Part b Part c Part d Jun. 30, 2018 Dec. 31, 2018 Root MSE Part (f): I say the data: Because: Explain your reasoning here: I say the best forecasts will be the results I found in part Because: yes, data is seasonal Part e no, data is not seasonal (put an X in either cell C9 or E9) Tell why you chose the answer you put in cell A20 by writing your explanation in cell B21. Hint: Consider the root mean square error which is very similar to the sum of the residuals squared for all the data. Time Actual Fitted Residual 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 40.80 85.01 116.10 125.11 133.09 139.86 146.63 163.91 190.80 209.34 215.39 221.40 226.12 231.24 241.06 255.51 265.43 268.19 270.88 273.25 57.17957 78.133161 97.562585 115.57871 132.28434 147.7748 162.13848 175.45735 187.8074 199.25911 209.87781 219.72412 228.8542 237.32015 245.1703 252.44942 259.19906 265.45772 271.26114 276.64241 -16.37461 6.8808743 18.538438 9.5307432 0.8012212 -7.916441 -15.51313 -11.55066 2.9962295 10.080326 5.5157285 1.6794989 -2.733661 -6.076485 -4.110995 3.0561156 6.2345817 2.7362513 -0.384246 -3.389396 b0 b1 345.240543 34.5822474 Solution: root(MSE) = 8.7654632 Yˆt =b0 − 0.9834929 Pseduo R-Square 300.00 250.00 200.00 150.00 100.00 50.00 0.00 0 5 10 b2 b3 0.2748125 0.2748106 −(b2t )b3 Yˆt =b0 −(b0 −b1)e 15 20 Actual 25 Fitted 30 35 40 Actual 40.804962 85.014035 116.10102 125.10945 133.08556 139.85836 146.62536 163.90669 190.80363 209.33943 215.39354 221.40361 226.12054 231.24367 241.0593 255.50553 265.43364 268.19398 270.87689 273.25301 Actual 40.804962 85.014035 116.10102 125.10945 133.08556 139.85836 146.62536 163.90669 190.80363 209.33943 215.39354 221.40361 226.12054 231.24367 241.0593 255.50553 265.43364 268.19398 270.87689 273.25301 Actual - Mean -150.1516483 -105.9425753 -74.8555873 -65.8471573 -57.8710493 -51.0982513 -44.3312543 -27.0499193 -0.1529813 18.3828217 24.4369317 30.4470037 35.1639247 40.2870577 50.1026897 64.5489227 74.4770267 77.2373647 79.9202797 82.2964007 Time 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Actual 40.80 85.01 116.10 125.11 133.09 139.86 146.63 163.91 190.80 209.34 215.39 221.40 226.12 231.24 241.06 255.51 265.43 268.19 270.88 273.25 Fitted 65.77827 80.82765 96.58755 112.6696 128.7168 144.422 159.5368 173.8741 187.3045 199.7496 211.174 221.5768 230.983 239.4369 246.9949 253.7216 259.6848 264.9535 269.5947 273.673 Residual -24.9733 4.186389 19.51348 12.43985 4.36874 -4.56359 -12.9114 -9.96743 3.499136 9.589861 4.219545 -0.17315 -4.86251 -8.19319 -5.93562 1.783962 5.748799 3.24051 1.282147 -0.42001 Solution: b0 b1 301.2071 0.1455014 root(MSE) = 9.6771169 Pseduo R-Square 0.9798806 Yˆt = 300.00 250.00 200.00 150.00 100.00 50.00 0.00 0 5 10 15 b2 3.884524 −e ˆ Yt =b0e −b1(t−b2 ) 15 20 Actual 25 Fitted 30 35 40 Actual 40.80496 85.01404 116.101 125.1095 133.0856 139.8584 146.6254 163.9067 190.8036 209.3394 215.3935 221.4036 226.1205 231.2437 241.0593 255.5055 265.4336 268.194 270.8769 273.253 Actual Actual - Mean 40.80496 -150.1516483 85.01404 -105.9425753 116.101 -74.8555873 125.1095 -65.8471573 133.0856 -57.8710493 139.8584 -51.0982513 146.6254 -44.3312543 163.9067 -27.0499193 190.8036 -0.1529813 209.3394 18.3828217 215.3935 24.4369317 221.4036 30.4470037 226.1205 35.1639247 231.2437 40.2870577 241.0593 50.1026897 255.5055 64.5489227 265.4336 74.4770267 268.194 77.2373647 270.8769 79.9202797 273.253 82.2964007 Time 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Actual 40.80 85.01 116.10 125.11 133.09 139.86 146.63 163.91 190.80 209.34 215.39 221.40 226.12 231.24 241.06 255.51 265.43 268.19 270.88 273.25 Fitted 71.60675 83.80171 97.12546 111.3911 126.3383 141.6488 156.9718 171.9552 186.2777 199.6743 211.9525 222.998 232.7689 241.2848 248.6113 254.8444 260.0972 264.4885 268.1349 271.1461 Residual -30.8018 1.212323 18.97556 13.71836 6.747259 -1.79044 -10.3464 -8.04855 4.525897 9.665169 3.441015 -1.59435 -6.64836 -10.0412 -7.552 0.661088 5.336414 3.705508 2.741957 2.106904 b0 Solution: b1 b2 284.37192 3.6887531 0.216289 root(MSE) = 10.523123 Pseduo R-Square Yˆt = 0.9762091 300 250 200 150 100 50 0 0 5 10 15 Actual Yˆt = b0 − b2t 1 + b1 e 20 Actual 25 Fitted 30 35 40 Pear 40.80496 85.01404 116.101 125.1095 133.0856 139.8584 146.6254 163.9067 190.8036 209.3394 215.3935 221.4036 226.1205 231.2437 241.0593 255.5055 265.4336 268.194 270.8769 273.253 Actual - Mean -150.1516483 -105.9425753 -74.8555873 -65.8471573 -57.8710493 -51.0982513 -44.3312543 -27.0499193 -0.1529813 18.3828217 24.4369317 30.4470037 35.1639247 40.2870577 50.1026897 64.5489227 74.4770267 77.2373647 79.9202797 82.2964007 Instructions for Part f: Go to your worksheet Problem 2 Solutions. If you think the method you used in parte is the best, then type an X in the cell C9 indicating, “yes the data is seasonal” and carefully explain why yo think so in cell B11. However, if you think any other of the procedures (from part b, c, or d) is best, type an X in t cell E9 indicating "No, the data is not seasonal." Finally, in cell A26, write which procedure (b, c, d, or e) you recommend and then, in cell B23, explain why you picked it and recommended it. Explain your reasoning and include any relevant numbers from your computer output to support your claim in cell B23. If Graph Looks Like Figure 1 Part: (a) or (b) Power Curve We Have the Functional Relationship Transform (ti, yi) into ů = both (In ti, In ģi) (c) or (d) Exponential Carre ů = behr (ti, In ĝi) (e) or (f) Logarithmic Curve (g) or (h) Reciprocal Cur y = b + b (Int) (In ti, ġi) ỹ = bo+byt 1 À (i) S-Curve 1 (bo+1) ŷ = e In û Ñ V = 7 M un nolaislamalar oli PACE UNIVERSITY LUBIN GRADUATE SCHOOL OF BUSINESS rowser obno Dr. Yurkiewicz Oct. 18, 2018 MBA647 Midterm Exam Instructions: Get the Data for the problems in the Excel workbook called MBA647 Midterm Fall 2018 (CRN 71093). Immediately, put your name in the specific cell E2 on the first worksheet called “Problem 1 Data.” As soon as you do this, save the workbook, calling it your last name, first name. Thus, if I were saving the test, I would save it as Yurkiewicz, Jack. You are working on the only this workbook. It is a closed-note exam, which means you cannot look at your homework problems, notes, internet, email, etc. Each question will be answered on a specified worksheet; the questions tell you exactly where. All the worksheets will be combined into the ONE workbook. You will save this workbook and when you are done, you will then e-mail it to me, as an attachment, using Blackboard. Remember to save your work OFTEN, like every five minutes! Ow 17** ME . MAMMA MIA! INSTRUCTIONS FOR WHAT TO PUT INTO THE TEST: Answer all the questions in the worksheets called Problem 1 Solutions, Problem 2 Solutions. For Problem 1, I have started you off with a sheet called Problem 1 Worksheet. You will do the analysis and show your work for Problem 1 onto this sheet. For example, suppose you did a regression analysis with StatTools or Excel. Leave the Excel or StatTools output in the worksheet that Excel or StatTools makes, but then use that output to copy the pertinent values into the Worksheet for Problem 1 and answer the questions for problem 1 in Problem 1 Solutions. Leave the original Excel or StatTools output in their own worksheets. The Gompertz, Weibull, and Pearl-Reed curve templates are included in the exam workbook. ***** COME FRA **** ***** 1. Momma Mia! Here We Go Again is a 2018 romantic comedy film written and ut in directed by Ol Parker, from a story by Parker, Catherine Johnson, and Richard Curtis. It is a follow-up to the 2008 film Mamma Mia, which in turn was based on the musical of the same name using the music of ABBA. The film is both a prequel and a HERE WE GO AGAIN sequel to the original movie, and it features flashbacks, telling the story of Donna Sheridan's arrival on the island of Kalokairi and her first meetings with her daughter Sophie's three possible fathers. Made on a $75 million budget and receiving good reviews from critics (80% Rotten Tomatoes) and movie patrons, the film was released in theaters on July 27, 2018 and has been a hit at the boxoffice in its first seventeen days of its release. The film is still playing in movie theaters as of today. The worksheet called Problem 1 Data gives the total cumulative boxoffice gross of the movie, in millions of dollars (these are figures just for the United States; worldwide values are higher). On the morning of August 13th, seventeen days after the film's release, the studio boss from Universal has asked you to make a forecast for how much Momma Mia will have grossed (domestic gross) by the end of these two specific dates: August 19th (day 24) and Sept. 6th (day 42), 2018. The data for the film's first seventeen days is on worksheet Show your work in the Problem 1 Worksheet but put all your final answers to the questions (b) through (j) on the next sheet Problem 1 Solutions. (5 points for parts a-i; 10 points for partj) a) Make a time plot of the data and put the graph into the sheet Problem 1 Time Plot. b) Assuming linear boxoffice growth as a function of time, what are your forecasts for the total domestic boxoffice gross of the film at the conclusion of August 19th and of September. 6th? c) Assuming exponential boxoffice growth as a function of time, what are your forecasts for total domestic boxoffice gross of the films at the conclusion of those dates, Aug. 19th and Sept. 6th? d) Assuming a S-Curve boxoffice growth model, what are your forecasts for Aug. 19th and Sept. 6th? e) Assuming a third-order polynomial boxoffice growth model, what are your forecasts for Aug. 19th and Sept. 6th? f) After looking at your Momma Mia time plot, you think that a possible model could be the logarithmic model. Using that model, what are your forecasts for Aug. 19th and Sept. 6th? 1 Holo (Note: The Excel templates for Parts g through J are in the workbook.) g) Feeling even more secure, you think that a Weibull growth model (which you believe the studio boss has never used) would give, perhaps an even better forecast. Using that model, what are your forecasts for Aug. 19th and Sept. 6th? Make the graph of the Weibull curve going to the second particular day of interest Sept. 6th. h) Assuming a Gompertz growth model, what are your forecasts for Aug. 19th and Sept. 6th? Make the graph of the Gompertz curve go to Sept. 6th. e make i) Finally, assuming the Pearl-Reed growth model, what are your forecasts for Aug. 19th and Sept. 6th? Make the graph for the Pearl-Reed curve go to Sept. 6: pleo de comando malevour bo orica od 190 noten j) If you had to choose just one model of the above eight models to make your boxoffice forecast, which one 0004 mm would you use? Explain your justification, using some statistics, for picking that particular model. mehow 90 B2,id om boy ancorbidw.musxaston-borolo il lood how zid root no gnilo 910 UO Y el sowohnuY 2) The Hornby Construction Company has a core workforce on its payroll. However, it does hire or "put on leave" ol professional independent construction workers if it sees that its business demands rise or fall. For example, if show there is a storm, Hornby would temporarily hire additional workers to meet the extra demand to clean up, built, or repair homes that have been damaged. If business is slow, Hornsby would temporarily put some workers "onomsl leave" where they just get benefits but no salary. Data for six years of monthly salaries of the hired workforce are tabulated at the end of each month. The numbers, in dollars, are in the worksheet Problem 2. The data starts with the monthly salaries of Jan. 31st, 2012 and ends with the monthly salaries as of Dec. 31st, 2017. The president of 9 the firm, Hornsby Reilly, wants to make forecasts for the future. He thinks he knows something about forecasting (after all, he did get a B-in Dr. Yurkiewicz' MBA647 course a few years back!) and has made some suggestions to bib you about what approach to take. Assume that salaries for a month are determined on the last day of that month. For example, the salary value for 6/30/2013, which is 31,781, gives the company's total salaries paid to its workers for the month June 2013. Assume today is Dec. 31, 2017, and, the problem is: you have been asked to help ow awo Hornsby forecast the salaries for some specific dates in 2018. a) (8 points) Make a time plot of the salaries and put it in the worksheet Problem 2 Time Plot. MoM non lolonia16.1915 yd vos mol.19 10 vd bootib Following Hornsby's guidance and instructions make the forecasts and put them in the worksheet called Problem 2 Solutions so that Hornsby can easily see, compare, and contrast them. In that worksheet, you can see the specifica dates of your forecasts in the A column, and put the forecasts for these dates, based on the technique you used in parts b-e below, in the appropriate cells. You may also include in your workbook (for partial credit) any additional worksheets (from StatTools or Excel) showing the models, output, and calculations. b) (8 points) Suppose Hornsby believed salaries were essentially "flat” or stable for the last six years, and you convince him that recent data is more important than data from several years ago, help him make a forecast for the salaries for June 30, 2018 and Dec. 31, 2018. (Don't argue with him about salaries being flat or not; remember that he is the president!) c) (8 points) After some discussion, you convinced Hornsby that there is some trend to the data, but he believes that the salaries for the all six years should count equally toward making the forecasts. Again, following his belief, make forecasts for the salaries for June 30, 2018 and Dec. 31, 2018. d) (8 points) After some explaining on your part, Hornsby agrees with your suggestion that indeed there is some trend to the salaries, but he says he is sure that there are no seasonal effects. He also agrees with your recommendation that recent data is more important than data from several years ago, and thus recent data should count more than distant data. Remembering that the president is always correct in his assessments, once again make forecasts for the profits for June 30, 2018 and Dec. 31, 2018. e) (8 points) After a close examination of the time plot, and some more "gentle explaining on your part, Hornsby agrees with you and now thinks that there may be a seasonality pattern in the data. Again, help him make forecasts for the salaries for Jun. 30, 2018 and Dec. 31, 2018. f) (10 points) Forgetting Hornsby and his C+ grade, which forecast method, of all the ones you tried above, do you believe will give the most accurate or best forecasts? See the instructions for Part f on the next page. 2.