Case study: Bank Account Forecast A well-known commercial bank in Australia is interested in estimating the number of new bank account opened by customers in each year. The number of new accounts opened in this bank has increased slowly over years even during the global financial crisis (20082009). Top management strongly believes that they need a long-term strategic plan for the bank which is a 5-year forecast for the number of new accounts opened. To achieve this aim, the bank operations manager examined past account data and also extracted the employment rate over 30 years (1991-2020). The resulting data are shown in below table: No of new Year account(000) Employment No of new Rate (%) Year account(000) Employment Rate (%) 1991 3.136 90.42 2006 6.007 95.22 1992 3.309 89.27 2007 5.826 95.62 |1993 3.803 89.13 2008 8.266 95.77 1994 6.807 90.28 2009 7.128 94.44 1995 2.386 91.53 2010 9.670 94.79 1996 4.643 91.49 2011 11.419 94.92 1997 2.911 91.64 2012 11.335 94.78 1998 2.553 92.32 2013 9.358 94.34 1999 4.422 93.13 2014 10.418 93.92 2000 3.613 93.72 2015 10.459 93.95 2001 3.948 93.26 2016 7.328 94.29 2002 3.347 93.63 2017 9.004 94.41 2003 3.907 94.07 2018 8.559 94.70 2004 4.071 94.60 2019 8.476 94.84 2005 6.666 94.97 2020 8.635 93.39 a. Using the following forecast methods discuss which method fits best for the bank strategic plan. You need to justify the selection of one method over another. 1. Moving average (you need to find the best value for n) 2. Linear trend (trend projection) 3. Linear regression b. Can you exclude a portion of the data for the analysis? If yes, why? The bank operations manager also collected the data on Australia GDP Per Capita (Gross Domestic Product) and believes that GDP Per Capita can also affect the number of bank account. Below table shows the data for the same period: GDP Per Capita GDP Per Capita Year (000AS) Year (000AS) 1991 18.8218 2006 36.04492 1992 18.57012 2007 40.96005 1993 17.63453 2008 49.60166 1994 18.04614 2009 42.77236 1995 20.31963 2010 52.02213 1996 21.86133 2011 62.51783 1997 23.4686 2012 68.01215 1998 21.31896 2013 68.15011 1999 20.53304 2014 62.51079 2000 21.67925 2015 56.75572 2001 19.49086 2016 49.97113 2002 20.08248 2017 54.02797 2003 23.44703 2018 57.35496 2004 30.43068 2019 55.0572 2005 33.99924 2020 51.81215 c. Between GDP Per Capita and employment rate which one do you think can better estimate the number of new bank account? Marking criteria: Criterion Marks Correct forecasts for all possible periods using Moving average. Show the graph of actual and forecast. 3 Correct forecasts for all possible periods using Linear trend (trend projection). Show the graph of actual and forecast. 3 Correct forecasts for all possible periods using Linear regression. Show the graph of actual and forecast. 3 Find the method that fits best for the bank strategic plan. Explain why? Whether we can exclude a portion of the data for the analysis? If yes, why? 4 3 Explain which variable, GDP Per Capita or employment rate, can better estimate the number of new bank account and why? 4 Total 20 Important information for Assignment 1: Please submit just one file. Your submission must be in Microsoft Word. You can copy and paste screenshots from Excel into the Word document. Brief explanation is required for all calculations. Do not round the results up or down.