Instructions attached below
Problem Read "Case 3: Forecasting Offeratory Revenue at St. Elizabeth Seton Catholic Church," from the textbook. Forecasting Part 1 Based on the information in the case and the data in the "Service Analytics Case Study Data" file, use Microsoft Excel to recommend the best forecasting model for monthly offertory revenue. Address the following as a part of your analysis. 1. Create a new column called "Period" and number each period starting from 1 at 2001M07. 2. Create a line chart of the dataset. 3. Using Excel, run descriptive analysis for the first 12 months, the next 12 months, etc., on the dataset. Start at 2001M07. Ignore periods 2005M07, 2005M08, and 2005M09. 4. Determine the 12 period moving average forecasts and the simple exponential smoothing forecasts (α = 0.05) based on the data. The mathematical formulas for these forecasting techniques are as follows. N period Moving Average Forecast (for next period) = (Sum of Actuals for N number of previous periods) / N. Note: for this assignment, assume N=12. Simple Exponential Smoothing Forecast (for next period) = Previous Period Forecast + (α * (Previous Period Actual - Previous Period Forecast)). Note: for this assignment, assume α = 0.05. For the Period 1, assume that the Forecast is the same as the Actual.value. Mean Absolute %Error = Average of: ((|Actual - Forecast|) / Actual )) for all periods where Actuals and Forecasts exist. 5. Create a line chart for each forecast, and ensure that each line chart also contains the historical dataset. 6. Compute the mean absolute percentage error for each forecast, based on the available and computed data. 7. Based on the line charts and the mean absolute percentage error calculations, indicate which forecasting technique should be used. Forecasting Part 2 You have shown the "Forecasting Part 1" results to the church leadership. Upon reviewing the data behavior, one of the parish leaders, who has a bachelor's degree in mathematics, recommends one additional forecasting approach. He suggests that you take the average of each month in a prior year to create a seasonal average forecast. For example, for a given July forecast, all of the prior July months would be averaged. 1. Compute additional forecasts based on this approach. Create a line chart for each forecast, and ensure that each line chart also contains the historical dataset. Compute the mean absolute percentage error for each forecast, based on the available and computed data. 2. Based on the line charts and the mean absolute percentage error calculations, determine whether the seasonal average forecasting technique should be used instead of the previously computed 12 period moving average and simple exponential smoothing forecasts. Explain why. 3. Select an optimal forecasting technique and justify your selection accordingly. 4. Using your selected optimal forecasting technique, perform a forecast prediction for the next 12 months. PowerPoint Presentation Create a 15 slide PowerPoint presentation that summarizes the setup and results of your analysis. The analysis setup portion of the presentation should include the following: 1. The line charts and associated interpretation. 2. Requested descriptive analyses and associated interpretation. 3. The Microsoft Excel results of all the forecasts run and associated interpretation of key results. 4. Any other analyses, as you see fit, in order to gain greater insights into the results. Include the results of each analysis in your presentation. The use of graphs, charts, and supporting data is required. Interpret the results of each analysis and draw general conclusions from the results. Make recommendations for the organization and address the organizational challenges that may be encountered based upon your recommendations. Organize the PowerPoint presentation in the following way (incorporate the content-specific information described above accordingly): 1. Introduction and case background. 2. Objectives for each analysis. 3. Approach or method of analysis and justification for selecting the approach or method. 4. Results of each analysis. 5. Supporting graphs, charts, data, and spreadsheets for each analysis (minimum = 4, maximum = 8). 6. Interpretation of the results for each analysis. 7. The results of the descriptive analysis summarizing the data behavior for each year using basic statistical descriptive analysis. 8. The optimal forecasting technique, supporting rationale for selecting it, and insights into your recommended predictions for the next 12 months. 9. For your selected optimal forecasting technique, prescribe a specific course of action for the church. Address whether the predictions be for the next 12 months should be used and why. Identify other challenges the church may face if the prescribed course of action is taken. 10. Description of additional services analytics approaches that might useful for the church. In the "Notes" section of each slide, include your talking points. This information should align to the results of your analyses and be supported in the accompanying Excel file.