the questions and material to answer the questions are attached here.... thank you!

Final Exam MBA604 Part A Period (month) Demand Northeast 1 2 3 4 5 6 7 8 9 10 11 12 13 1200 1300 1200 1400 1500 1450 1550 1700 1800 1630 1480 1480 Demand is given in Thousand cases. Marketing/sale Number of s budget contracted Northeast restaurants Northeast 25,000.00 28,000.00 30,000.00 30,000.00 30,000.00 35,000.00 35,000.00 38,000.00 35,000.00 35,000.00 35,000.00 35,000.00 35,000.00 Demand Northwest 80 85 93 95 95 95 95 95 95 95 95 95 95 800 850 820 900 880 920 900 950 1020 930 990 1000 Marketing/sa Number of Demand les budget contracted Southeast Northwest reastaurants Northeast 18,000.00 18,000.00 18,000.00 22,000.00 22,000.00 22,000.00 25,000.00 25,000.00 26,000.00 27,500.00 28,000.00 28,500.00 25,000.00 50 58 63 66 67 68 73 73 75 79 77 77 75 990 950 940 820 900 950 1030 1020 1050 1000 1050 1100 Marketing/Sales Number of Budget contracted Southeast restaurants Southeast 20,000.00 15,000.00 15,000.00 12,000.00 12,000.00 12,000.00 18,000.00 18,500.00 19,000.00 19,000.00 19,000.00 19,000.00 20,000.00 Demand Southwest 88 85 80 77 78 83 87 91 92 92 92 93 93 1550 1600 1450 1500 1600 1450 1640 1670 1750 1780 1800 1850 Marketing/S Number of ales Budget contracted Southwest restaurants Southwest 25,000.00 25,000.00 25,000.00 28,000.00 28,000.00 28,000.00 28,000.00 28,000.00 25,000.00 25,000.00 25,000.00 25,000.00 25,000.00 135 135 140 143 142 144 151 158 160 157 161 161 160 Production cost Market Northeast Northwest Southeast Southwest Capacity in thousand cases Koblenz 1,600.00 1,600.00 1,600.00 1,600.00 1,200 Bamberg 1,500.00 1,500.00 1,500.00 1,500.00 1,800 Oldenburg 1,800.00 1,800.00 1,800.00 1,800.00 New Brewery location Magdeburg Zwickau 1,400.00 1,600.00 1,400.00 1,600.00 1,400.00 1,600.00 1,400.00 1,600.00 1,200 Both production and transportation cost are per 1000 cases. The capacity is per month, in line with the forecasted monthly demand. 1,700 1,500 Transportation cost Market Koblenz Northeast 550.00 Northwest 350.00 Southeast 200.00 Southwest 400.00 Bamberg 450.00 500.00 210.00 300.00 Oldenburg 400.00 250.00 450.00 300.00 New Brewery Location Magdeburg Zwickau 200.00 250.00 300.00 350.00 300.00 ### 450.00 400.00 Zwickau Capacity Resulting total in 1000 capacity (all cases breweries) 1250 1500 1700 5450 5700 5900 Magdeburg Capacity Resulting total in 1000 capacity (all cases breweries) 1500 1700 2200 Production Cost per 1000 cases 1,800.00 1,600.00 1,400.00 Production Cost per 1000 cases 5700 5900 6400 Expected sales are given for 1 month. The capacity is also monthly capacity. 1,600.00 1,400.00 1,300.00 Expected aggregated sales good economy (in 1000 cases) 6000.00 6000.00 6000.00 Expected aggregated sales good economy (in 1000 cases) 6000.00 6000.00 6000.00 Expected aggregated sales medium economy (in 1000 cases) 5000.00 5000.00 5000.00 Expected aggregated sales medium economy (in 1000 cases) 5000.00 5000.00 5000.00 Expected Subcontracting cost aggregated sales per 1000 cases bad economy (in 1000 cases) 4300.00 4300.00 4300.00 2,500.00 2,500.00 2,500.00 Expectedaggrega Subcontracting cost ted sales bad per 1000 cases economy (in 1000 cases) 4300.00 4300.00 4300.00 3,500.00 3,500.00 3,500.00 There are three parts. The parts are only connected by the topic, not by the actual modeling results. Within Part I however, questions a) and b), build upon each other. In other words, the results from a) are inputs for b). If mistakes are made in part a), points are only reduced at this point. If part b) is solved correctly and is only leading to incorrect results because of the incorrect inputs from a), no further points are subtracted. The questions as well as data in the exam are entirely fictitious. Furthermore, the data does not represent realistic business data; it is chosen for the purpose of showcasing the student's ability to determine which decisionmaking methods should be used, so that unambiguous conclusions can be drawn. Make sure you are showing all steps of your work. For example, in part 2b) show how you calculated total profits, this is also worth points! Conclude all of your models with a written out answer. Part One A) K's Brewery is selling its famous beers (K Lager, K-IPA, and K's Black Belt Ale) in four sales regions throughout Germany. These four regions are Northeast, Northwest, Southeast and Southwest. Due to cultural, as well as historic and political differences, demand in the four regions differs. The firm uses two general marketing tools: advertisements and promotions (determined by the marketing budget) and signing up restaurants as contract distributors that exclusively sell K beers. Regional managers are allowed to employ varying emphasis on these two strategies, depending on what they think works best in a given region. The attached file includes sales data for the past 12 months, as well as marketing budgets and number of contracted restaurants for each region (also per month). Also included is the proposed marketing budget for each region for the next month, and the number of restaurants that are expected to be under contract in the next month. K's brewery's management wants to develop a model to forecast future demand. Which of the three possible predicting variables (marketing budget, number of contracted restaurants, or time) alone or in any possible combination represents the best forecasting model? Choose your preferred model, explain your choice, and forecast demand for the next month (Note: although beer sales may be subject to seasonality, you do not need to consider this to solve this problem). Further hints: The forecasts are done by each of the four regional managers, i.e. demand for each region is forecasted separately. The best forecasting model may not be based on the same predicting variable for each region. (Total points 30) B) K's Brewery is currently brewing in three different locations: Koblenz, Bamberg and Oldenburg (please consult the map for the location of these cities). The firm is considering building a fourth brewery in either Magdeburg or Zwickau, as demand is outpacing capacity. The attached file includes production and transportation costs, as well as maximum monthly capacity for each of the facilities. Using the forecast for next month's production as obtained in part a), determine if Magdeburg or Zwickau is the better location for an additional brewery (with respect to total production and transportation costs). Determine also how much beer should be brewed where and which brewery will deliver to which region. Hint: based on the given information, you need to determine how to serve the forecasted demand given the available capacity. Two separate models are needed: one including the new brewery in Magdeburg, the other with the new brewery in Zwickau. (Total points 21) Part Two K's Brewery is evaluating where to locate another brewery (again, while this answers a similar question then Part One b), consider this to be a completely independent problem). Furthermore, the management of K's brewery is contemplating if the currently planned additional capacity is sufficient (or too much). The plans include a capacity for Zwickau of 1250, 1500 or 1700 (in 1000 cases), and for Magdeburg 1500, 1700, or 2200. If capacity and demand do not match, the following happens: if capacity is above demand, the brewery will brew just according to demand; if capacity is below demand, K's brewery needs to subcontract, which incurs subcontracting costs. Depending on the size of the new facility, productions costs per 1000 cases will change also (due to increased or decreased economies of scale). The Part2 input data file includes all relevant information. . The probability for a good economy is estimated at 0.3, for a medium economy also at 0.3 and 0.4 for a bad economy. A case of beer sells for Euro4. Determine which location should be chosen, and which size. Discuss your findings. Hints: You need to calculate aggregated costs (i.e. totaled for aggregated production and then profits for the three different sizes of each facility, and for each economic state of nature. (total points 38) Part Three K's brewery is sponsoring sports games and putting up \"beer gardens\Production cost Market Northeast Northwest Southeast Southwest Capacity in thousand cases Koblenz 1,600.00 1,600.00 1,600.00 1,600.00 1,200 Bamberg 1,500.00 1,500.00 1,500.00 1,500.00 1,800 Oldenburg 1,800.00 1,800.00 1,800.00 1,800.00 New Brewery location Magdeburg Zwickau 1,400.00 1,600.00 1,400.00 1,600.00 1,400.00 1,600.00 1,400.00 1,600.00 1,200 1,700 Transportation cost Market Koblenz Northeast 550.00 Northwest 350.00 Southeast 200.00 Southwest 400.00 Bamberg 450.00 500.00 210.00 300.00 New Brewery Location Oldenburg Magdeburg Zwickau 400.00 200.00 250.00 300.00 450.00 300.00 ### 450.00 250.00 350.00 300.00 400.00 1,500 Both production and transportation cost are per 1000 cases. The capacity is per month, in line with the forecasted monthly demand. Model 1: New Brewery at Magdeburg Market Koblenz Bamberg Oldenburg Magdeburg Total Northeast 0 0 0 1567 Northwest 92 0 719 133 Southeast 1066 0 0 0 Southwest 43 1800 0 0 Total 1200 1800 719 1700 = Demand 1567 >= 1567 944 >= 944 1066 >= 1066 1843 >= 1843 5419 5419 Production + Transportation cost Market Koblenz Bamberg Northeast - Northwest 179,079 Southeast 1,918,060 Southwest 85,152 3,240,000 Total 2,182,290 3,240,000 Oldenburg 1,473,379 1,473,379 Magdeburg 2,506,776 226,551 2,733,327 Total >= Demand 1567 >= 1567 944 >= 944 1066 >= 1066 1843 >= 1843 5419 5419 Production + Transportation cost Market Koblenz Bamberg Northeast - Northwest 179,079 Southeast 1,918,060 Southwest 85,152 3,240,000 Total 2,182,290 3,240,000 Oldenburg 146,817 1,746,572 1,893,389 Zwickau 2,775,000 2,775,000 Total 2,921,817 1,925,651 1,918,060 3,325,152 10,090,680 2,506,776 1,879,008 1,918,060 3,325,152 9,628,996 Final Exam MBA604 Part A Period (month) Demand Northeast 1 2 3 4 5 6 7 8 9 10 11 12 13 Intercept B1 B2 1200 1300 1200 1400 1500 1450 1550 1700 1800 1630 1480 1480 1,566.73 176.155 0.038 0.800 Demand is given in Thousand cases. Marketing/sale Number of s budget contracted Northeast restaurants Northeast 25,000.00 28,000.00 30,000.00 30,000.00 30,000.00 35,000.00 35,000.00 38,000.00 35,000.00 35,000.00 35,000.00 35,000.00 35,000.00 Demand Northwest 80 85 93 95 95 95 95 95 95 95 95 95 95 Marketing/sa Number of Demand les budget contracted Southeast Northwest reastaurants Northeast 800 850 820 900 880 920 900 950 1020 930 990 1000 943.82 512.019 0.013 1.458 18,000.00 18,000.00 18,000.00 22,000.00 22,000.00 22,000.00 25,000.00 25,000.00 26,000.00 27,500.00 28,000.00 28,500.00 25,000.00 50 58 63 66 67 68 73 73 75 79 77 77 75 SS 2 161746.165 9 43520.501 11 205266.667 MS 80873.083 4835.611 990 950 940 820 900 950 1030 1020 1050 1000 1050 1100 1,065.59 -6.711 0.002 11.1 Marketing/Sales Number of Budget contracted Southeast restaurants Southeast 20,000.00 15,000.00 15,000.00 12,000.00 12,000.00 12,000.00 18,000.00 18,500.00 19,000.00 19,000.00 19,000.00 19,000.00 20,000.00 SUMMARY OUTPUT Regression Statistics Multiple R 0.888 R Square 0.788 Adjusted R Squa 0.741 Standard Error 69.539 Observations 12 Coefficients Standard Error Intercept 823.196 499.503 Marketing/Sales -0.029 0.014 Number of contr 10.574 2.072 ANOVA df Regression Residual Total t Stat 1.648 -2.122 5.103 F Significance F 16.724 0.001 P-value Lower 95% Upper 95% Lower 95.0% 0.134 -306.759 1953.151 -306.759 0.063 -0.060 0.002 -0.060 0.001 5.886 15.262 5.886 Upper 95.0% 1953.151 0.002 15.262 Demand Southwest 88 85 80 77 78 83 87 91 92 92 92 93 93 Marketing/Sal Number of es Budget contracted Southwest restaurants Southwest 1550 25,000.00 1600 25,000.00 1450 25,000.00 1500 28,000.00 1600 28,000.00 1450 28,000.00 1640 28,000.00 1670 28,000.00 1750 25,000.00 1780 25,000.00 1800 25,000.00 1850 25,000.00 1843 25,000.00 823.196 -0.029 Insignificant 10.574 Insignificant 135 135 140 143 142 144 151 158 160 157 161 161 160 Zwickau Capacity Resulting total in 1000 capacity (all cases breweries) 1250 1500 1700 5450 5700 5900 Magdeburg Capacity Resulting total in 1000 capacity (all cases breweries) 1500 1700 2200 Cost per 1000 cases 1,800.00 1,600.00 1,400.00 Cost per 1000 cases 5700 5900 6400 Expected sales are given for 1 month. The capacity is also monthly capacity. 1,600.00 1,400.00 1,300.00 0.3 0.3 0.4 Expected aggregated Expected aggregated Expected Subcontracting cost sales good economy sales medium aggregated sales per 1000 cases (in 1000 cases) economy (in 1000 bad economy (in cases) 1000 cases) 6000.00 6000.00 6000.00 Expected aggregated sales good economy (in 1000 cases) 6000.00 6000.00 6000.00 5000.00 5000.00 5000.00 Expected aggregated sales medium economy (in 1000 cases) 5000.00 5000.00 5000.00 4300.00 4300.00 4300.00 Expected aggregate d sales 2,500.00 2,500.00 2,500.00 5020 5020 5020 Expectedaggrega Subcontracting cost ted sales bad per 1000 cases economy (in 1000 cases) Expected aggregate d sales 4300.00 4300.00 4300.00 2,500.00 2,500.00 2,500.00 5020 5020 5020 Mat+manu cost for 1000 cases Subcontra Transport cting cost ation cost Revenue Net profit 4,154.00 0 10090680 20080000 9,985,166.47 3,954.00 0 10090680 20080000 9,985,366.47 3,754.00 0 10090680 20080000 9,985,566.47 Mat+manu cost for 1000 cases Subcontra Transport cting cost ation cost Revenue Net profit 3,954.00 0 9628996 20080000 10,447,050.20 3,754.00 0 9628996 20080000 10,447,250.20 3,654.00 0 9628996 20080000 10,447,350.20 Cost per 1000 cases beer brewed Probability cost*prob 1,800 0.08 144 2,000 0.12 240 2,200 0.25 550 2,400 0.2 480 2,600 0.2 520 2,800 0.15 420 EMC 2,354 Market Manufacturing cost Material cost Total cost Koblenz 1,600 2,354 3,954 Bamberg 1,500 2,354 3,854 Oldenburg 1,800 2,354 4,154 Magdeburg 1,400 2,354 3,754 Zwickau 1,600 2,354 3,954 Final Exam MBA604 Fall 2014 Explanation and Disclaimer The exam consists of two parts. The two parts are only connected by the topic, not by the actual mathematical results. Within each part, questions a) and b), however, build upon each other. In other words, the results from part a) are inputs for part b). If mistakes are made in part a), points are only reduced at this point. If part b) is solved correctly and is only leading to incorrect results because of the incorrect inputs from a), no further points are subtracted. The questions as well as data in the exam are entirely fictitious. Furthermore, the data does not represent realistic business data; it is chosen for the purpose of showcasing the student's ability to determine which decision-making methods should be used, so that unambiguous conclusions can be drawn. Make sure you are showing all steps of your work. For example, in part 2b) show how you calculated total profits, this is also worth points! Part One A) K's Brewery is selling its famous beers (K Lager, K-IPA, and K's Black Belt Ale) in four sales regions throughout Germany. These four regions are Northeast, Northwest, Southeast and Southwest. Due to cultural, as well as historic and political differences, demand in the four regions differs. The firm uses two general marketing tools: advertisements and promotions (determined by the marketing budget) and signing up restaurants as contract distributors that exclusively sell K beers. Regional managers are allowed to employ varying emphasis on these two strategies, depending on what they think works best in a given region. The attached file includes sales data for the past 12 months, as well as marketing budgets and number of contracted restaurants for each region (also per month). Also included is the proposed marketing budget for each region for the next month, and the number of restaurants that are expected to be under contract in the next month. K's brewery's management wants to develop a model to forecast future demand. Which of the three possible predicting variables (marketing budget, number of contracted restaurants, or time) alone or in any possible combination represents the best forecasting model? Choose your preferred model, explain your choice, and forecast demand for the next month (Note: although beer sales may be subject to seasonality, you do not need to consider this to solve this problem). Further hints: The forecasts are done by each of the four regional managers, i.e. demand for each region is forecasted separately. The best regression model may not be the same in each region. (Total points 30) Answer: (1) The best regression model on North east region is the multivariable regression on both marketing spend and contracted distributors. The output summery are as shown below: SUMMARY OUTPUT: North East Regression Statistics Multiple R 0.800 R Square 0.640 Adjusted R Square 0.560 122.38 0 Standard Error Observations Intercept Marketing/sales budget Northeast Number of contracted restaurants Northeast ANOVA df MS 119949. 415 14976.9 82 F Significanc eF 8.009 0.010 Upper 95% Lower 95.0% Upper 95.0% 1965.63 1 1613.321 1965.631 12.000 Coeffici ents Standard Error t Stat P-value 176.15 5 791.049 0.223 0.829 SS 239898. 830 134792. 837 374691. 667 Lower 95% 1613.32 1 0.038 0.016 2.411 0.039 0.002 0.073 0.002 0.073 0.800 12.139 0.066 0.949 -26.659 28.259 -26.659 28.259 Regression 2 Residual 9 Total 11 By using the Intercept, B1 and B2 values, we can estimate the demand to be 1567 thousand cases on an average. (2) The best regression model on North West region is the multivariable regression on both marketing spend and contracted distributors. The output summery are as shown below: SUMMARY OUTPUT: North West Regression Statistics Multiple R 0.897 R Square 0.805 Adjusted R Square 0.762 Regression 2 Standard Error 34.019 Residual 9 Observations 12 Total 11 ANOVA df SS 43051.1 51 10415.5 16 53466.6 67 MS 21525.5 75 1157.28 0 F Significanc eF 18.600 0.001 Intercept Marketing/sales budget Northwest Number of contracted restaurants Northeast Coefficie nts 512.019 0.013 Standard Error 92.217 0.007 t Stat 5.552 1.755 1.458 3.321 0.439 P-value 0.000 0.113 Lower 95% 303.411 -0.004 Upper 95% 720.628 0.030 Lower 95.0% 303.411 -0.004 Upper 95.0% 720.628 0.030 0.671 -6.055 8.972 -6.055 8.972 By using the Intercept, B1 and B2 values, we can estimate the demand to be 944 thousand cases on an average. (3) The best regression model on South East region is the multivariable regression on both marketing spend and contracted distributors. The output summery are as shown below: SUMMARY OUTPUT : South East Regression Statistics Multiple R 0.92 R Square 0.84 Adjusted R Square 0.80 Regression 2 Standard Error 33.73 Residual 9 Observations 12 Total 11 Standard Error t Stat P-value 239.095 0.007 -0.028 0.246 3.951 2.809 Coefficie nts Intercept -6.711 Marketing/Sales Budget Southeast 0.002 Number of contracted restaurants Southeast 11.100 ANOVA SS 53829.2 23 10237.4 44 64066.6 67 MS 26914.6 11 1137.49 4 F Significanc eF 23.661 0.000 Upper 95% Lower 95.0% Upper 95.0% 0.978 0.811 Lower 95% 547.580 -0.015 534.159 0.018 -547.580 -0.015 534.159 0.018 0.020 2.162 20.038 2.162 20.038 df By using the Intercept, B1 and B2 values, we can estimate the demand to be 1066 thousand cases on an average. (4) In south west region even the best regression model has no significance as T-stat value are higher. And the negative B1 value is counter-intuitive. (It cannot be true that increasing marketing spend decreases sales). The regression model is as shown below SUMMARY OUTPUT : South West Regression Statistics Multiple R 0.888 ANOVA R Square 0.788 Adjusted R Square 0.741 Regression 2 Standard Error 69.539 Residual 9 Observations 12 Coefficien ts 823.196 Total 11 Standard Error 499.503 t Stat 1.648 0.014 2.072 Intercept Marketing/Sales Budget Southwest -0.029 Number of contracted restaurants Southwest 10.574 df F Significance F 16.724 0.001 Upper 95% 1953.151 Lower 95.0% -306.759 Upper 95.0% 1953.151 -0.060 0.002 -0.060 0.002 5.886 15.262 5.886 15.262 MS 80873.08 3 P-value 0.134 SS 161746.1 65 43520.50 1 205266.6 67 Lower 95% -306.759 -2.122 0.063 5.103 0.001 4835.611 Hence, the model does not provide any accurate estimate. Instead, by looking at the data, we can assume that the demand is fairly same irrespective of marketing spend or contracted restaurant. Hence, a simple regression with no of period, gives as fairly linear forecast. The forecast calculated here was 1843 thousand cases. B) K's Brewery is currently brewing in three different locations: Koblenz, Bamberg and Oldenburg (please consult the map for the location of these cities). The firm is considering building a fourth brewery in either Magdeburg or Zwickau, as demand is outpacing capacity. The attached file includes production and transportation costs, as well as maximum monthly capacity for each of the facilities. Using the forecast for next month's production as obtained in part a), determine if Magdeburg or Zwickau is the better location for an additional brewery (with respect to total production and transportation costs). Determine also how much beer should be brewed where and which brewery will deliver to which region. Hint: based on the given information, you need to determine how to serve the forecasted demand given the available capacity. Two separate models are needed: one including the new brewery in Magdeburg, the other with the new brewery in Zwickau. (Total points 21) Answer: To analyses the optimized production and transportation schedule, we build a Simplex LP model in the excel file attached. According to the output: if we start new brewery at Magdeburg then the total cost of supply chain (production + transportation) would be 9,628,996. While Opening the new brewery at Zwickau would cost us 10,090,680. Hence, new Brewery must be open at Magdeburg. The optimized production and transportation schedule is as follows: Market Northeast Northwest Southeast Southwest Total Production Koblenz 0 92 1066 43 Bamberg 0 0 0 1800 Oldenb urg 0 719 0 0 Magdebur g 1567 133 0 0 Total supply 1567 944 1066 1843 1200 1800 719 1700 5419 Part Two K's Brewery is also evaluating different approaches to the decision where to locate the second brewery. These approaches consider a) operations costs for each facility; and b) if the capacity to be added at either Magdeburg or Zwickau is sufficient/too much. a) Operations cost Total fixed (production) costs can be calculated from the information in part 1b). Raw materials costs have to be added; however, in particular the cost for hops has seen significant fluctuations due to bad harvests. The following table is based on data for the past 5 years and shows the probability for raw materials costs per 1000 cases beer brewed. What would be the expected cost of raw materials per 1000 cases? Round to an integer. (Total points 11) Determine total operations cost per 1000 cases for each facility that includes the expected cost for raw material plus the before mentioned fixed costs. Cost per 1000 cases beer brewed 1800 Probability 0.08 2000 2200 2400 2600 2800 0.12 0.25 0.2 0.2 0.15 Answer: (1) The estimated cost of material is calculated using Expected Mean Cost concept. The calculations are as below: Cost per 1000 cases beer brewed 1,800 2,000 2,200 2,400 2,600 2,800 EMC Probabi lity cost*pr ob 0.08 0.12 0.25 0.2 0.2 0.15 144 240 550 480 520 420 2,354 The expected mean cost would be 2354 per 1000 cases of bear. (2) The total operation cost (material +manufacturing cost) are as below: Market Manufacturing cost Koblenz Material cost 2,354 Total cost 3,954 Bamberg 1,600 1,500 2,354 3,854 Oldenburg 1,800 2,354 4,154 Magdeburg 1,400 2,354 3,754 Zwickau 1,600 2,354 3,954 b) The management of K's brewery is also contemplating if the currently planned additional capacity is sufficient (or too much). The plans include a capacity for Zwickau of 1250, 1500 or 1700 (in 1000 cases), and for Magdeburg 1500, 1700 ,or 2200. Besides the immediate forecasts for the next month (Part 1a), long-term forecasts of demand were undertaken to judge aggregated (across all regions) demand depending on the state of the economy. Depending on the size of the new facility, productions costs per 1000 cases will change also (due to increased or decreased economies of scale). If capacity and demand do not match, the following happens: if capacity is above demand, the brewery will brew just according to demand; if capacity is below demand, K's brewery needs to subcontract, which incurs cost subcontracting costs. The Part2 input data file includes all relevant information. . The probability for a good economy is estimated at 0.3, for a medium economy also at 0.3 and 0.4 for a bad economy. A case of beer sells for Euro4. Hints: You need to calculate aggregated costs (i.e. totaled for aggregated production including raw material costs, based on your answer from part a), and then profits for the three different sizes of each facility, and for each economic state of nature. Determine which location should be chosen, and which size. Discuss your findings. (total points 38) Answer: The detailed calculations are shown in the excel file. The summery is as below: Site Capaci ty 1250 Net profit 9,985,166 Zwickau 1500 9,985,366 1700 9,985,566 1500 10,447,050 Magdebu rg 1700 10,447,250 2200 10,447,350 The calculation suggests that maximum profit would occur if we have plant at Magdeburg of 2200 thousand cases capacity. However, the analysis ignores the capital cost of plant capacity building, as it is not provided. While the profit increase between lowest capacity and highest capacity is just 300 (!), the capital cost attached with the access capacity must be huge. Against that demand is expected to be 5020 thousand cases, even the lowest capacity at Magdeburg plant would easily capable the demand. Hence, I would suggest of having capacity of 1500 thousand cases at Magdeburg. There are three parts. The parts are only connected by the topic, not by the actual modeling results. Within Part I however, questions a) and b), build upon each other. In other words, the results from a) are inputs for b). If mistakes are made in part a), points are only reduced at this point. If part b) is solved correctly and is only leading to incorrect results because of the incorrect inputs from a), no further points are subtracted. The questions as well as data in the exam are entirely fictitious. Furthermore, the data does not represent realistic business data; it is chosen for the purpose of showcasing the student's ability to determine which decisionmaking methods should be used, so that unambiguous conclusions can be drawn. Make sure you are showing all steps of your work. For example, in part 2b) show how you calculated total profits, this is also worth points! Conclude all of your models with a written out answer. Part One A) K's Brewery is selling its famous beers (K Lager, K-IPA, and K's Black Belt Ale) in four sales regions throughout Germany. These four regions are Northeast, Northwest, Southeast and Southwest. Due to cultural, as well as historic and political differences, demand in the four regions differs. The firm uses two general marketing tools: advertisements and promotions (determined by the marketing budget) and signing up restaurants as contract distributors that exclusively sell K beers. Regional managers are allowed to employ varying emphasis on these two strategies, depending on what they think works best in a given region. The attached file includes sales data for the past 12 months, as well as marketing budgets and number of contracted restaurants for each region (also per month). Also included is the proposed marketing budget for each region for the next month, and the number of restaurants that are expected to be under contract in the next month. K's brewery's management wants to develop a model to forecast future demand. Which of the three possible predicting variables (marketing budget, number of contracted restaurants, or time) alone or in any possible combination represents the best forecasting model? Choose your preferred model, explain your choice, and forecast demand for the next month (Note: although beer sales may be subject to seasonality, you do not need to consider this to solve this problem). Further hints: The forecasts are done by each of the four regional managers, i.e. demand for each region is forecasted separately. The best forecasting model may not be based on the same predicting variable for each region. (Total points 30) B) K's Brewery is currently brewing in three different locations: Koblenz, Bamberg and Oldenburg (please consult the map for the location of these cities). The firm is considering building a fourth brewery in either Magdeburg or Zwickau, as demand is outpacing capacity. The attached file includes production and transportation costs, as well as maximum monthly capacity for each of the facilities. Using the forecast for next month's production as obtained in part a), determine if Magdeburg or Zwickau is the better location for an additional brewery (with respect to total production and transportation costs). Determine also how much beer should be brewed where and which brewery will deliver to which region. Hint: based on the given information, you need to determine how to serve the forecasted demand given the available capacity. Two separate models are needed: one including the new brewery in Magdeburg, the other with the new brewery in Zwickau. (Total points 21) Part Two K's Brewery is evaluating where to locate another brewery (again, while this answers a similar question then Part One b), consider this to be a completely independent problem). Furthermore, the management of K's brewery is contemplating if the currently planned additional capacity is sufficient (or too much). The plans include a capacity for Zwickau of 1250, 1500 or 1700 (in 1000 cases), and for Magdeburg 1500, 1700, or 2200. If capacity and demand do not match, the following happens: if capacity is above demand, the brewery will brew just according to demand; if capacity is below demand, K's brewery needs to subcontract, which incurs subcontracting costs. Depending on the size of the new facility, productions costs per 1000 cases will change also (due to increased or decreased economies of scale). The Part2 input data file includes all relevant information. . The probability for a good economy is estimated at 0.3, for a medium economy also at 0.3 and 0.4 for a bad economy. A case of beer sells for Euro4. Determine which location should be chosen, and which size. Discuss your findings. Hints: You need to calculate aggregated costs (i.e. totaled for aggregated production and then profits for the three different sizes of each facility, and for each economic state of nature. (total points 38) Part Three K's brewery is sponsoring sports games and putting up \"beer gardens\