Homework answers / question archive / Costello Cabts Quality and Beauty Always Operations Management 330 HOMEWORK Forecasting Project The Costello Countertop Company has been in business for 5 years

Costello Cabts Quality and Beauty Always Operations Management 330 HOMEWORK Forecasting Project The Costello Countertop Company has been in business for 5 years

Business

Share With

Costello Cabts Quality and Beauty Always Operations Management 330 HOMEWORK Forecasting Project The Costello Countertop Company has been in business for 5 years. During that time, the owner recorded the sales of its countertops in tons of material sold every two months. The firm's owner wants to develop a mathematical model to forecast next year's sales. The historical data follows: Bi-Monthly Time Period Year 1 2 3 4 5 6 Total Sales 1 8 4 6 6 10 16 50 2 12 8 8 10 28 30 96 3 20 6 10 18 32 38 124 4 24 18 14 20 38 44 158 5 30 20 26 44 50 62 232 1. 2. 3. 4. 5. Compute the seasonal indexes for the bi-monthly time periods. Compute a trend line using the appropriate data. Forecast next year's sales using the trend line model. Forecast the sales for each bi-monthly time period next year. Think about your results. Which bimonthly time period has the highest sales and which has the lowest sales? How might this information help Mr. Costello plan labor and material needs? You must use a spreadsheet to make all calculations. Then complete a chase and level production plan using the data on the next page. You can download the file to Excel directly from Blackboard. When you are finished, complete the memo on the page after the aggregate plan by replacing any spaces marked in red with the correct answer. Some spaces include multiple choice answers for which you must choose the correct answer. You must input your answers in Blackboard by clicking on the Chapter 14 link entitled, Submit Forecasting and Aggregate Planning Memo Answers. You have 1 hour to finish the submission. Then upload your Excel files to Blackboard, Submit Excel Files. Costello Cabts Quality and Beauty Always Costello Countertop Company Production Rate (tons/wkr) 2 Reg. Wage Rate ($/ton) 500 Hiring Cost ($/wkr) 1000 Overtime limit (tons) 5 Overtime Wage Rate ($/ton) 750 Firing Cost ($/wkr) 500 Inventory Holding Cost ($/ton) 500 Subcont. Rate ($/ton) 900 Month Demand Regular Time Production Overtime Subconract January February March April May June July August September October November December 18 18 12 12 14 14 20 20 32 32 36 36 Regular Time Overtime Subcontracting Inventory 0 Using the data in this table, calculate a chase demand production plan and a level production plan for Year 6 for Costello Countertop Company. You may not need to use all columns. Complete a memo giving your recommendation and explanation. You must use a spreadsheet to make your calculations. No. of Workers 10 No. Hired No. Fired Hiring Firing Costs Month January February March April May June July August September October November December Total Cost Inventory Holding Costello Cabts Quality and Beauty Always MEMORANDUM TO: Mr. Costello FROM: XXXXXXXXXX SUBJECT: Results of Requested Forecast 5 May 2018 Attached are the results of next year’s sales forecast. After examining several quantitative models, I determined that the data best fits a linear regression forecasting model, with seasonal adjustments. My analysis shows that the line which best fits the historical annual demand is given by the equation (1)_____. From this model, I can predict that next year’s annual sales volume will be about (2) units. I am able to make this prediction by substituting the number, (3) , for x in the equation, which corresponds to next year. You may be interested in further understanding how this equation might be interpreted. The b-coefficient, which has the value, (4) , represents the approximate change in (5) that is expected each (6) . Annual sales are affected by a very distinct seasonal pattern, so it is important to predict sales behavior throughout the year. The company should plan accordingly, as the widely fluctuating seasonal pattern affects resource needs. Historical data shows that approximately (7) percent of annual demand occurs in the first two months of the year, and that is followed by subsequent percentages for each of the next five bi-monthly time periods for the remainder of the year: (8) , (9) , (10) , (11) , and (12), respectively. Based on these percentages, and the annual demand predicted by the forecasting model, I can provide an estimate of the following sales amounts for each of the bi-monthly time-periods during the year: (13), (14), (15), (16), (17), and (18). The accuracy of the forecasting model can be determined from several measures. The MAD, or mean absolute deviation, is (19) . This number describes the approximate average difference between historical actual demand and predicted demand each (20) . The MAPD, which stands for (21) , is (22) , which means our level of confidence in the forecast is (extremely high, moderate, unacceptable). The correlation between the actual annual demand and forecasted annual demand is (23) , which is (very strong, moderate, very weak). Taken as a whole, these multiple measures of accuracy show (consistent, moderately mixed, extremely disparate) error results, so the forecast should be (used as an exact prediction of sales, used with some caution and minor adjustments for error, completely abandoned as useless). Costello Cabts Quality and Beauty Always Two aggregate plan options are also included in this analysis. The two choices evaluated are the level production plan and the chase demand plan. The level production plan maintains even production throughout the year, while the chase demand production plan follows demand exactly. For the level production plan, a major cost is incurred from high (inventory levels, hiring and firing costs). The chase demand plan, on the other hand, incurs higher (inventory cost, hiring and firing costs). The total cost of the level production plan is _____________, and the total cost of the chase demand plan is ______________. On the basis of cost alone, the best choice is to use the (level production plan, chase demand plan). There may be other considerations, however. For example, if our workforce is very highly trained and difficult to recruit, you may wish to consider the (level production plan, chase demand plan). COSTELLO CABINET SALES FORECAST Year 1 2 3 4 5 Sums: Seasonal Factors: Linear Regression 1 Bi-Monthly Time Period 2 3 4 5 6 8 12 20 24 30 4 8 6 18 20 16 30 38 44 62 6 8 10 14 26 6 10 18 20 44 10 28 32 38 50 Total Sales 50 96 124 158 232 x y xy Avgs: b= a= y= Forecast for Year 6: Error Calculations Period Number Season Period al ForeFactor cast Actual Demand Forecast Error Sums MAD= MAPD= r= Regression x2 alculations Abs Error COSTELLO CABINET SALES FORECAST Year 1 2 3 4 5 Sums: Seasonal Factors: Linear Regression 1 Bi-Monthly Time Period 2 3 4 5 6 8 12 20 24 30 4 8 6 18 20 16 30 38 44 62 6 8 10 14 26 6 10 18 20 44 10 28 32 38 50 Total Sales 50 96 124 158 232 x y xy Avgs: b= a= y= Forecast for Year 6: Error Calculations Period Number Season Period al ForeFactor cast Actual Demand Forecast Error Sums MAD= MAPD= r= Regression x2 alculations Abs Error Sheet1 Costello Countertop Company Production Rate (tons/wkr) Inventory Holding Cost ($/ton) 2 Reg. Wage Rate ($/ton) 500 500 Month Demand January February March April May June July August September October November December 18 18 12 12 14 14 20 20 32 32 36 36 Regular Time Production Overtime Regular Time Overtime Hiring Cost ($/wkr) 1000 Firing Cost ($/wkr) 500 Sub-conract Inventory 0 No. of Workers 10 No. Hired No. Fired Costs Month January February March April May June July August September October November December Total Cost = SubInventory contracting Holding 0 Page 1 Hiring Firing Sheet2 Page 2 Sheet3 Page 3 Sheet4 Page 4 Sheet6 Page 5 11:47 < Forecasting and Aggregate Planning... Operations Management 330 HOMEWORK 4 5 6 Total Sales 50 1 96 Forecasting Project The Costello Countertop Company has been in business for 5 years. During that time, the owner recorded the sales of its countertops in tons of material sold every two months. The firm's owner wants to develop a mathematical model to forecast next year's sales. The historical data follows: Bi-Monthly Time Period Year 1 2 3 8 4 6 6 10 16 2 12 8 8 10 28 30 3 20 6 10 18 32 38 124 4 24 18 14 20 38 44 158 5 30 20 26 44 50 62 232 1. Compute the seasonal indexes for the bi-monthly time periods. 2. Compute a trend line using the appropriate data. 3. Forecast next year's sales using the trend line model. 4. Forecast the sales for each bi-monthly time period next year. 5. Think about your results. Which bimonthly time period has the highest sales and which has the lowest sales? How might this information help Mr. Costello plan labor and material needs? You must use a spreadsheet to make all calculations. Then complete a chase and level production plan using the data on the next page. You can download the file to Excel directly from Blackboard. When you are finished, complete the memo on the page after the aggregate plan by replacing any spaces marked in red with the correct answer. Some spaces include multiple choice answers for which you must choose the correct answer. You must input your answers in Blackboard by clicking on the Chapter 14 link entitled, Submit Forecasting and Aggregate Planning Memo Answers. You have 1 hour to finish the submission. Then upload your Excel files to Blackboard, Submit Excel Files. Costello Countertop Company Production Rate (tons/wkr) Hiring Cost (S/wkr) Using the data in this table, calculate a chase demand production plan and a level production plan for Year 6 for Costello Countertop Company You may not need to use all columns. Complete a memo giving your recommendation and explanation. You must use a spreadsheet to make your calculations. 2 500 1000 Reg. Wage Rate (S/ton) Overtime Wage Rate (S/ton) Overtime limit (tons) Firing Cost (S/wkr) 5 750 500 Inventory Holding Subcont. Rate (S/ton) Cost ($/ton) 500 900 Regular Time Demand Production Over- time Sub-conract Inventory No. of Workers No. Hired No. Fired Month 9 12 10 DOO Dashboard Calendar To Do Notifications Inbox 11:47 < Forecasting and Aggregate Planning... Total Cost MEMORANDUM TO: Mr. Costello FROM: XXXXXXXXXX SUBJECT: Results of Requested Forecast 5 May 2018 Attached are the results of next year's sales forecast. After examining several quantitative models, I determined that the data best fits a linear regression forecasting model, with seasonal adjustments. My analysis shows that the line which best fits the historical annual demand is given by the equation (1) From this model, I can predict that next year's annual sales volume will be about (2) units. I am able to make this prediction by substituting the number, _(3)_ , for x in the equation, which corresponds to next year. You may be interested in further understanding how this equation might be interpreted. The b-coefficient, which has the value, _(4)_ represents the approximate change in _(5)__ that is expected each _(6)__ Annual sales are affected by a very distinct seasonal pattern, so it is important to predict sales behavior throughout the year. The company should plan accordingly, as the widely fluctuating seasonal pattern affects resource needs. Historical data shows that approximately (7) percent of annual demand occurs in the first two months of the year, and that is followed by subsequent percentages for each of the next five bi-monthly time periods for the remainder of the year: (8), (2), (10), (11), and (12), respectively. Based on these percentages, and the annual demand predicted by the forecasting model, I can provide an estimate of the following sales amounts for each of the bi-monthly time-periods during the year: _(13), (14), (15), (16), (17), and (18) The accuracy of the forecasting model can be determined from several measures. The MAD, or mean absolute deviation, is _(19). This number describes the approximate average difference between historical actual demand and predicted demand each (20) . The MAPD, which stands for (21), is _(22)_, which means our level of confidence in the forecast is extremely high, moderate, unacceptable). The correlation between the actual annual 12 10 Dashboard Calendar To Do Notifications Inbox 11:47 < Forecasting and Aggregate Planning... G You must use a spreadsheet to make all calculations. Then complete a chase and level production plan using the data on the next page. You can download the file to Excel directly from Blackboard. When you are finished, complete the memo on the page after the aggregate plan by replacing any spaces marked in red with the correct answer. Some spaces include multiple choice answers for which you must choose the correct answer. You must input your answers in Blackboard by clicking on the Chapter 14 link entitled, Submit Forecasting and Aggregate Planning Memo Answers. You have 1 hour to finish the submission. Then upload your Excel files to Blackboard, Submit Excel Files. Costello Countertop Company Production Rate (tons/wkr) Using the data in this table, calculate a chase demand production plan and a level production plan for Year 6 for Costello Countertop Company. You may not need to use all columns. Complete a memo giving your recommendation and explanation. You must use a spreadsheet to make your calculations. Hiring Cost (S/wkr) 2 500 1000 Reg. Wage Rate (S/ton) Overtime Wage Rate (S/ton) Overtime limit (tons) Firing Cost (S/wkr) 5 750 500 Inventory Holding Cost ($/ton) Subcont. Rate (S/ton) 500 900 Regular Time Demand Production Over- time Sub-conract Inventory 0 No. of Workers No. Hired No. Fired Month 10 18 18 12 12 14 14 January February March April May June July August September October November December 20 20 32 32 36 36 Regular Time Over- time Costs Sub- contracting Inventory Holding Month Hiring Firing January February March April May June July August September October November December Total Cost MEMORANDUM 12 10 DOO Dashboard Calendar To Do Notifications Inbox 11:47 < Forecasting and Aggregate Planning... Operations Management 330 HOMEWORK 4 5 6 Total Sales 50 1 96 Forecasting Project The Costello Countertop Company has been in business for 5 years. During that time, the owner recorded the sales of its countertops in tons of material sold every two months. The firm's owner wants to develop a mathematical model to forecast next year's sales. The historical data follows: Bi-Monthly Time Period Year 1 2 3 8 4 6 6 10 16 2 12 8 8 10 28 30 3 20 6 10 18 32 38 124 4 24 18 14 20 38 44 158 5 30 20 26 44 50 62 232 1. Compute the seasonal indexes for the bi-monthly time periods. 2. Compute a trend line using the appropriate data. 3. Forecast next year's sales using the trend line model. 4. Forecast the sales for each bi-monthly time period next year. 5. Think about your results. Which bimonthly time period has the highest sales and which has the lowest sales? How might this information help Mr. Costello plan labor and material needs? You must use a spreadsheet to make all calculations. Then complete a chase and level production plan using the data on the next page. You can download the file to Excel directly from Blackboard. When you are finished, complete the memo on the page after the aggregate plan by replacing any spaces marked in red with the correct answer. Some spaces include multiple choice answers for which you must choose the correct answer. You must input your answers in Blackboard by clicking on the Chapter 14 link entitled, Submit Forecasting and Aggregate Planning Memo Answers. You have 1 hour to finish the submission. Then upload your Excel files to Blackboard, Submit Excel Files. Costello Countertop Company Production Rate (tons/wkr) Hiring Cost (S/wkr) Using the data in this table, calculate a chase demand production plan and a level production plan for Year 6 for Costello Countertop Company You may not need to use all columns. Complete a memo giving your recommendation and explanation. You must use a spreadsheet to make your calculations. 2 500 1000 Reg. Wage Rate (S/ton) Overtime Wage Rate (S/ton) Overtime limit (tons) Firing Cost (S/wkr) 5 750 500 Inventory Holding Subcont. Rate (S/ton) Cost ($/ton) 500 900 Regular Time Demand Production Over- time Sub-conract Inventory No. of Workers No. Hired No. Fired Month 9 12 10 DOO Dashboard Calendar To Do Notifications Inbox 11:47 < Forecasting and Aggregate Planning... G You must use a spreadsheet to make all calculations. Then complete a chase and level production plan using the data on the next page. You can download the file to Excel directly from Blackboard. When you are finished, complete the memo on the page after the aggregate plan by replacing any spaces marked in red with the correct answer. Some spaces include multiple choice answers for which you must choose the correct answer. You must input your answers in Blackboard by clicking on the Chapter 14 link entitled, Submit Forecasting and Aggregate Planning Memo Answers. You have 1 hour to finish the submission. Then upload your Excel files to Blackboard, Submit Excel Files. Costello Countertop Company Production Rate (tons/wkr) Using the data in this table, calculate a chase demand production plan and a level production plan for Year 6 for Costello Countertop Company. You may not need to use all columns. Complete a memo giving your recommendation and explanation. You must use a spreadsheet to make your calculations. Hiring Cost (S/wkr) 2 500 1000 Reg. Wage Rate (S/ton) Overtime Wage Rate (S/ton) Overtime limit (tons) Firing Cost (S/wkr) 5 750 500 Inventory Holding Cost ($/ton) Subcont. Rate (S/ton) 500 900 Regular Time Demand Production Over- time Sub-conract Inventory 0 No. of Workers No. Hired No. Fired Month 10 18 18 12 12 14 14 January February March April May June July August September October November December 20 20 32 32 36 36 Regular Time Over- time Costs Sub- contracting Inventory Holding Month Hiring Firing January February March April May June July August September October November December Total Cost MEMORANDUM 12 10 DOO Dashboard Calendar To Do Notifications Inbox 11:47 < Forecasting and Aggregate Planning... Total Cost MEMORANDUM TO: Mr. Costello FROM: XXXXXXXXXX SUBJECT: Results of Requested Forecast 5 May 2018 Attached are the results of next year's sales forecast. After examining several quantitative models, I determined that the data best fits a linear regression forecasting model, with seasonal adjustments. My analysis shows that the line which best fits the historical annual demand is given by the equation (1) From this model, I can predict that next year's annual sales volume will be about (2) units. I am able to make this prediction by substituting the number, _(3)_ , for x in the equation, which corresponds to next year. You may be interested in further understanding how this equation might be interpreted. The b-coefficient, which has the value, _(4)_ represents the approximate change in _(5)__ that is expected each _(6)__ Annual sales are affected by a very distinct seasonal pattern, so it is important to predict sales behavior throughout the year. The company should plan accordingly, as the widely fluctuating seasonal pattern affects resource needs. Historical data shows that approximately (7) percent of annual demand occurs in the first two months of the year, and that is followed by subsequent percentages for each of the next five bi-monthly time periods for the remainder of the year: (8), (2), (10), (11), and (12), respectively. Based on these percentages, and the annual demand predicted by the forecasting model, I can provide an estimate of the following sales amounts for each of the bi-monthly time-periods during the year: _(13), (14), (15), (16), (17), and (18) The accuracy of the forecasting model can be determined from several measures. The MAD, or mean absolute deviation, is _(19). This number describes the approximate average difference between historical actual demand and predicted demand each (20) . The MAPD, which stands for (21), is _(22)_, which means our level of confidence in the forecast is extremely high, moderate, unacceptable).