Please explain in detail step by step and show the formulas used.

Q5. Cheung Enterprises is a small but well-established mechanical engineering company that manufactures industrial goods. Along with several other companies, it produces specialized replacement parts for short haul commercial aircraft that fly regional routes, servicing point to point destinations. The company incurs both direct and indirect costs when making its parts. Direct costs include materials and labor, which are tracked through the company's job costing system. Indirect manufacturing costs are for depreciation on equipment, various lubricants and ball bearings used to maintain the machinery, outlays for utilities such as electricity, heat and water, insurance for the facility, as well as property taxes. As a highly automated plant, indirect costs make up a large portion of the total costs of production. Therefore, determining the best way to allocate these costs is critical for making effective pricing decisions for replacement parts in this competitive business environment. Because of the specialized nature of the business, manufacturing occurs in small quantities, or "batches." Each batch is part in a production run that is completed on one of three assembly lines, which all use different equipment. Every week a different assembly line, and production equipment, is used. This is done to allow preventative maintenance on the unused machinery, as required by the manufacturer's warranty. (Note that wear and tear is based on the period of usage - i. e., weeks - and not the number of units produced.) Required You are the new financial analyst at Cheung Enterprises. You have gathered data (See below) that you believe may be related to indirect costs. You want to develop a predictive model to allocate overhead and has collected weekly data for almost 2 years relating to the company's indirect costs and what you believe are the relevant cost drivers, including machine hours, labor hours, and production runs. Please prepare a predictive model. 1 Q5 Data File 2 Number of Number of 3 Week Machine Hours Labour Hours 4 1 1745 33 5 2 1485 31 6 3 1695 29 7 4 1557 24 8 5 1705 37 9 6 1988 10 7 1925 11 8 1241 12 9 1566 13 10 1721 14 11 1830 12 1579 13 1529 14 1377 18 15 1696 19 16 1875 20 17 1979 21 18 1301 22 19 1395 23 20 2005 24 21 1582 25 22 1652 26 23 1710 27 24 1623 28 25 1680 29 26 1315 30 27 1637 31 28 1796 32 29 1792 33 30 1698 34 31 1321 35 32 1742 36 33 1887 15 16 17 67 32 43 31 49 23 39 39 51 52 39 43 36 46 48 31 40 34 34 44 29 36 59 28 34 35 38 30 43 Number of Production Runs 9,739 7,266 9,311 8,550 12,641 10,621 11,200 6,231 10,845 7,586 11,228 9,499 7,799 8,539 6,749 14,639 12,585 6,280 6,038 10,451 12,607 10,312 13,218 9,333 8,348 7,235 8,061 7,124 9,935 13,061 5,525 10,029 8,296 Number of Overhead Machinery Dollars Manufacturer 126,605 Parker Hannifin 108,992 Mitsubishi 130,355 Siemens 102,600 Parker Hannifin 139,046 Mitsubishi 138,078 Siemens 134,400 Parker Hannifin 93,467 Mitsubishi 119,292 Siemens 113,786 Parker Hannifin 112,275 Mitsubishi 132,987 Siemens 93,588 Parker Hannifin 85,390 Mitsubishi 101,238 Siemens 161,024 Parker Hannifin 125,847 Mitsubishi 87,918 Siemens 90,573 Parker Hannifin 135,859 Mitsubishi 126,067 Siemens 113,434 Parker Hannifin 145,395 Mitsubishi 130,668 Siemens 100,173 Parker Hannifin 86,822 Mitsubishi 104,796 Siemens 106,856 Parker Hannifin 149,023 Mitsubishi 130,612 Siemens 82,879 Parker Hannifin 140,403 Mitsubishi 116,142 Siemens n = 100 Number of Overhead Dollars2 126605 108992 130355 102600 139046 138078 134400 93467 119292 113786 112275 132987 93588 85390 101238 161024 125847 87918 90573 135859 126067 113434 145395 130668 100173 86822 104796 106856 149023 130612 82879 140403 116142 37 38 39 9524485 40 43 41 38 39 40 41 42 46 43 47 44 48 45 49 46 50 47 51 48 52 49 53 50 54 51 55 52 56 53 57 54 58 55 59 56 60 57 61 58 59 60 64 61 65 62 63 64 68 65 69 66 70 67 71 68 72 69 98823228689 66 34 35 36 37 +non co 67 1932 1616 1593 1698 1445 1649 1516 1659 1933 1873 1209 1525 1675 1781 1536 1488 1341 1650 1824 1925 1267 1358 1950 1539 1608 1664 1580 1634 1281 1593 1747 1743 1652 1287 1695 1835 37 8,096 121,444 Parker Hannifin 32 8,939 116,202 Mitsubishi 55 8,038 112,532 Siemens 33 12,184 146,208 Parker Hannifin 31 7,315 87,782 Mitsubishi 29 9,669 116,033 Siemens 25 8,468 127,020 Parker Hannifin 37 9,593 95,926 Mitsubishi 32 7,726 115,887 Siemens 43 10,360 155,401 Parker Hannifin 31 8,182 89,999 Mitsubishi 49 6,542 91,584 Siemens 24 10,160 121,920 Parker Hannifin 39 9,525 142,870 Mitsubishi 39 12,717 127,172 Siemens 51 9,524 114,284 Parker Hannifin 52 6,368 95,519 Mitsubishi 39 11,989 119,888 Siemens 43 12,313 147,750 Parker Hannifin 36 14,865 163,520 Mitsubishi 46 8,259 90,854 Siemens 48 9,223 101,454 Parker Hannifin 31 14,200 142,000 Mitsubishi 40 10,793 118,723 Siemens 34 11,923 131,148 Parker Hannifin 34 6,798 101,967 Mitsubishi 44 8,910 98,013 Siemens 29 8,763 113,919 Parker Hannifin 36 8,044 88,480 Mitsubishi 60 6,716 100,735 Siemens 28 12,347 135,821 Parker Hannifin 34 10,286 123,433 Mitsubishi 35 10,742 118,160 Siemens 38 7,500 97,500 Parker Hannifin 30 9,242 129,382 Mitsubishi 43 9,739 116,864 Siemens 121444 116202 112532 146208 87782 116033 127020 95926 115887 155401 89999 91584 121920 142870 127172 114284 95519 119888 147750 163520 90854 101454 142000 118723 131148 101967 98013 113919 88480 100735 135821 123433 118160 97500 129382 116864 73 70 74 71 75 72 76 73 77 74 78 75 79 76 80 77 81 78 82 79 83 80 84 81 85 82 86 83 87 84 88 85 89 86 90 87 91 88 92 89 93 90 94 91 95 92 96 93 97 94 98 95 99 96 100 97 101 98 102 99 103 100 1880 1573 1550 1698 1445 1649 1516 1659 1933 1873 1209 1525 1675 1781 1536 1488 1341 1650 1824 1925 1267 1358 1950 1539 1608 1664 1580 1634 1281 1593 1747 37 10,912 32 6,838 56 10,880 33 10,422 31 7,440 29 11,981 25 11,844 37 11,714 32 13,501 43 9,363 31 5,650 49 8,784 24 8.777 39 10,719 39 8,130 51 8,103 52 8,582 39 8,361 43 8,161 36 10,901 46 6,075 48 5,451 31 11,360 40 6,693 34 12,506 34 9,927 44 8,502 29 8,332 36 9,202 60 6,958 28 7,894 163,680 Parker Hannifin 95,735 Mitsubishi 119,680 Siemens 135,486 Parker Hannifin 96,723 Mitsubishi 119,807 Siemens 118,437 Parker Hannifin 140,564 Mitsubishi 162,017 Siemens 121,713 Parker Hannifin 73,455 Mitsubishi 105,408 Siemens 122,880 Parker Hannifin 150,065 Mitsubishi 121,946 Siemens 113,443 Parker Hannifin 85,818 Mitsubishi 117,056 Siemens 122,422 Parker Hannifin 163,520 Mitsubishi 78,973 Siemens 81,769 Parker Hannifin 124,960 Mitsubishi 100,390 Siemens 137,568 Parker Hannifin 119,120 Mitsubishi 93,517 Siemens 108,316 Parker Hannifin 101,221 Mitsubishi 104,365 Siemens 102,620 Parker Hannifin 163680 95735 119680 135486 96723 119807 118437 140564 162017 121713 73455 105408 122880 150065 121946 113443 85818 117056 122422 163520 78973 81769 124960 100390 137568 119120 93517 108316 101221 104365 102620 Q5. Cheung Enterprises is a small but well-established mechanical engineering company that manufactures industrial goods. Along with several other companies, it produces specialized replacement parts for short haul commercial aircraft that fly regional routes, servicing point to point destinations. The company incurs both direct and indirect costs when making its parts. Direct costs include materials and labor, which are tracked through the company's job costing system. Indirect manufacturing costs are for depreciation on equipment, various lubricants and ball bearings used to maintain the machinery, outlays for utilities such as electricity, heat and water, insurance for the facility, as well as property taxes. As a highly automated plant, indirect costs make up a large portion of the total costs of production. Therefore, determining the best way to allocate these costs is critical for making effective pricing decisions for replacement parts in this competitive business environment. Because of the specialized nature of the business, manufacturing occurs in small quantities, or "batches." Each batch is part in a production run that is completed on one of three assembly lines, which all use different equipment. Every week a different assembly line, and production equipment, is used. This is done to allow preventative maintenance on the unused machinery, as required by the manufacturer's warranty. (Note that wear and tear is based on the period of usage - i. e., weeks - and not the number of units produced.) Required You are the new financial analyst at Cheung Enterprises. You have gathered data (See below) that you believe may be related to indirect costs. You want to develop a predictive model to allocate overhead and has collected weekly data for almost 2 years relating to the company's indirect costs and what you believe are the relevant cost drivers, including machine hours, labor hours, and production runs. Please prepare a predictive model. 1 Q5 Data File 2 Number of Number of 3 Week Machine Hours Labour Hours 4 1 1745 33 5 2 1485 31 6 3 1695 29 7 4 1557 24 8 5 1705 37 9 6 1988 10 7 1925 11 8 1241 12 9 1566 13 10 1721 14 11 1830 12 1579 13 1529 14 1377 18 15 1696 19 16 1875 20 17 1979 21 18 1301 22 19 1395 23 20 2005 24 21 1582 25 22 1652 26 23 1710 27 24 1623 28 25 1680 29 26 1315 30 27 1637 31 28 1796 32 29 1792 33 30 1698 34 31 1321 35 32 1742 36 33 1887 15 16 17 67 32 43 31 49 23 39 39 51 52 39 43 36 46 48 31 40 34 34 44 29 36 59 28 34 35 38 30 43 Number of Production Runs 9,739 7,266 9,311 8,550 12,641 10,621 11,200 6,231 10,845 7,586 11,228 9,499 7,799 8,539 6,749 14,639 12,585 6,280 6,038 10,451 12,607 10,312 13,218 9,333 8,348 7,235 8,061 7,124 9,935 13,061 5,525 10,029 8,296 Number of Overhead Machinery Dollars Manufacturer 126,605 Parker Hannifin 108,992 Mitsubishi 130,355 Siemens 102,600 Parker Hannifin 139,046 Mitsubishi 138,078 Siemens 134,400 Parker Hannifin 93,467 Mitsubishi 119,292 Siemens 113,786 Parker Hannifin 112,275 Mitsubishi 132,987 Siemens 93,588 Parker Hannifin 85,390 Mitsubishi 101,238 Siemens 161,024 Parker Hannifin 125,847 Mitsubishi 87,918 Siemens 90,573 Parker Hannifin 135,859 Mitsubishi 126,067 Siemens 113,434 Parker Hannifin 145,395 Mitsubishi 130,668 Siemens 100,173 Parker Hannifin 86,822 Mitsubishi 104,796 Siemens 106,856 Parker Hannifin 149,023 Mitsubishi 130,612 Siemens 82,879 Parker Hannifin 140,403 Mitsubishi 116,142 Siemens n = 100 Number of Overhead Dollars2 126605 108992 130355 102600 139046 138078 134400 93467 119292 113786 112275 132987 93588 85390 101238 161024 125847 87918 90573 135859 126067 113434 145395 130668 100173 86822 104796 106856 149023 130612 82879 140403 116142 37 38 39 9524485 40 43 41 38 39 40 41 42 46 43 47 44 48 45 49 46 50 47 51 48 52 49 53 50 54 51 55 52 56 53 57 54 58 55 59 56 60 57 61 58 59 60 64 61 65 62 63 64 68 65 69 66 70 67 71 68 72 69 98823228689 66 34 35 36 37 +non co 67 1932 1616 1593 1698 1445 1649 1516 1659 1933 1873 1209 1525 1675 1781 1536 1488 1341 1650 1824 1925 1267 1358 1950 1539 1608 1664 1580 1634 1281 1593 1747 1743 1652 1287 1695 1835 37 8,096 121,444 Parker Hannifin 32 8,939 116,202 Mitsubishi 55 8,038 112,532 Siemens 33 12,184 146,208 Parker Hannifin 31 7,315 87,782 Mitsubishi 29 9,669 116,033 Siemens 25 8,468 127,020 Parker Hannifin 37 9,593 95,926 Mitsubishi 32 7,726 115,887 Siemens 43 10,360 155,401 Parker Hannifin 31 8,182 89,999 Mitsubishi 49 6,542 91,584 Siemens 24 10,160 121,920 Parker Hannifin 39 9,525 142,870 Mitsubishi 39 12,717 127,172 Siemens 51 9,524 114,284 Parker Hannifin 52 6,368 95,519 Mitsubishi 39 11,989 119,888 Siemens 43 12,313 147,750 Parker Hannifin 36 14,865 163,520 Mitsubishi 46 8,259 90,854 Siemens 48 9,223 101,454 Parker Hannifin 31 14,200 142,000 Mitsubishi 40 10,793 118,723 Siemens 34 11,923 131,148 Parker Hannifin 34 6,798 101,967 Mitsubishi 44 8,910 98,013 Siemens 29 8,763 113,919 Parker Hannifin 36 8,044 88,480 Mitsubishi 60 6,716 100,735 Siemens 28 12,347 135,821 Parker Hannifin 34 10,286 123,433 Mitsubishi 35 10,742 118,160 Siemens 38 7,500 97,500 Parker Hannifin 30 9,242 129,382 Mitsubishi 43 9,739 116,864 Siemens 121444 116202 112532 146208 87782 116033 127020 95926 115887 155401 89999 91584 121920 142870 127172 114284 95519 119888 147750 163520 90854 101454 142000 118723 131148 101967 98013 113919 88480 100735 135821 123433 118160 97500 129382 116864 73 70 74 71 75 72 76 73 77 74 78 75 79 76 80 77 81 78 82 79 83 80 84 81 85 82 86 83 87 84 88 85 89 86 90 87 91 88 92 89 93 90 94 91 95 92 96 93 97 94 98 95 99 96 100 97 101 98 102 99 103 100 1880 1573 1550 1698 1445 1649 1516 1659 1933 1873 1209 1525 1675 1781 1536 1488 1341 1650 1824 1925 1267 1358 1950 1539 1608 1664 1580 1634 1281 1593 1747 37 10,912 32 6,838 56 10,880 33 10,422 31 7,440 29 11,981 25 11,844 37 11,714 32 13,501 43 9,363 31 5,650 49 8,784 24 8.777 39 10,719 39 8,130 51 8,103 52 8,582 39 8,361 43 8,161 36 10,901 46 6,075 48 5,451 31 11,360 40 6,693 34 12,506 34 9,927 44 8,502 29 8,332 36 9,202 60 6,958 28 7,894 163,680 Parker Hannifin 95,735 Mitsubishi 119,680 Siemens 135,486 Parker Hannifin 96,723 Mitsubishi 119,807 Siemens 118,437 Parker Hannifin 140,564 Mitsubishi 162,017 Siemens 121,713 Parker Hannifin 73,455 Mitsubishi 105,408 Siemens 122,880 Parker Hannifin 150,065 Mitsubishi 121,946 Siemens 113,443 Parker Hannifin 85,818 Mitsubishi 117,056 Siemens 122,422 Parker Hannifin 163,520 Mitsubishi 78,973 Siemens 81,769 Parker Hannifin 124,960 Mitsubishi 100,390 Siemens 137,568 Parker Hannifin 119,120 Mitsubishi 93,517 Siemens 108,316 Parker Hannifin 101,221 Mitsubishi 104,365 Siemens 102,620 Parker Hannifin 163680 95735 119680 135486 96723 119807 118437 140564 162017 121713 73455 105408 122880 150065 121946 113443 85818 117056 122422 163520 78973 81769 124960 100390 137568 119120 93517 108316 101221 104365 102620