Forecasting Case Study: Urban Planning Important Note: Students must access the \"County Business Patterns\" Topic Material for this assignment. Students will use this U.S. Census Industry data portal to access data for a zip code with which you are familiar. This can be the zip code of your personal residence, location of employer (corporate, regional, or local office), undergraduate educational institution, hometown, etc. Scenario You have recently been hired as an urban planner for your local government. You have been tasked with determining economic growth and decline patterns in your area. As an urban planner, you will ultimately be responsible for determining patterns and forecasting for all industries within your area. However, the city manager has asked that you prioritize one of the 10 industries below. Business proposals have been submitted and the city manager would like to have use forecasting data to make an informed decision about whether to approve industry expansion or to allocate resources elsewhere. Industries Construction Manufacturing Transportation and Warehousing Information Finance and Insurance Real Estate and Rental and Leasing Professional, Scientific, and Technical Services Management of Companies and Enterprises Administrative and Support and Waste Management and Remediation Services Health Care and Social Assistance Forecasting Access the \"County Business Patterns\" page on the United States Census Bureau website and enter your city's zip code. Using one of the industries above, access the data for the last 5 years that are available on website. Determine patterns of economic growth or decline during this time period, and develop the most optimal forecasting model for the next 2 years. Note that you will need to set up these two forecast calculations. Clearly justify why your selected model is the best one. Specifically explain what forecast error is and how you used it to ascertain the most optimal forecasting model. Assume that you are presenting your findings to senior management and that senior management has minimal knowledge of forecasting techniques and how forecast error is calculated. Forecasting Moving averages - 4 period moving average Enter Enter the the past past demands demands in in the the data data area area Num pds Data Period Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Next period 4 Demand Forecasts and Error Analysis Forecast Error Absolute #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Total Average #DIV/0! Squared Abs Pct Err #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Bias MAD MSE MAPE 12 SE #DIV/0! Not enough data to compute the standard error 10 8 Value 6 4 2 0 1 2 3 D Forecasting 12 pute the standard error 10 8 Value 6 4 2 0 1 2 3 4 5 6 7 Time Demand Forecast 8 9 10 11 Forecasting Weighted moving averages - 2 period moving average Enter Enter the the data data in in the the shaded shaded area. area. Enter Enter weights weights in in INCREASING INCREASING order order from from top top to to bottom. bottom. Data Period Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Demand Weights 0.15 0.3 Forecasts and Error Analysis Forecast Error Absolute 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total Average Bias Next period 0 Squared 0 0 0 0 0 0 0 0 0 0 0 MAD SE Abs Pct Err 0 0 0 0 0 0 0 0 0 0 0 MSE 0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! MAPE Forecasting 1 0.9 0.8 0.7 0.6 Value 0.5 0.4 0.3 0.2 0.1 0 1 2 3 4 5 6 7 Time Demand Forecast 8 9 10 11 Forecasting Exponential smoothing Enter Enter alpha alpha (between (between 00 and and 1), 1), enter enter the the past past demands demands in in the the shaded shaded column column then then enter enter aa starting starting forecast. forecast. IfIf the the starting starting forecast forecast isis not not in in the the first first period period then then delete delete the the error error analysis analysis for for all all rows rows above above the the starting starting forecast. forecast. Alpha Data Period Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 0.05 Forecasts and Error Analysis Forecast Error Absolute 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total 0 Average 0 Bias MAD Demand Squared 0 0 0 0 0 0 0 0 0 0 0 0 0 MSE SE Next period 0 0 0 0 0 0 0 0 0 0 0 0 0 Abs Pct Err #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! MAPE 1 0 0.9 0 0.8 0.7 0.6 Value 0.5 0.4 0.3 0.2 0.1 0 1 2 3 Forecasting 1 0.9 0.8 0.7 0.6 Value 0.5 0.4 0.3 0.2 0.1 0 1 2 3 4 5 6 7 Time Column B 0 8 9 10 Dine In (1)/Take Out (2) Satisfaction with Service Satisfaction with Food 1 4 1 2 1 3 1 5 2 3 2 2 2 3 1 4 2 3 1 2 2 1 2 2 1 5 1 4 1 4 1 3 1 4 2 3 2 3 1 4 2 4 1 2 2 3 2 3 1 3 2 4 2 3 1 4 2 3 1 4 2 2 2 2 1 4 2 3 2 3 1 3 1 3 1 4 2 3 2 3 1 4 2 3 2 2 1 3 2 4 1 2 2 4 Overall Satisfaction 4 3 3 5 4 4 4 3 3 3 3 2 4 5 5 4 3 4 4 5 5 3 5 4 4 5 3 4 4 5 3 2 4 2 3 3 3 5 3 4 4 3 3 3 4 3 5 4 3 3 5 3 3 3 3 3 2 2 2 4 4 4 3 4 3 3 4 4 3 4 3 3 4 3 4 4 4 2 2 4 3 3 3 3 4 3 3 4 3 2 3 4 2 4 1 1 4 5 5 5 4 5 Driving Distance to Restaurant Total Bill 5 10 5 15 10 10 12 15 10 25 15 25 10 26 16 27 2 25 10 26 15 20 10 20 12 20 16 20 18 20 20 27 18 28 20 28 16 28 7 12 9 20 10 24 6 26 10 28 9 27 8 24 10 22 6 23 10 25 10 20 15 20 16 20 18 20 16 20 14 25 20 22 16 23 17 28 16 23 5 15 10 28 6 24 10 27 6 26 7 28 6 24 8 22 6 8 23 20 \f