60.1K

Verified Solution

Link Copied!

step by step please

- Use Microsoft Excel ONLY to solve the problems. If an answer requires written arguments or explanations, you must do it in the Excel file. - You are required to submit ONE Excel file only. Answer questions 1a,1b and 1C, and problem 2 on different worksheets. Altogether you will have four worksheets in one workbook, including the cover sheet (next bullet point). - The file you submit must have a cover sheet that shows your first and last name, student number, and course and section numbers. Use the first worksheet in the Excel workbook you will submit as a coversheet. - The file must be uploaded to Brightspace using the Assignment2 link by the due date to get a credit. Email submission is neither accepted nor credited. - The file you subInit MUST be an Excel file and openable in the Windows environment. - Verify that your assignment file is correctly submitted through the course website. A 10% deduction per day will apply to late submissions for the first three days only; wrong files will automatically receive a grade of zero. Assignment Questions: PROBLEM 1: (20 marks) The Fowler Martial Arts Academy trains young boys and girls in selfdefence. Joan Fowler, the academy owner, notes that monthly revenue is higher when school is in session but quite low when school is out (because many children are away on vacation or at summer camp). She has researched revenues for the past four years and obtained the information shown MonthlyRevenue Excel file from Brightspace in the "Data Files" folder. Answer the following questions based on the data provided in the file. a) Forecast the revenue for January Year 5 using the moving averages method. Consider three different numbers of periods for the moving average calculation (2, 4 , and 6 months). (10 marks) 1. Does the January forecast for Year 5 change if you change the number of periods to calculate the moving average? Enumerate the three different forecasts (using the three PROBLEM 1: (20 marks) The Fowler Martial Arts Academy trains young boys and girls in selfdefence. Joan Fowler, the academy owner, notes that monthly revenue is higher when school is in session but quite low when school is out (because many children are away on vacation or at summer camp). She has researched revenues for the past four years and obtained the information shown MonthlyRevenue Excel file from Brightspace in the "Data Files" folder. Answer the following questions based on the data provided in the file. a) Forecast the revenue for January Year 5 using the moving averages method. Consider three different numbers of periods for the moving average calculation (2, 4, and 6 months). (10 marks) 1. Does the January forecast for Year 5 change if you change the number of periods to calculate the moving average? Enumerate the three different forecasts (using the three periods 2,4, and 6 months) for January Year 5. (3 marks) 2. Plot the actual and predicted revenue for all three moving average calculations (You will have four plots in total on one chart - the actual data and the three forecasts). (3 marks) 3. Which model is the best (the one Dased on a 2,4 , or 6 -month moving average)? Why? Show your calculations. ( 4 marks) ITIS 1P97 Winter 2023 b) Using multiple regression analysis, forecast revenues for each month of Year 5 ( 8 marks) c) Which model is better - the one based on the moving average or the regression analysis? Why? Show your calculations. Note: compare the best moving average model in question 3 above with the regression model in question b. (2 marks) famous for beaches, pools, and patios: sling, Adirondack, and hammocks. The unit profit for these products is $40,$70, and $80, respectively. Each type of chair requires cutting, assembling, and finishing. The owner is retired and willing to work six hours per day for five days per week, so he has 120 hours available each month. He does not want to spend more than 50 hours each month on any one activity (that is, cutting, assembling, and finishing). The retailer he works with enures that all products he makes can quickly be sold. Sling chairs are made up of ten wood pieces for the frame and one piece of cloth. The actual cutting of the wood takes 30 minutes. Assembling includes sewing the fabric and the attachment of rivets, screws, fabric, and dowel rods, which takes 45 minutes. The finishing stage involves sanding, staining, and varnishing the various parts, which takes one hour. Adirondack chairs take one and a half an hour to cut and one hour to assemble, and finishing takes one hour. For hammocks, cutting takes 0.4 hours, assembly takes 1.5 hours, and finishing takes one hour. How many of each type of chair should he produce each month to maximize profit? a) Develop a linear optimization model for the problem on a spreadsheet in Excel. (10 marks) b) What is the optimal solution for the problem? ( 2 marks) c) Create a Sensitivity Report and answer the following questions: (i) Suppose we forced sling chairs to be in the solution; what would happen to the objective function's value? ( 2 marks) (ii) What are the maximum and minimum values of the objective function coefficients for sling chairs, Adirondack chairs, and hammocks for which the current optimal solution is valid? (2 marks) (iii) What do the shadow prices for the "Assembling Hours Used" and "Total Hours Used" constraints mean? (2 marks) (iv) What are the maximum and minimum values for the cutting, assembly, and finishing hours used constraints for which the current constraint value does not change? (2 marks) - Use Microsoft Excel ONLY to solve the problems. If an answer requires written arguments or explanations, you must do it in the Excel file. - You are required to submit ONE Excel file only. Answer questions 1a,1b and 1C, and problem 2 on different worksheets. Altogether you will have four worksheets in one workbook, including the cover sheet (next bullet point). - The file you submit must have a cover sheet that shows your first and last name, student number, and course and section numbers. Use the first worksheet in the Excel workbook you will submit as a coversheet. - The file must be uploaded to Brightspace using the Assignment2 link by the due date to get a credit. Email submission is neither accepted nor credited. - The file you subInit MUST be an Excel file and openable in the Windows environment. - Verify that your assignment file is correctly submitted through the course website. A 10% deduction per day will apply to late submissions for the first three days only; wrong files will automatically receive a grade of zero. Assignment Questions: PROBLEM 1: (20 marks) The Fowler Martial Arts Academy trains young boys and girls in selfdefence. Joan Fowler, the academy owner, notes that monthly revenue is higher when school is in session but quite low when school is out (because many children are away on vacation or at summer camp). She has researched revenues for the past four years and obtained the information shown MonthlyRevenue Excel file from Brightspace in the "Data Files" folder. Answer the following questions based on the data provided in the file. a) Forecast the revenue for January Year 5 using the moving averages method. Consider three different numbers of periods for the moving average calculation (2, 4 , and 6 months). (10 marks) 1. Does the January forecast for Year 5 change if you change the number of periods to calculate the moving average? Enumerate the three different forecasts (using the three PROBLEM 1: (20 marks) The Fowler Martial Arts Academy trains young boys and girls in selfdefence. Joan Fowler, the academy owner, notes that monthly revenue is higher when school is in session but quite low when school is out (because many children are away on vacation or at summer camp). She has researched revenues for the past four years and obtained the information shown MonthlyRevenue Excel file from Brightspace in the "Data Files" folder. Answer the following questions based on the data provided in the file. a) Forecast the revenue for January Year 5 using the moving averages method. Consider three different numbers of periods for the moving average calculation (2, 4, and 6 months). (10 marks) 1. Does the January forecast for Year 5 change if you change the number of periods to calculate the moving average? Enumerate the three different forecasts (using the three periods 2,4, and 6 months) for January Year 5. (3 marks) 2. Plot the actual and predicted revenue for all three moving average calculations (You will have four plots in total on one chart - the actual data and the three forecasts). (3 marks) 3. Which model is the best (the one Dased on a 2,4 , or 6 -month moving average)? Why? Show your calculations. ( 4 marks) ITIS 1P97 Winter 2023 b) Using multiple regression analysis, forecast revenues for each month of Year 5 ( 8 marks) c) Which model is better - the one based on the moving average or the regression analysis? Why? Show your calculations. Note: compare the best moving average model in question 3 above with the regression model in question b. (2 marks) famous for beaches, pools, and patios: sling, Adirondack, and hammocks. The unit profit for these products is $40,$70, and $80, respectively. Each type of chair requires cutting, assembling, and finishing. The owner is retired and willing to work six hours per day for five days per week, so he has 120 hours available each month. He does not want to spend more than 50 hours each month on any one activity (that is, cutting, assembling, and finishing). The retailer he works with enures that all products he makes can quickly be sold. Sling chairs are made up of ten wood pieces for the frame and one piece of cloth. The actual cutting of the wood takes 30 minutes. Assembling includes sewing the fabric and the attachment of rivets, screws, fabric, and dowel rods, which takes 45 minutes. The finishing stage involves sanding, staining, and varnishing the various parts, which takes one hour. Adirondack chairs take one and a half an hour to cut and one hour to assemble, and finishing takes one hour. For hammocks, cutting takes 0.4 hours, assembly takes 1.5 hours, and finishing takes one hour. How many of each type of chair should he produce each month to maximize profit? a) Develop a linear optimization model for the problem on a spreadsheet in Excel. (10 marks) b) What is the optimal solution for the problem? ( 2 marks) c) Create a Sensitivity Report and answer the following questions: (i) Suppose we forced sling chairs to be in the solution; what would happen to the objective function's value? ( 2 marks) (ii) What are the maximum and minimum values of the objective function coefficients for sling chairs, Adirondack chairs, and hammocks for which the current optimal solution is valid? (2 marks) (iii) What do the shadow prices for the "Assembling Hours Used" and "Total Hours Used" constraints mean? (2 marks) (iv) What are the maximum and minimum values for the cutting, assembly, and finishing hours used constraints for which the current constraint value does not change? (2 marks)

Answer & Explanation Solved by verified expert

See Answer