b) Develop a linear trend, multiplicative model for the data, including seasonality and forecast visits to the clinic for each month of 20X7. To develop a linear trend, multiplicative model, we need to determine the trend component and the seasonal indices. Here's how you can do it in Excel: Open a new Excel worksheet and enter the given data in columns A, B, and C. Column A will contain the months, column B the years (20X4, 20X5, 20X6), and column C the corresponding number of visits. In column D, calculate the average visits for each month across the three years using the AVERAGEIF function. Enter the formula =AVERAGEIF($A$2:$A$37,A2,$C$2:$C$37) in cell D2 and drag it down to fill the range D2:D13. In column E, calculate the grand average visits for each month using the AVERAGE function. Enter the formula =AVERAGE($D$2:$D$13) in cell E2 and drag it down to fill the range E2:E13. In column F, calculate the trend component by dividing the average visits for each month by the grand average visits. Enter the formula =D2/E2 in cell F2 and drag it down to fill the range F2:F13. In column G, calculate the seasonal index for each month by dividing the trend component by the average trend component. Enter the formula =F2/AVERAGE($F$2:$F$13) in cell G2 and drag it down to fill the range G2:G13. Now, to forecast visits for each month of 20X7, multiply the corresponding seasonal index by the trend component for that month. In column H, enter the formula =AVERAGEIF($A$2:$A$13,A2,$G$2:$G$13)*$F$2 in cell H2 and drag it down to fill the range H2:H13. These values represent the forecasted visits for each month of 20X7. (c) Use exponential smoothing with an of 0.3 and forecast each month of 20X7, assuming after each forecast that the value calculated in (a) above is the actual demand. For F, use December 20X6 sales. To perform exponential smoothing in Excel, follow these steps: In column J, enter the given data for 20X6 visits (December 20X6 sales) corresponding to each month. In column K, enter the initial forecast for January 20X7, which is the December 20X6 sales value. Copy the value from column J to column K for each month. In column L, calculate the forecasted value for January 20X7 using the exponential smoothing formula. Enter the formula =$K$2+(0.3*($J$13-$K$2)) in cell L3 and drag it down to fill the range L3:L13. The value from column K (December 20X6 sales) is subtracted from the actual value in column J (20X6 visits) and multiplied by the smoothing constant ( = 0.3). Then the result is added to the previous forecast in column K. Explanation: (b) Develop a linear trend, multiplicative model for the data, including seasonality and forecast visits to the clinic for each month of 20X7. Open a new Excel worksheet and enter the given data in columns A, B, and C. Column A will contain the months, column B the years (20X4, 20X5, 20X6), and column C the corresponding number of visits. In column D, calculate the average visits for each month across the three years using the AVERAGEIF function. Enter the formula =AVERAGEIF($A$2:$A$37,A2,$C$2:$C$37) in cell D2 and drag it down to fill the range D2:D13. In column E, calculate the grand average visits for each month using the AVERAGE function. Enter the formula =AVERAGE($D$2:$D$13) in cell E2 and drag it down to fill the range E2:E13. In column F, calculate the trend component by dividing the average visits for each month by the grand average visits. Enter the formula =D2/E2 in cell F2 and drag it down to fill the range F2:F13. In column G, calculate the seasonal index for each month by dividing the trend component by the average trend component. Enter the formula =F2/AVERAGE($F$2:$F$13) in cell G2 and drag it down to fill the range G2:G13. In cell H2, enter the formula =G2*F2 to calculate the forecasted visits for January 20X7. Drag this formula across the range H2:M2 to calculate the forecasted visits for each month of 20X7, based on the corresponding seasonal indices and trend components. Step 2/2 For the remaining months of 20X7, continue using the exponential smoothing formula. In cell L4, enter the formula =$L$3+(0.3*($J$14-$L$3)) and drag it down to fill the range L4:L13. This formula uses the previous forecast in column L and adjusts it based on the difference between the actual value in column J and the previous forecast. The values in column L represent the forecasted visits for each month of 20X7 using exponential smoothing. (d) Calculate MAD, MAPE, and bias resulting from the calculations in (b). What do they indicate? To calculate MAD (Mean Absolute Deviation), MAPE (Mean Absolute Percentage Error), and bias, follow these steps: In column M, calculate the absolute deviation between the forecasted values in column H (from part b) and the actual values in column C. Enter the formula =ABS(C2-H2) in cell M2 and drag it down to fill the range M2:M13. In cell M15, calculate the MAD using the formula =AVERAGE(M2:M13). The MAD represents the average absolute deviation between the forecasted values and the actual values. In column N, calculate the absolute percentage error for each month by dividing the absolute deviation in column M by the actual values in column C. Enter the formula =ABS(M2/C2) in cell N2 and drag it down to fill the range N2:N13. In cell N15, calculate the MAPE using the formula =AVERAGE(N2:N13). The MAPE represents the average percentage error between the forecasted values and the actual values. In cell M17, calculate the bias using the formula =AVERAGE(H2:H13-C2:C13). The bias represents the average difference between the forecasted values and the actual values. The resulting values for MAD, MAPE, and bias will provide you with insights into the accuracy and bias of the forecasts obtained using the linear trend, multiplicative model (part b). Explanation: (c) Use exponential smoothing with an of 0.3 and forecast each month of 20X7, assuming after each forecast that the value calculated in (a) above is the actual demand. For F, use December 20X6 sales. In column J, enter the given data for 20X6 visits (December 20X6 sales) corresponding to each month. In cell K2, enter the December 20X6 sales value as the initial forecast for January 20X7. In cell L2, enter the formula =K2+0.3*(J2-K2) to calculate the forecasted visits for January 20X7 using exponential smoothing. This formula adjusts the previous forecast (K2) based on the difference between the actual value (J2) and the previous forecast, multiplied by the smoothing constant ( = 0.3). Drag the formula from cell L2 to fill the range L2:M13 to forecast visits for each month of 20X7, assuming the value calculated in (b) is the actual demand after each forecast. (d) Calculate MAD, MAPE, and bias resulting from the calculations in (b). What do they indicate? In column N, calculate the absolute deviation (AD) by subtracting the actual visits in column C from the forecasted visits in column H. Enter the formula =ABS(C2-H2) in cell N2 and drag it down to fill the range N2:N13. Final answer (b) Develop a linear trend, multiplicative model for the data, including seasonality and forecast visits to the clinic for each month of 20X7. Open a new Excel worksheet and enter the given data in columns A, B, and C. Column A will contain the months, column B the years (20X4, 20X5, 20X6), and column C the corresponding number of visits. In column D, calculate the average visits for each month across the three years using the AVERAGEIF function. Enter the formula =AVERAGEIF($A$2:$A$37,A2,$C$2:$C$37) in cell D2 and drag it down to fill the range D2:D13. In column E, calculate the grand average visits for each month using the AVERAGE function. Enter the formula =AVERAGE($D$2:$D$13) in cell E2 and drag it down to fill the range E2:E13. In column F, calculate the trend component by dividing the average visits for each month by the grand average visits. Enter the formula =D2/E2 in cell F2 and drag it down to fill the range F2:F13. In column G, calculate the seasonal index for each month by dividing the trend component by the average trend component. Enter the formula =F2/AVERAGE($F$2:$F$13) in cell G2 and drag it down to fill the range G2:G13. In cell H2, enter the formula =G2*F2 to calculate the forecasted visits for January 20X7. Drag this formula across the range H2:M2 to calculate the forecasted visits for each month of 20X7, based on the corresponding seasonal indices and trend components. (c) Use exponential smoothing with an of 0.3 and forecast each month of 20X7, assuming after each forecast that the value calculated in (a) above is the actual demand. For F, use December 20X6 sales. In column J, enter the given data for 20X6 visits (December 20X6 sales) corresponding to each month. In cell K2, enter the December 20X6 sales value as the initial forecast for January 20X7. In cell L2, enter the formula =K2+0.3*(J2-K2) to calculate the forecasted visits for January 20X7 using exponential smoothing. This formula adjusts the previous forecast (K2) based on the difference between the actual value (J2) and the previous forecast, multiplied by the smoothing constant ( = 0.3). Drag the formula from cell L2 to fill the range L2:M13 to forecast visits for each month of 20X7, assuming the value calculated in (b) is the actual demand after each forecast. (d) Calculate MAD, MAPE, and bias resulting from the calculations in (b). What do they indicate? In column N, calculate the absolute deviation (AD) by subtracting the actual visits in column C from the forecasted visits in column H. Enter the formula =ABS(C2-H2) in cell N2 and drag it down to fill the range N2:N13. CAN SOMEONE SHOW ME HOW TO DO THIS IN EXCEL. I AM NOT UNDERSTANDING WHICH NUMBERS TO BE CONSIDERED IN EXPONENTIAL SMOOTHING. THE QUESTION IS THIS. Given the data below of visits to a walk-in medical clinic: 5. Month January February March April May June July August September October November December Total 20X4 12,000 8,000 18,000 13,000 14,000 18,000 17,000 20,000 25,000 28,000 18,000 18,000 209,000 20X5 15,000 12,000 14,000 18,000 15,000 18,000 20,000 22,000 26,000 28,000 20,000 22,000 230,000 20X6 15,000 22,000 18,000 18,000 16,000 20,000 28,000 28,000 20,000 30,000 22,000 28,000 265.000 (a) Fit a linear trend line to the data and plot both the data and the trend line. What does the plot tell you? (b) Develop a linear trend, multiplicative model for the data, including seasonality and forecast visits to the clinic for each month of 20X7. (c) Use exponential smoothing with an a of 0.3 and fore- cast each month of 20X7, assuming after each forecast that the value calculated in (a) above is the actual demand. For F, use December 20X6 sales. (d) Calculated MAD, MAPE, and bias resulting from the calculations in (b). What do they indicate?I have figured the first question. I need answers for b,c and d. I have looked up the answers but its not correct. can someone give me the right answers. with details in excel work also i need formulas how to use it in excel. Please show me the real excel worksheet. How to calculate the MAD, MAPE and bias in excel. please don't post just answers I need some explanation. with the formulas how to answer them. I am trying to learn this