Use the average demand scenario to evaluate the sensitivity of the project's NPV with respect to sale price of the electric motor and the cost of the controller (hint: increases and decreases of 1%, 2%, and 3% in sales price and engine price).
Based on the scenario and sensitivity analysis you performed above, comment on the overall riskiness of the project.
Would you recommend that Electrics accept or reject the project?
What is the basis for your recommendation?
Based on using the information from the spreadsheets
AutoSave . Off) CTX-13 Forecast.xIsx - Excel O Search A Abbo, Enrique G AE X File Home Insert Draw Page Layout Formulas Data Review View Developer Help ACROBAT Share Comments AA Insert Calibri 12 ab General 2" Ap O LA x Delete Paste BIUV I V $ ~ % " .00 Conditional Format as Cell Sort & Find & Ideas Sensitivity Formatting " Table Styles Format Filter ~ Select v Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity K15 X V A B C D E F G H K L M Year 0 Depreciated Asset MACRS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 $17,000,000 Dep. % 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% Sales Growth 10% assumption Dep Amt $2,429,300.00 $4,163,300.00 $2,973,300.00 $2,123,300.00 $1,518,100.00 $1,516,400.00 $1,518,100.00 $758,200.00 8 Average Demand Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 9 Price per electric motor conversion kit $ 9,200.00 $ 10,120.00 $ 11,132.00 $ 12,245.20 $ 13,469.72 $ 14,816.69 $ 16,298.36 $ 17,928.20 10 Units sold per year 40,500 44,550 49,005 53,906 59,296 65,226 71,748 78,923 11 Net Sales $ 372,600,000.00 $ 450,846,000.00 $ 545,523, 5,523,660.00 $ 660,083,628.60 $ 798,701,190.61 $ 966,428,440.63 $ 1,169,378,413.17 $ 1,414,947,879.93 12 Labor cost per electric motor S 4,250.00 $ 4,675.00 $ 5,142.50 $ 5,656.75 $ 6,222.43 $ 6,844.67 $ 7,529.13 $ 8,282.05 13 Total Labor $ 172,125,000.00 $ 208,271,250.00 $ 252,008,212.50 $ 304,929,937.13 $ 368,965,223.92 $ 446,447,920.94 $ 540,201,984.34 $ 653,644,401.06 14 Parts S 2,500.00 $ 2,750.00 $ 3,025.00 $ 3,327.50 3,660.25 4,026.28 4,428.90 $ 4,871.79 15 Total Parts $ 101,250,000.00 $ 122,512,500.00 $ 148,240,125.00 $ 179,370,551.25 $ 217,038,367.01 $ 262,616,424.09 $ 317,765,873.14 384,496,706.50 16 Purchase Price per Kit 1,280.00 1,280.00 1,280.00 1,280.00 $ 1,280.00 $ 1,280.00 1,280.00 1,280.00 17 Totl Purchase Price 51,840,000.00 $ 57,024,000.00 $ 62,726,400.00 68,999,040.00 $ 75,898,944.00 $ 83,488,838.40 $ 91,837,722.24 $ 101,021,494.46 18 Selling General & Administrative 9,500,000.00 $ 10,450,000.00 $ 11,495,000.00 $ 12,644,500.00 $ 13,908,950.00 $ 15,299,845.00 16,829,829.50 $ 18,512,812.45 19 Warranty 3,645,000.00 $ 4,009,500.00 4,410,450.00 $ 4,851,495.00 $ 5,336,644.50 20 Depreciation 2,429,300.00 $ 4,163,300.00 2,973,300.00 $ 2,123,300.00 1,518,100.00 $ 1,516,400.00 $ 1,518,100.00 $ 758,200.00 21 EBIT $ 31,810,700.00 $ 44,415,450.00 $ 63,670,172.50 $ 87,164,805.23 $ 116,034,961.17 $ 157,059,012.20 $ 201,224,903.94 256,514,265.46 22 Taxes (40%) $ 12,724,280.00 $ 13,324,635.00 $ 19,101,051.75 $ 26,149,441.57 $ 34,810,488.35 $ 47,117,703.66 60,367,471.18 $ 76,954,279.64 23 Operating Income $ 19,086,420.00 $ 31,090,815.00 $ 44,569,120.75 $ 61,015,363.66 $ 81,224,472.82 $ 109,941,308.54 $ 140,857,432.76 $ 179,559,985.82 24 25 Operating Cash (Op Inc + Dep) $ 21,515,720.00 $ 35,254,115.00 $ 47,542,420.75 $ 63,138,663.66 $ 82,742,572.82 $ 111,457,708.54 $ 142,375,532.76 $ 180,318,185.82 26 NWC 10% annual sales S 1,908,642.00 $ 3,109,081.50 $ 4,456,912.08 $ 6,101,536.37 $ 8,122,447.28 $ 10,994,130.85 14,085,743.28 $ 17,955,998.58 27 Yearly Cash Flow $ 19,607,078.00 $ 32,145,033.50 $ 43,085,508.68 $ 57,037,127.29 $ 74,620,125.54 $ 100,463,577.69 $ 128,289,789.48 $ 162,362,187.24 28 Dividends 9,803,539.00 $ 16,072,516.75 $ 21,542,754.34 $ 28,518,563.65 $ 37,310,062.77 $ 50,231,788.84 $ 64,144,894.74 $ 81,181,093.62 29 Retained Earnings 9,803,539.00 $ 16,072,516.75 $ 21,542,754.34 $ 28,518,563.65 $ 37,310,062.77 $ 50,231,788.84 64,144,894.74 $ 81,181,093.62 Weak Demand Sheet 1 Average Demand Strong Demand Question 6 +AutoSave . Off ) CTX-13 Forecast.xIsx - Excel O Search A Abbo, Enrique G AE X File Home Insert Draw Page Layout Formulas Data Review View Developer Help ACROBAT Share Comments 12 ~ A" A Insert Calibri ab General x Delete Paste BIUV $ ~ % 9 00 70 Conditional Format as Cell Sort & Find & Ideas Sensitivity Formatting Table Styles Format Filter > Select v Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity A9 X Y fix Price per electric motor conversion kit B D G H K M N P Q 1 CTX - 13 8 Year Forecast Year 0 Depreciated Asset MACRS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 $17,000,000 Dep. % 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% JOU AWN Sales Growth 10% assumption Dep Amt $2,429,300.00 $4,163,300.00 $2,973,300.00 $2,123,300.00 $1,518,100.00 $1,516,400.00 $1,518,100.00 $758,200.00 8 Strong Demand Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 9 Price per electric motor conversion kit 9,250.00 $ 10,175.00 S 11,192.50 $ 12,311.75 $ 13,542.93 S 14,897.22 $ 16,386.94 $ 18,025.63 10 Units sold per year 10,750 14,825 49,308 54,238 59,662 65,628 72,191 79,410 11 Net Sales $ 376,937,500.00 $ 456,094,375.00 $ 551,874,193.75 $ 667,767,774.44 $ 807,999,007.07 $ 977,678,798.55 $ 1,182,991,346.25 $ 1,431,419,528.96 12 Labor cost per electric motor 1,250.00 $ 1,675.00 $ 5,142.50 $ 5,656.75 $ 5,222.43 $ 6,844.67 $ 7,529.13 $ 8,282.05 13 Total Labor $ 173,187,500.00 $ 209,556,875.00 $ 253,563,818.75 $ 306,812,220.69 $ 371,242,787.03 $ 449,203,772.31 $ 543,536,564.49 $ 657,679,243.04 14 Parts 2,500.00 $ 2,750.00 $ 3,025.00 $ 3,327.50 $ 3,660.25 $ 4,026.28 $ 4,428.90 $ 4,871.79 15 Total Parts $ 101,875,000.00 $ 123,268,750.00 $ 149,155,187.50 $ 180,477,776.88 $ 218,378,110.02 $ 264,237,513.12 $ 319,727,390.88 $ 386,870,142.96 16 Purchase Price per Kit 1,280.00 1,280.00 $ 1,280.00 1,280.00 1,280.00 1,280.00 1,280.00 $ 1,280.00 17 Totl Purchase Price 52,160,000.00 57,376,000.00 $ 63,113,600.00 $ 69,424,960.00 76,367,456.00 84,004,201.60 $ 92,404,621.76 $ 101,645,083.94 18 Selling General & Administrative 9,500,000.00 $ 10,450,000.00 $ 11,495,000.00 $ 12,644,500.00 $ 13,908,950.00 $ 15,299,845.00 $ 16,829,829.50 $ 18,512,812.45 19 Warranty 3,667,500.00 $ 4,034,250.00 $ 4,437,675.00 $ 4,881,442.50 $ 5,369,586.75 20 Depreciation 2,429,300.00 $ 4,163,300.00 $ 2,973,300.00 $ 2,123,300.00 $ 1,518,100.00 $ 1,516,400.00 $ 1,518,100.00 S 758,200.00 21 EBIT $ 34,118,200.00 $ 47,245,200.00 $ 67,135,612.50 $ 91,403,574.38 $ 121,214,017.27 $ 163,417,066.52 $ 208,974,839.62 $ 265,954,046.58 22 Taxes (40%) $ 13,647,280.00 $ 14,173,560.00 $ 20,140,683.75 $ 27,421,072.31 $ 36,364,205.18 $ 49,025,119.96 62,692,451.89 79,786,213.97 23 Operating Income 20,470,920.00 $ 33,071,640.00 $ 46,994,928.75 $ 63,982,502.06 $ 84,849,812.09 $ 114,391,946.57 $ 146,282,387.73 $ 186,167,832.60 24 25 Operating Cash (Op Inc + Dep) 22,900,220.00 $ 37,234,940.00 $ 49,968,228.75 $ 66,105,802.06 $ 86,367,912.09 $ 115,908,346.57 $ 147,800,487.73 $ 186,926,032.60 26 NWC 10% annual sales $ 2,047,092.00 $ 3,307,164.00 $ 4,699,492.88 $ 6,398,250.21 $ $ 8,484,981.21 $ 11,439,194.66 $ 14,628,238.77 $ 18,616,783.26 27 Yearly Cash Flow $ 20,853,128.00 $ 33,927,776.00 $ 45,268,735.88 $ 59,707,551.86 $ 77,882,930.88 $ 104,469,151.91 $ 133,172,248.96 $ 168,309,249.34 28 Dividends $ 10,426,564.00 $ 16,963,888.00 $ 22,634,367.94 $ 29,853,775.93 $ 38,941,465.44 $ 52,234,575.95 66,586,124.48 $ 84,154,624.67 29 Retained Earnings $ 10,426,564.00 $ 16,963,888.00 $ 22,634,367.94 $ 29,853,775.93 $ 38,941,465.44 $ 52,234,575.95 $ 66,586,124.48 $ 84,154,624.67 30 31 32 33 Weak Demand Sheet 1 Average Demand Strong Demand Question 6 +AutoSave . Off CTX-13 Forecast.xIsx - Excel Search A Abbo, Enrique G AE X File Home Insert Draw Page Layout Formulas Data Review View Developer Help ACROBAT Share Comments 12 ~ A" A Insert Calibri ab General E AP O x Delete Paste BIUV - DAY V $ ~ % 9 0828 Conditional Format as Cell Sort & Find & Ideas Sensitivity Formatting " Table S Styles Format Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity 030 X V fx B D E F H K M N P Q MACRS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Dep. % 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% Dep Amt $2,429,300.00 $4,163,300.00 $2,973,300.00 $2,123,300.00 $1,518,100.00 $1,516,400.00 ##HHHHHHHH### ########### 8 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 9 S ,100.00 $ 10,010.00 $ 11,011.00 $ 12,112.10 $ 13,323.31 $ 14,655.64 $ 16,121.21 $ 17,733.33 10 40,000 44,000 48,400 53,240 58,564 64,420 70,862 77,949 11 ############## 12 S 4,250.00 $ 4,675.00 5,142.50 $ 5,656.75 $ 6,222.43 $ 6,844.67 $ 7,529.13 $ 8,282.05 13 ############## ############## ############## $ 533,532,824.04 $ 645,574,717.09 14 S 2,500.00 $ 2,750.00 $ 3,025.00 $ 3,327.50 $ 3,660.25 4,026.28 4,428.90 4,871.79 15 ############## ############## ############## ############## $ 313,842,837.67 $ 379,749,833.58 16 9 1,280.00 $ 1,280.00 $ 1,280.00 $ 1,280.00 $ 1,280.00 $ 1,280.00 1,280.00 1,280.00 17 $ 51,200,000.00 $ 56,320,000.00 $ 61,952,000.00 $ 68,147,200.00 $ 74,961,920.00 $ 82,458,112.00 $ 90,703,923.20 99,774,315.52 18 $ 9,500,000.00 $ 10,450,000.00 $ 11,495,000.00 $ 12,644,500.00 $ 13,908,950.00 $ 15,299,845.00 $ 16,829,829.50 $ 18,512,812.45 19 $ 3,600,000.00 $ 3,960,000.00 $ 4,356,000.00 $ 4,791,600.00 $ 5,270,760.00 20 $ 2,429,300.00 $ 4,163,300.00 $ 2,973,300.00 $ 2,123,300.00 $ 1,518,100.00 $ 1,516,400.00 $ 1,518,100.00 $ 758,200.00 21 $ 27,270,700.00 $ 38,846,700.00 $ 56,849,100.00 $ 78,820,134.00 ##########IIIIII# ##############|$ 185,960,414.71 $ 237,919,515.60 22 $ 10,908,280.00 $ 11,654,010.00 $ 17,054,730.00 $ 23,646,040.20 $ 31,751,285.44 $ 43,361,230.27 $ 55,788,124.41 $ 71,375,854.68 23 $ 16,362,420.00 $ 27,192,690.00 $ 39,794,370.00 $ 55,174,093.80 $ 74,086,332.70 ############## $ 130,172,290.30 $ 166,543,660.92 24 25 $ 18,791,720.00 $ 31,355,990.00 $ 42,767,670.00 $ 57,297,393.80 $ 75,604,432.70 ############## |$ 131,690,390.30 $ 167,301,860.92 26 $ 1,636,242.00 $ 2,719,269.00 $ 3,979,437.00 $ 5,517,409.38 $ 7,408,633.27 $ 10,117,620.40 $ 13,017,229.03 $ 16,654,366.09 27 $ 17,155,478.00 $ 28,636,721.00 $ 38,788,233.00 $ $ 51,779,984.42 $ 68,195,799.43 $ 92,574,983.58 $ 118,673,161.27 $ 150,647,494.83 28 $ 8,577,739.00 $ 14,318,360.50 $ 19,394,116.50 $ 25,889,992.21 $ 34,097,899.71 $ 46,287,491.79 59,336,580.63 $ 75,323,747.41 29 $ 8,577,739.00 $ 14,318,360.50 $ 19,394,116.50 $ 25,889,992.21 $ 34,097,899.71 $ 46,287,491.79 $ 59,336,580.63 $ 75,323,747.41 30 Weak Demand Sheet1 Average Demand Strong Demand Question 6 + - - + 70%AutoSave . Off MT-78 Forecast.xIsx - Excel O Search A Abbo, Enrique G AE X File Home Insert Draw Page Layout Formulas Data Review View Developer Help ACROBAT Share Comments Calibri 12 ~ A" A ab Insert Accounting Ex APO x Delete v Paste BIUV MyAv $ ~ % 9 08 20 Conditional Format as Cell Sort & Find & Ideas Sensitivity Formatting Table Styles Format Filter ~ Select v Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity F11 X V =F10*F9 B D F G H K M N 0 P Q 1 MT-78 8 Year Forecast W N Year 0 Depreciated Asset MACRS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 $17,000,000 Dep. % 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% Sales Growth 10% assumption Dep Amt $2,429,300.00 $4,163,300.00 $2,973,300.00 $2,123,300.00 $1,518,100.00 $1,516,400.00 $1,518,100.00 $758,200.00 8 Average Demand Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Price per electric motor conversion kit S 9,200.00 $ 10,120.00 S 11,132.00 $ 12,245.20 $ 13,469.72 $ 14,816.69 $ 16,298.36 $ 17,928.20 10 Units sold per year 40,500 44,550 49,005 53,906 59,296 65,226 71,748 78,923 11 Net Sales $ 372,600,000.00 $ 450,846,000.00 $ 545,523,660.00 $ 660,083 1 60 $ 798,701,190.61 | $ 966,428,440.63 $ 1,169,378,413.17 $ 1,414,947,879.93 12 Labor cost per electric motor 4,250.00 $ 4,675.00 $ 5,142.50 $ 5,090.75 $ 6,222.43 $ 6,844.67 7,529.13 $ 8,282.05 13 Total Labor $ 172,125,000.00 $ 208,271,250.00 $ 252,008,212.50 $ 304,929,937.13 $ 368,965,223.92 $ 446,447,920.94 540,201,984.34 $ 653,644,401.06 14 Parts 2,500.00 2,750.00 3,025.00 3,327.50 3,660.25 4,026.28 ,428.90 $ 4,871.79 15 Total Parts 101,250,000.00 $ 122,512,500.00 $ 148,240,125.00 $ 179,370,551.25 $ 217,038,367.01 $ 262,616,424.09 317,765,873.14 $ 384,496,706.50 16 Purchase Price per Kit 1,260.00 $ 1,260.00 $ 1,260.00 $ 1,260.00 1,260.00 1,260.00 1,260.00 $ 1,260.00 17 Totl Purchase Price 51,030,000.00 $ 56,133,000.00 $ 61,746,300.00 $ 67,920,930.00 74,713,023.00 $ 82,184,325.30 90,402,757.83 $ 99,443,033.61 18 Selling General & Administrative 9,500,000.00 10,450,000.00 $ 11,495,000.00 $ 12,644,500.00 $ 13,908,950.00 $ 15,299,845.00 $ 16,829,829.50 $ 18,512,812.45 19 Warranty 4,050,000.00 $ 4,455,000.00 $ 4,900,500.00 $ 5,390,550.00 $ 5,929,605.00 20 Depreciation 2,429,300.00 4,163,300.00 $ 2,973,300.00 2,123,300.00 $ 1,518,100.00 $ 1,516,400.00 $ 1,518,100.00 $ 758,200.00 21 EBIT $ 32,215,700.00 $ 44,860,950.00 $ 64,160,222.50 $ 87,703,860.23 $ 116,627,921.67 $ 158,363,525.30 $ 202,659,868.35 $ 258,092,726.31 22 Taxes (40%) $ 12,886,280.00 $ 13,458,285.00 $ 19,248,066.75 $ 26,311,158.07 $ 34,988,376.50 $ 47,509,057.59 $ 60,797,960.51 $ 77,427,817.89 23 Operating Income $ 19,329,420.00 $ 31,402,665.00 $ 44,912,155.75 $ 61,392,702.16 $ 81,639,545.17 $ 110,854,467.71 $ 141,861,907.85 $ 180,664,908.42 24 25 Operating Cash (Op Inc + Dep) $ 21,758,720.00 $ 35,565,965.00 $ 47,885,455.75 $ 63,516,002.16 $ 83,157,645.17 $ 112,370,867.71 $ 143,380,007.85 $ 181,423,108.42 26 NWC 10% annual sales 1,932,942.00 $ 3,140,266.50 $ 4,491,215.58 $ 6,139,270.22 $ 8,163,954.52 $ 11,085,446.77 14, 186, 190.78 $ 18,066,490.84 27 Yearly Cash Flow $ 19,825,778.00 $ 32,425,698.50 $ 43,394,240.18 $ 57,376,731.94 $ 74,993,690.65 $ 101,285,420.94 $ 129,193,817.06 $ 163,356,617.58 28 Dividends S 9,912,889.00 $ 16,212,849.25 $ 21,697,120.09 $ 28,688,365.97 $ 37,496,845.33 $ 50,642,710.47 64,596,908.53 $ 81,678,308.79 29 Retained Earnings $ 9,912,889.00 $ 16,212,849.25 $ 21,697,120.09 $ 28,688,365.97 $ 37,496,845.33 $ 50,642,710.47 $ 64,596,908.53 $ 81,678,308.79 30 31 32 33 Weak Demand Average Demand Strong Demand Question 6 +AutoSave . Off ) MT-78 Forecast.xIsx - Excel O Search A Abbo, Enrique G AE X File Home Insert Draw Page Layout Formulas Data Review View Developer Help ACROBAT Share Comments Insert Calibri 12 AA ab Accounting Ex APO LA x Delete Paste BIUV UVA $ ~ % 9 00 70 Conditional Format as Cell Sort & Find & Ideas Sensitivity Formatting Table Styles Format Filter > Select v Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity B11 X =B9*B10 B D E G H K M N Q 1 MT-78 8 Year Forecast W N Year 0 Depreciated Asset MACRS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 $17,000,000 Dep. % 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% 5 Sales Growth 10% assumption Dep Amt $2,429,300.00 $4,163,300.00 $2,973,300.00 $2,123,300.00 518,100.00 $1,516,400.00 $1,518,100.00 $758,200.0 7 8 Average Demand Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 9 Price per electric motor conversion kit S 9,100.00 $ 10,010.00 $ 11,011.00 $ 12, 112.10 $ 13,323.31 S 14,655.64 $ 16,121.21 $ 17,733.33 10 Units sold per year 40,000 44,000 48,400 53,240 58,564 64,420 70,862 77,949 Sales $ 364,000,000.00 | $ 440,440,000.00 $ 532,932,400.00 $ 644,848,204.00 $ 780,266,326.84 $ 944,122,255.48 $ 1,142,387,929.13 $ 1,382,289,394.24 12 Labor cost per electric motor S ,250.00 $ 1,675.00 $ 5,142.50 $ ,656.75 $ 5,222.43 $ 5,844.67 $ ,529.13 $ 8,282.05 13 Total Labor $ 170,000,000.00 $ 205,700,000.00 $ 248,897,000.00 $ 301, 165,370.00 364,410,097.70 $ 440,936,218.22 533,532,824.04 $ 645,574,717.09 14 Parts 2,500.00 2,750.00 3,025.00 $ 3,327.50 3,660.25 1,026.28 4,428.90 $ 4,871.79 15 Total Parts 100,000,000.00 $ 121,000,000.00 $ 146,410,000.00 $ 177,156,100.00 $ 214,358,881.00 $ 259,374,246.01 $ 313,842,837.67 $ 379,749,833.58 16 Purchase Price per Kit 1,260.00 $ 1,260.00 $ 1,260.00 $ 1,260.00 $ 1,260.00 $ 1,260.00 1,260.00 $ 1,260.00 17 Totl Purchase Price 50,400,000.00 $ 55,440,000.00 $ 60,984,000.00 $ 67,082,400.00 $ 73,790,640.00 $ 81,169,704.00 $ 89,286,674.40 $ 98,215,341.84 18 Selling General & Administrative 9,500,000.00 $ 10,450,000.00 $ 11,495,000.00 $ 12,644,500.00 $ 13,908,950.00 $ 15,299,845.00 $ 16,829,829.50 S 18,512,812.45 19 Warranty 4,000,000.00 $ 4,400,000.00 $ 4,840,000.00 $ 5,324,000.00 $ 5,856,400.00 20 Depreciation 2,429,300.00 $ 4,163,300.00 $ 2,973,300.00 $ 2,123,300.00 $ 1,518,100.00 $ 1,516,400.00 $ 1,518,100.00 $ 758,200.00 21 EBIT $ 27,670,700.00 $ 39,286,700.00 $ 57,333,100.00 $ 79,352,534.00 $ 106,423,258.14 $ 145,825,842.25 $ 187,377,663.51 $ 239,478,489.28 22 Taxes (40%) $ 11,068,280.00 $ 11,786,010.00 $ 17,199,930.00 $ 23,805,760.20 $ 31,926,977.44 $ 43,747,752.67 56,213,299.05 $ 71,843,546.78 23 Operating Income $ 16,602,420.00 $ 27,500,690.00 $ 40,133,170.00 $ 55,546,773.80 $ 74,496,280.70 $ 102,078,089.57 $ 131,164,364.46 $ 167,634,942.49 24 25 Operating Cash (Op Inc + Dep) 19,031,720.00 $ 31,663,990.00 $ 43,106,470.00 $ 57,670,073.80 $ 76,014,380.70 $ 103,594,489.57 $ 132,682,464.46 $ 168,393,142.49 26 NWC 10% annual sales $ 1,660,242.00 $ 2,750,069.00 $ 4,013,317.00 $ 5,554,677.38 $ 7,449,628.07 $ 10,207,808.96 $ 13,116,436.45 $ 16,763,494.25 27 Yearly Cash Flow $ 17,371,478.00 $ 28,913,921.00 $ 39,093,153.00 $ 52,115,396.42 $ 68,564,752.63 $ 93,386,680.62 $ 119,566,028.01 $ 151,629,648.25 28 Dividends 8,685,739.00 $ 14,456,960.50 $ 19,546,576.50 $ 26,057,698.21 $ 34,282,376.31 $ 46,693,340.31 $ 59,783,014.01 $ 75,814,824.12 29 Retained Earnings $ 8,685,739.00 $ 14,456,960.50 $ 19,546,576.50 $ 26,057,698.21 $ 34,282,376.31 $ 46,693,340.31 $ 59,783,014.01 $ 75,814,824.12 30 31 32 33 Weak Demand Average Demand Strong Demand Question 6 +AutoSave . Off H MT-78 Forecast.xlsx - Excel Search A Abbo, Enrique G AE X File Home Insert Draw Page Layout Formulas Data Review View Developer Help ACROBAT Share Comments Calibri ~ 12 ~ A" A Insert General Ex APO x Delete Paste BIUV I V $ ~ % 9 00 70 Conditional Format as Cell Sort & Find & Ideas Sensitivity Formatting " Table Styles v Format Filter > Select Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity A1 X V fx MT-78 8 Year Forecast B D E F G H K M N 0 P Q MT-78 8 Year Forecast Year 0 Depreciated Asset MACRS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 $17,000,000 Dep. 9% 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% 5 Sales Growth 10% assumption Dep Amt $2,429,300.00 $4,163,300.00 $2,973,300.00 $2,123,300.00 $1,518,100.00 $1,516,400.00 $1,518,100.00 $758,200.00 8 Strong Demand Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 9 Price per electric motor conversion kit S 9,250.00 $ 10,175.00 $ 11,192.50 $ 12,311.75 $ 13,542.93 S 14,897.22 S 16,386.94 S 18,025.63 10 Units sold per year 40,750 44,825 49,308 54,238 59,662 65,628 72,191 79,410 11 Net Sales $ 376,937,500.00 $ 456,094,375.00 $ 551,874,193.75 $ 667,767,774.44 $ 807,999,007.07 $ 977,678,798.55 $ 1,182,991,346.25 $ 1,431,419,528.96 12 Labor cost per electric motor S 4,250.00 $ 4,675.00 $ 5,142.50 $ 5,656.75 $ 6,222.43 $ 6,844.67 $ 7,529.13 $ 3,282.05 13 Total Labor $ 173,187,500.00 $ 209,556,875.00 $ 253,563,818.75 $ 306,812,220.69 $ 371,242,787.03 $ 449,203,772.31 543,536,564.49 657,679,243.04 14 Parts 2,500.00 $ 2,750.00 S 3,025.00 $ 3,327.50 $ 3,660.25 4,026.28 4,428.90 4,871.79 15 Total Parts 101,875,000.00 $ 123,268,750.00 $ 149,155,187.50 $ 180,477,776.88 $ 218,378,110.02 $ 264,237,513.12 $ 319,727,390.88 $ 386,870,142.96 16 Purchase Price per Kit 1,280.00 1,280.00 1,280.00 $ 1,280.00 $ 1,280.00 $ 1,280.00 1,280.00 1,280.00 17 Totl Purchase Price 52,160,000.00 $ 57,376,000.00 $ 63,113,600.00 $ 69,424,960.00 $ 76,367,456.00 $ 84,004,201.60 92,404, 621.76 $ 101,645,083.94 18 Selling General & Administrative 9,500,000.00 $ 10,450,000.00 $ 11,495,000.00 $ 12,644,500.00 $ 13,908,950.00 $ 15,299,845.00 16,829,829.50 $ 18,512,812.45 19 Warranty 3,667,500.00 $ 4,034,250.00 4,437,675.00 $ 4,881,442.50 $ 5,369,586.75 20 Depreciation 2,429,300.00 $ 4,163,300.00 $ 2,973,300.00 $ 2,123,300.00 $ 1,518,100.00 $ 1,516,400.00 $ 1,518,100.00 S 758,200.00 21 EBIT 34,118,200.00 $ 47,245,200.00 67,135,612.50 $ 91,403,574.38 $ 121,214,017.27 $ 163,417,066.52 208,974,839.62 $ 265,954,046.58 22 Taxes (40%) $ 13,647,280.00 $ 14,173,560.00 $ 20,140,683.75 $ 27,421,072.31 $ 36,364,205.18 $ 49,025,119.96 $ 62,692,451.89 $ 79,786,213.97 23 Operating Income $ 20,470,920.00 $ 33,071,640.00 $ 46,994,928.75 $ 63,982,502.06 $ 84,849,812.09 $ 114,391,946.57 $ 146,282,387.73 $ 186,167,832.60 24 25 Operating Cash (Op Inc + Dep) $ 22,900,220.00 $ 37,234,940.00 $ 49,968,228.75 $ 66,105,802.06 $ 86,367,912.09 $ 115,908,346.57 $ 147,800,487.73 $ 186,926,032.60 26 NWC 10% annual sale 2,047,092.00 $ 3,307,164.00 $ 4,699,492.88 $ 6,398,250.21 $ 8,484,981.21 $ 11,439,194.66 $ 14,628,238.77 18,616,783.26 27 Yearly Cash Flow $ 20,853,128.00 $ 33,927,776.00 $ 45,268,735.88 $ 59,707,551.86 $ 77,882,930.88 $ 104,469,151.91 $ 133,172,248.96 $ 168,309,249.34 28 Dividends $ 10,426,564.00 $ 16,963,888.00 $ 22,634,367.94 $ 29,853,775.93 $ 38,941,465.44 $ 52,234,575.95 $ 66,586,124.48 $ 84,154,624.67 29 Retained Earnings $ 10,426,564.00 $ 16,963,888.00 $ 22,634,367.94 $ 29,853,775.93 $ 38,941,465.44 $ 52,234,575.95 $ 66,586,124.48 $ 84,154,624.67 30 31 32 33 Weak Demand Average Demand Strong Demand Question 6 +
