could someone please solve this with excel all set up wirh the equations. i have not been able to find an example enough for me to solve help me solve it on my own! Thbak you!

Chapter 4 Case - Forecasting Background: Southwestern University Case B Southwestern University (SWU), a large state college in Stephenville, Texas, 30 miles southwest of the Dallas/Fort Worth metroplex, enrolls close to 20,000 students. In a typical town-gown relationship, the school is a dominant force in the small city, with more students during fall and spring than permanent residents Always a football powerhouse, SWU is usually in the top 20 in college football rankings. Since the legendary Phil Flamm was hired as its head coach in 2009 (in hopes of reaching the clusive number 1 ranking), attendance at the five Saturday home games each year increased. Prior to Flamm's arrival, attendance generally averaged 25,000 to 29,000 per game. Season ticket sales bumped up by 10,000 just with the announcement of the new coach's arrival. Stephenville and SWU were ready to move to the big time! The immediate issue facing SWU, however, was not NCAA ranking. It was capacity. The existing SWU stadium, built in 1953, has seating for 54,000 fans. The following table indicates attendance at each game for the past 6 years. One of Flamm's demands upon joining SWU had been a Stadium expansion, or possibly even a new stadium. With attendance increasing, SWU administrators began to face the issue head-on. Flamm had wanted dormitories solely for his athletes in the stadium as an additional feature of any expansion SWU's president, Dr. Joel Wiszer. decided it was time for his vice president of development to forecast when the existing stadium would "max" out." The expansion was, in his mind, a given. But Wisner needed to know how long he could wait. He also sought a revenue projection, assuming an average ticket price of $50 in 2016 and a 5% increase cach year in future prices 200 3.2 Game Andres Oppones De Anders 1142300 100 M 35. SC 10.00 10,300 Nutada 6.00 mts 38300k 39,100 To State 43,00 Alma 26,900 Atas 25.300 Nevala 36.300 Bie Sud 39.200 1 4 3 15.100 TCU 2013 2014 Game Allende Open Air Open Artender 41.900 rame 2.500 46900 BOOM 3.200 North Town 0:00 41.200 Larte 300 TARM 30,100 30cm 16.100 4000LSU 47.800 NASE Solution Requirements Utilize Microsoft Excel to complete the following steps. Submit the assignment in Excel for credit. No credit will be given for work submitted in another file format. Pay attention to formatting and clearly label all steps: 1. Create a linear trend forecast for the next ten games. Use these trend lines to calculate the annual forecasts for year 7 and 8 Hint: You WAVE to creates are equation for athletes in the stadium as an additional feature of any expansion SWU's president, Dr. Joel Wisner, decided it was time for his vice president of development to forecast when the existing stadium would "max" out. The expansion was, in his mind, a given. But Wisner needed to know how long he could wait. He also sought a revenue projection, assuming an average ticket price of $50 in 2016 and a 5% increase each year in future prices. 2010 2011 Game Alder Opp Andres Open Attendees op 1 38,100 M25.00 ISC 19.00 Tess 48.200 do 16.500 Texas Tee 3.200 The 72,100 This Sone 3.100 kila 124900 33.00 Nevada 27.000 3,000 TOU 5 2011 2014 2015 Game Attende Opper Opport And Opposem 41000 km SU 46,100 Miss 48.200 ha 100 e 43.900 42-300 TE MAM 8.00 Seu 1 30,300 R 800 Sehem 16100 Mane +0.5000 $7.500 A Solution Requirements Utilize Microsoft Excel to complete the following steps. Submit the assignment in Excel for credit. No credit will be given for work submitted in another file format. Pay attention to formatting and clearly label all steps: 1. Create a linear trend forecast for the next ten games. Use these trend lines to calculate the annual forecasts for year 7 and 8 Hint: You will have to create a linear trend equation for cach game of the season across all six years. In other words, use all six years of info for Game I to create a Game 1 trend equation. Then repeat for Game 2 through Game 5. Once you have the equation for each of the five games, a forecast can be made for the next two years by forecasting out Games through 5 for both year 7 and year 8. Consider starting with the Excel table shown in the slides: For example: Gall Vers XY 2. Compute seasonal indices for each game. Use this to adjust the two annual forecasts generated in the previous step and to reallocate the next ten game forecasts. Hint: Use games as "seasons," with the six years of data for each season 3. Calculate the MAD, MSE, and MAPE for both the lincar trend forecasts from step 1 and the seasonal forecasts from step 2. Determine which forecast is more accurate and should be used. Assume the actual demand for the next ten games is: Actual Actual 2016 2017 SIDON 35.100 36.220 Bed39.2003 2015 2013 2014 Game Mendes Andersen 41.500 a 2.500 4200 M200 North 50.000 TL 1 43,00 Tia 48.300 las A&M 38.00 h 0,100 m 16.00 40.500 SU 47.5000 Solution Requirements Utilize Microsoft Excel to complete the following steps. Submit the assignment in Excel for credit. No credit will be given for work submitted in another file format. Pay attention to formatting and clearly label all steps: 1. Create a linear trend forecast for the next ten games. Use these trend lines to calculate the annual forecasts for year 7 and 8. Hint: You will have to create a linear trend equation for each game of the season across all six years. In other words, use all six years of info for Game 1 to create a Game I trend equation. Then repeat for Game 2 through Game 5. Once you have the equation for each of the five games, a forecast can be made for the next two years by forecasting out Games through 5 for both year 7 and year 8. Consider starting with the Excel table shown in the slides: For example: YearArte NXY 3 1 6 2. Compute seasonal indices for each game. Use this to adjust the two annual forecasts generated in the previous step and to reallocate the next ten game forecasts. Hint: Use games as "seasons." with the six years of data for each season. 3. Calculate the MAD, MSE, and MAPE for both the lincar trend forecasts from step 1 and the seasonal forecasts from step 2. Determine which forecast is more accurate and should be used. Assume the actual demand for the next ten games is: Asal Actual 2016 2017 KRS 0000 Kia 500.00 500.00 h 18.500.00 $2,800.00 KOOK100,00 Kangoo 100.00 9368024330008 4. Use the forecast and information in the case on ticket prices to project revenue for the next two years. 7 D Year) Gant Atend X2 XY 34200 1 34200 2 36100 72200 3 35900 9 107700 4 41900 16 167600 5 42500 25 292500 6 4900 30 281400 237500 91 875600 35 3958333333 15.16666667 145933.3333 11 YEN 1 Game 2 Attendees XY 39800 35800 2 40200 4 80400 3 46500 9 139500 4 46100 16 184400 GR200 25 241000 6 50100 36 300600 21 270000 91 985700 3.5 45150 15.16666667 154283 3393 20 YERI Games Attendees X2 XY 38200 1 39100 4 3 43100 9 4 43900 18 5 44200 25 6 45900 36 21 254400 91 3.5 42400 15.16666667 36200 78200 128300 175600 221000 275400 917700 152950 Vax Game 4 Anendes X2 XY 26900 1 26900 2 25300 4 50000 27900 9 3700 30100 16 120400 5 33100 25 16500 6 36300 36 217800 21 180400 91 668900 35 30066.66667 15.16666667 11143.3333 Year Games Attendeestyle ez XY 35100 36200 3 38200 9 40500 16 5 47800 25 49900 36 21 248700 01 3.5 41450 15.16666667 35100 72400 117800 162000 239000 296400 925500 1542501 Chapter 4 Case - Forecasting Background: Southwestern University Case B Southwestern University (SWU), a large state college in Stephenville, Texas, 30 miles southwest of the Dallas/Fort Worth metroplex, enrolls close to 20,000 students. In a typical town-gown relationship, the school is a dominant force in the small city, with more students during fall and spring than permanent residents Always a football powerhouse, SWU is usually in the top 20 in college football rankings. Since the legendary Phil Flamm was hired as its head coach in 2009 (in hopes of reaching the clusive number 1 ranking), attendance at the five Saturday home games each year increased. Prior to Flamm's arrival, attendance generally averaged 25,000 to 29,000 per game. Season ticket sales bumped up by 10,000 just with the announcement of the new coach's arrival. Stephenville and SWU were ready to move to the big time! The immediate issue facing SWU, however, was not NCAA ranking. It was capacity. The existing SWU stadium, built in 1953, has seating for 54,000 fans. The following table indicates attendance at each game for the past 6 years. One of Flamm's demands upon joining SWU had been a Stadium expansion, or possibly even a new stadium. With attendance increasing, SWU administrators began to face the issue head-on. Flamm had wanted dormitories solely for his athletes in the stadium as an additional feature of any expansion SWU's president, Dr. Joel Wiszer. decided it was time for his vice president of development to forecast when the existing stadium would "max" out." The expansion was, in his mind, a given. But Wisner needed to know how long he could wait. He also sought a revenue projection, assuming an average ticket price of $50 in 2016 and a 5% increase cach year in future prices 200 3.2 Game Andres Oppones De Anders 1142300 100 M 35. SC 10.00 10,300 Nutada 6.00 mts 38300k 39,100 To State 43,00 Alma 26,900 Atas 25.300 Nevala 36.300 Bie Sud 39.200 1 4 3 15.100 TCU 2013 2014 Game Allende Open Air Open Artender 41.900 rame 2.500 46900 BOOM 3.200 North Town 0:00 41.200 Larte 300 TARM 30,100 30cm 16.100 4000LSU 47.800 NASE Solution Requirements Utilize Microsoft Excel to complete the following steps. Submit the assignment in Excel for credit. No credit will be given for work submitted in another file format. Pay attention to formatting and clearly label all steps: 1. Create a linear trend forecast for the next ten games. Use these trend lines to calculate the annual forecasts for year 7 and 8 Hint: You WAVE to creates are equation for athletes in the stadium as an additional feature of any expansion SWU's president, Dr. Joel Wisner, decided it was time for his vice president of development to forecast when the existing stadium would "max" out. The expansion was, in his mind, a given. But Wisner needed to know how long he could wait. He also sought a revenue projection, assuming an average ticket price of $50 in 2016 and a 5% increase each year in future prices. 2010 2011 Game Alder Opp Andres Open Attendees op 1 38,100 M25.00 ISC 19.00 Tess 48.200 do 16.500 Texas Tee 3.200 The 72,100 This Sone 3.100 kila 124900 33.00 Nevada 27.000 3,000 TOU 5 2011 2014 2015 Game Attende Opper Opport And Opposem 41000 km SU 46,100 Miss 48.200 ha 100 e 43.900 42-300 TE MAM 8.00 Seu 1 30,300 R 800 Sehem 16100 Mane +0.5000 $7.500 A Solution Requirements Utilize Microsoft Excel to complete the following steps. Submit the assignment in Excel for credit. No credit will be given for work submitted in another file format. Pay attention to formatting and clearly label all steps: 1. Create a linear trend forecast for the next ten games. Use these trend lines to calculate the annual forecasts for year 7 and 8 Hint: You will have to create a linear trend equation for cach game of the season across all six years. In other words, use all six years of info for Game I to create a Game 1 trend equation. Then repeat for Game 2 through Game 5. Once you have the equation for each of the five games, a forecast can be made for the next two years by forecasting out Games through 5 for both year 7 and year 8. Consider starting with the Excel table shown in the slides: For example: Gall Vers XY 2. Compute seasonal indices for each game. Use this to adjust the two annual forecasts generated in the previous step and to reallocate the next ten game forecasts. Hint: Use games as "seasons," with the six years of data for each season 3. Calculate the MAD, MSE, and MAPE for both the lincar trend forecasts from step 1 and the seasonal forecasts from step 2. Determine which forecast is more accurate and should be used. Assume the actual demand for the next ten games is: Actual Actual 2016 2017 SIDON 35.100 36.220 Bed39.2003 2015 2013 2014 Game Mendes Andersen 41.500 a 2.500 4200 M200 North 50.000 TL 1 43,00 Tia 48.300 las A&M 38.00 h 0,100 m 16.00 40.500 SU 47.5000 Solution Requirements Utilize Microsoft Excel to complete the following steps. Submit the assignment in Excel for credit. No credit will be given for work submitted in another file format. Pay attention to formatting and clearly label all steps: 1. Create a linear trend forecast for the next ten games. Use these trend lines to calculate the annual forecasts for year 7 and 8. Hint: You will have to create a linear trend equation for each game of the season across all six years. In other words, use all six years of info for Game 1 to create a Game I trend equation. Then repeat for Game 2 through Game 5. Once you have the equation for each of the five games, a forecast can be made for the next two years by forecasting out Games through 5 for both year 7 and year 8. Consider starting with the Excel table shown in the slides: For example: YearArte NXY 3 1 6 2. Compute seasonal indices for each game. Use this to adjust the two annual forecasts generated in the previous step and to reallocate the next ten game forecasts. Hint: Use games as "seasons." with the six years of data for each season. 3. Calculate the MAD, MSE, and MAPE for both the lincar trend forecasts from step 1 and the seasonal forecasts from step 2. Determine which forecast is more accurate and should be used. Assume the actual demand for the next ten games is: Asal Actual 2016 2017 KRS 0000 Kia 500.00 500.00 h 18.500.00 $2,800.00 KOOK100,00 Kangoo 100.00 9368024330008 4. Use the forecast and information in the case on ticket prices to project revenue for the next two years. 7 D Year) Gant Atend X2 XY 34200 1 34200 2 36100 72200 3 35900 9 107700 4 41900 16 167600 5 42500 25 292500 6 4900 30 281400 237500 91 875600 35 3958333333 15.16666667 145933.3333 11 YEN 1 Game 2 Attendees XY 39800 35800 2 40200 4 80400 3 46500 9 139500 4 46100 16 184400 GR200 25 241000 6 50100 36 300600 21 270000 91 985700 3.5 45150 15.16666667 154283 3393 20 YERI Games Attendees X2 XY 38200 1 39100 4 3 43100 9 4 43900 18 5 44200 25 6 45900 36 21 254400 91 3.5 42400 15.16666667 36200 78200 128300 175600 221000 275400 917700 152950 Vax Game 4 Anendes X2 XY 26900 1 26900 2 25300 4 50000 27900 9 3700 30100 16 120400 5 33100 25 16500 6 36300 36 217800 21 180400 91 668900 35 30066.66667 15.16666667 11143.3333 Year Games Attendeestyle ez XY 35100 36200 3 38200 9 40500 16 5 47800 25 49900 36 21 248700 01 3.5 41450 15.16666667 35100 72400 117800 162000 239000 296400 925500 1542501