Part 1: The Performance Lawn Equipment database contains data needed to develop a pro forma income statement. Dealers selling PLE products all receive 18 percent of sales revenue for their part of doing business, and this is accounted for as the selling expense. The tax rate is 50 percent. Develop an Excel worksheet to extract and summarize the data needed to develop the income statement for 2014, and implement an Excel model in the form of a pro forma income statement for the company.

Part 2: The CFO of Performance Lawn Equipment, J. Kenneth Valentine, would like to have a model to predict the net income for the next three years. To do this, you need to determine how the variables in the pro forma income statement will likely change in the future. Using the calculations and worksheet that you developed along with other historical data in the database, estimate the annual rate of change in sales revenue, the cost of goods sold, the operating expense, and the interest expense. Use these rates to modify the pro forma income statement to predict the net income over the next three years.

Because the estimates you derived from the historical data may not hold in the future, conduct appropriate "what-if" scenarios and/or parametric sensitivity analyses to investigate how the projections might change if these assumptions dont hold. In your model, construct a tornado chart to show how the assumptions impact the net income . Summarize your results and conclusions in a report to Mr. Valentine. Upload your Word document AND your Excel worksheet file to the Assignment drop box.

Operating and Interest Expenses Mower Unit Sales Tractor Unit Sales Month 571 250 Month SA Europe Pacific China World Jan-14 200 720 100 0 48660 Feb-14 220 990 120 050951 Mar-14 250 1320 110 0 51479 Apr-14 280 1650 120 052830 May-14 310 1590 130 0 53690 Jun-14 300 1620 120 054031 Jul-14 280 1590 140 0 52561 Aug-14 250 1560 130 0 51932 Sep-14 230 1590 130 0 50313 Oct-14 220 1320 120 0 49563 Nov-14 210 990 130 0 48594 Dec-14 180 660 140 0 47594 Jan-15 210 690 140 0 49025 Feb-15 240 1020 150 0 51066 Mar-15 250 1290 140 0 52114 Apr-15 290 1620 150 052985 May-15 330 1650 130 0 53545 Jun-15 310 1590 140 0 54426 Jul-15 290 1560 150 0 52906 Aug-15 270 1530 140 0 51867 Sep-15 250 1590 150 0 50558 Oct-15 250 1260 160 0 49788 Nov-15 240 900 150 0 48559 Dec-15 210 660 150 0 47709 Jan-16 220 570 160 049340 Feb-16 250 840 150 051561 Mar-16 270 1110 160 052400 Apr-16 310 170 0 53481 May-16 360 1440 160 054271 Jun-16 330 1410 170 0 54802 Jul-16 310 1440 160 0 53512 Aug-16 300 1410 170 0 52083 Sep-16 280 1350 180 050844 Oct-16 270 1080 180 050064 Nov-16 260 840 190 0 49305 Dec-16 230 510 180 048055 Month NA SA Eur Pacific China World Jan-14 570 250 560 212 0 1592 Feb-14 611 270 600 230 01 1711 Mar-14 630 260 680 240 0 1810 Apr-14 684 270 650 263 0' 1867 May-14 650 280 580 269 0 1779 Jun-14 600 270 590 280 0 1740 Jul-14 512 264 760 290 0' 1826 Aug-14 500 280 645 270 07 1695 Sep-14 478 290 650 263 0 1681 Oct-14 455 280 670 258 0' 1663 Nov-14 407 290 888 240 0 1825 Dec-14 360 280 850 230 0 1720 Jan-15 320 620 0' 1761 Feb-15 650 350 760 275 0 2035 Mar-15 740 390 742 270 0 2142 Apr-15 840 440 780 280 0 2340 May-15 830 470 690 290 Jun-15 760 490 721 300 Jul-15 681 481 680 312 Aug-15 670 460 711 305 0 2146 Sep-15 640 460 695 290 0 2085 Oct-15 620 440 650 260 0' 1970 Nov-15 570 436 680 250 0 1936 Dec-15 533 420 657 240 0 1850 Jan-16 620 510 610 10' 2000 Feb-16 792 590 680 250 12 2324 Mar-16 890 610 730 260 20 2510 Apr-16 960 600 820 270 22 2672 May-16 1040 620 810 290 20 2780 Jun-16 1032 640 807 310 24 2813 Jul-16 1006 590 760 340 20' 2716 Aug-16 910 600 720 320 31 2581 Sep-16 803 670 660 313 30 2476 Oct-16 730 630 630 290 37 2317 Nov-16 699 710 603 280 32 2324 Dec-16 647 570 570 260 33' 2080 0 2280 02271 0' 2154 Administrative Depreciation Interest Jan-14 $633,073 $140,467 $7,244 Feb-14 $607,904 $165,636 $7,679 Mar-14 $630,687 $142,853 $6,887 Apr-14 $613,401 $160,139 $6,917 May-14 $607,664 $165,876 $8,316 Jun-14 $632,967 $140,573 $7,428 Jul-14 $609,604 $163,936 $8,737 Aug-14 $607,749 $165,791 $7,054 Sep-14 $603,367 $170,173 $8,862 Oct-14 $629,083 $144,457 $8,488 Nov-14 $611,995 $161,545 $7,049 Dec-14 $625,712 $147,828 $8,807 Jan-15 $656,123 $175,447 $7,430 Feb-15 $652,679 $178,891 $6,791 Mar-15 $655,521 $176,049 $8,013 Apr-15 $676,581 $154,989 $8,979 May-15 $676,581 $154,989 $7,484 Jun-15 $656,440 $175,130 $7,858 Jul-15 $661,969 $169,601 $7,424 Aug-15 $677,212 $154,358 $6,848 Sep-15 $653,545 $178,025 $6,751 Oct-15 $657,388 $174,182 $8,160 Nov-15 $672,475 $159,095 $7,898 Dec-15 $656,325 $175,245 $8,953 Jan-16 $723,594 $226,526 $9,443 Feb-16 $759,042 $191,078 $8,464 Mar-16 $749,187 $200,933 $10,264 Apr-16 $751,499 $198,621 $8,547 May-16 $741,452 $208,668 $8,578 Jun-16 $729,122 $220,998 $9,519 Jul-16 $734,783 $215,337 $9,343 Aug-16 $748,208 $201,912 $8,448 Sep-16 $738,186 $211,934 $9,957 Oct-16 $759,403 $190,717 $9,738 Nov-16 $726,183 $223,937 $9,785 Dec-16 $757,037 $193,083 $8,191 250 1500 250 270 280 480 750 1140 650 680 724 500 590 620 35 50 63 68 320 1410 730 730 70 200 190 200 210 190 200 200 210 220 210 220 760 800 840 830 820 810 827 230 750 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 380 360 320 310 300 290 270 260 270 280 300 340 390 380 350 340 320 310 300 290 1340 1360 1410 1490 1310 980 770 430 400 750 970 1310 1260 1240 1300 1250 1210 970 650 300 049766 0 51987 052845 0 53876 0 54496 0 54927 053927 0 52778 051349 0 50499 049570 049370 0 50181 0 52382 5' 53185 16 54197 22' 54663 26 55338 14 54756 1552818 11 51975 3 50817 149856 049085 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 730 930 1160 1510 1650 1490 1460 1390 1360 1340 1240 1103 1250 1550 1820 2010 2230 2490 2440 2334 2190 2080 2050 2004 740 720 670 610 599 560 550 520 480 523 560 570 590 600 580 287 290 300 310 330 340 350 341 330 320 300 290 200 210 220 230 253 270 280 250 230 220 190 190 2202 2540 2867 3348 3550 3432 3400 3261 3209 3132 3027 2777 2821 3209 3553 3820 4133 4476 4436 4256 4067 3890 3816 3717 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 82 80 90 100' 102 110 114 111 121 123 120 130 136 134 132 137 130 139 131 $672,232 $665,023 $667,657 $654,198 $659,435 $661,190 $647,321 $666,743 $678,705 $658,990 $656,221 $676,934 $641,571 $634,973 $662,054 $654,962 $645,579 $658,112 $637,711 $638,317 $651,996 $630,766 $645,095 $637,807 $179,138 $9,914 $186,347 $9,954 $183,713 $10,859 $197,172 $9,730 $191,935 $10,430 $190,180 $10,222 $204,049 $10,102 $184,627 $10,610 $172,665 $9,374 $192,380 $10,830 $195,149 $9,017 $174,436 $10,423 $210,589 $9.985 $217,187 $9,766 $190,106 $11,148 $197,198 $9,339 $206,581 $9,468 $194,048 $10,324 $214,449 $9,737 $213,843 $9,290 $200,164 $9,213 $221,394 $10,143 $207,065 $10,383 $214,353 $9,059 200 190 210 220 200 210 230 220 220 230 240 230 780 805 830 890 930 980 1002 970 960 930 920 902 570 550 530 517 490 Fixed Costs of Capacity Increase or New Construction Current Plants Kansas City Kansas City Santiago Santiago Additional Capacity Cost 10000 $605,000.00 20000 $985,000.00 5000 $381,000.00 10000 $680,000.00 Proposed Locations Maximum capacity Auckland 15,000 Auckland 20,000 Birmingham 15,000 Birmingham 20,000 Frankfurt 15,000 Frankfurt 20,000 Mumbai 15,000 Mumbai 25,000 Singapore 15,000 Singapore 20,000 Cost $917,000.00 $1,136,000.00 $962,000.00 $1,180,000.00 $874,000.00 $1,093,000.00 $750,000.00 $959,000.00 $839,000.00 $1,058,000.00 Operating and Interest Expenses Mower Unit Sales Tractor Unit Sales Month 571 250 Month SA Europe Pacific China World Jan-14 200 720 100 0 48660 Feb-14 220 990 120 050951 Mar-14 250 1320 110 0 51479 Apr-14 280 1650 120 052830 May-14 310 1590 130 0 53690 Jun-14 300 1620 120 054031 Jul-14 280 1590 140 0 52561 Aug-14 250 1560 130 0 51932 Sep-14 230 1590 130 0 50313 Oct-14 220 1320 120 0 49563 Nov-14 210 990 130 0 48594 Dec-14 180 660 140 0 47594 Jan-15 210 690 140 0 49025 Feb-15 240 1020 150 0 51066 Mar-15 250 1290 140 0 52114 Apr-15 290 1620 150 052985 May-15 330 1650 130 0 53545 Jun-15 310 1590 140 0 54426 Jul-15 290 1560 150 0 52906 Aug-15 270 1530 140 0 51867 Sep-15 250 1590 150 0 50558 Oct-15 250 1260 160 0 49788 Nov-15 240 900 150 0 48559 Dec-15 210 660 150 0 47709 Jan-16 220 570 160 049340 Feb-16 250 840 150 051561 Mar-16 270 1110 160 052400 Apr-16 310 170 0 53481 May-16 360 1440 160 054271 Jun-16 330 1410 170 0 54802 Jul-16 310 1440 160 0 53512 Aug-16 300 1410 170 0 52083 Sep-16 280 1350 180 050844 Oct-16 270 1080 180 050064 Nov-16 260 840 190 0 49305 Dec-16 230 510 180 048055 Month NA SA Eur Pacific China World Jan-14 570 250 560 212 0 1592 Feb-14 611 270 600 230 01 1711 Mar-14 630 260 680 240 0 1810 Apr-14 684 270 650 263 0' 1867 May-14 650 280 580 269 0 1779 Jun-14 600 270 590 280 0 1740 Jul-14 512 264 760 290 0' 1826 Aug-14 500 280 645 270 07 1695 Sep-14 478 290 650 263 0 1681 Oct-14 455 280 670 258 0' 1663 Nov-14 407 290 888 240 0 1825 Dec-14 360 280 850 230 0 1720 Jan-15 320 620 0' 1761 Feb-15 650 350 760 275 0 2035 Mar-15 740 390 742 270 0 2142 Apr-15 840 440 780 280 0 2340 May-15 830 470 690 290 Jun-15 760 490 721 300 Jul-15 681 481 680 312 Aug-15 670 460 711 305 0 2146 Sep-15 640 460 695 290 0 2085 Oct-15 620 440 650 260 0' 1970 Nov-15 570 436 680 250 0 1936 Dec-15 533 420 657 240 0 1850 Jan-16 620 510 610 10' 2000 Feb-16 792 590 680 250 12 2324 Mar-16 890 610 730 260 20 2510 Apr-16 960 600 820 270 22 2672 May-16 1040 620 810 290 20 2780 Jun-16 1032 640 807 310 24 2813 Jul-16 1006 590 760 340 20' 2716 Aug-16 910 600 720 320 31 2581 Sep-16 803 670 660 313 30 2476 Oct-16 730 630 630 290 37 2317 Nov-16 699 710 603 280 32 2324 Dec-16 647 570 570 260 33' 2080 0 2280 02271 0' 2154 Administrative Depreciation Interest Jan-14 $633,073 $140,467 $7,244 Feb-14 $607,904 $165,636 $7,679 Mar-14 $630,687 $142,853 $6,887 Apr-14 $613,401 $160,139 $6,917 May-14 $607,664 $165,876 $8,316 Jun-14 $632,967 $140,573 $7,428 Jul-14 $609,604 $163,936 $8,737 Aug-14 $607,749 $165,791 $7,054 Sep-14 $603,367 $170,173 $8,862 Oct-14 $629,083 $144,457 $8,488 Nov-14 $611,995 $161,545 $7,049 Dec-14 $625,712 $147,828 $8,807 Jan-15 $656,123 $175,447 $7,430 Feb-15 $652,679 $178,891 $6,791 Mar-15 $655,521 $176,049 $8,013 Apr-15 $676,581 $154,989 $8,979 May-15 $676,581 $154,989 $7,484 Jun-15 $656,440 $175,130 $7,858 Jul-15 $661,969 $169,601 $7,424 Aug-15 $677,212 $154,358 $6,848 Sep-15 $653,545 $178,025 $6,751 Oct-15 $657,388 $174,182 $8,160 Nov-15 $672,475 $159,095 $7,898 Dec-15 $656,325 $175,245 $8,953 Jan-16 $723,594 $226,526 $9,443 Feb-16 $759,042 $191,078 $8,464 Mar-16 $749,187 $200,933 $10,264 Apr-16 $751,499 $198,621 $8,547 May-16 $741,452 $208,668 $8,578 Jun-16 $729,122 $220,998 $9,519 Jul-16 $734,783 $215,337 $9,343 Aug-16 $748,208 $201,912 $8,448 Sep-16 $738,186 $211,934 $9,957 Oct-16 $759,403 $190,717 $9,738 Nov-16 $726,183 $223,937 $9,785 Dec-16 $757,037 $193,083 $8,191 250 1500 250 270 280 480 750 1140 650 680 724 500 590 620 35 50 63 68 320 1410 730 730 70 200 190 200 210 190 200 200 210 220 210 220 760 800 840 830 820 810 827 230 750 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 380 360 320 310 300 290 270 260 270 280 300 340 390 380 350 340 320 310 300 290 1340 1360 1410 1490 1310 980 770 430 400 750 970 1310 1260 1240 1300 1250 1210 970 650 300 049766 0 51987 052845 0 53876 0 54496 0 54927 053927 0 52778 051349 0 50499 049570 049370 0 50181 0 52382 5' 53185 16 54197 22' 54663 26 55338 14 54756 1552818 11 51975 3 50817 149856 049085 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 730 930 1160 1510 1650 1490 1460 1390 1360 1340 1240 1103 1250 1550 1820 2010 2230 2490 2440 2334 2190 2080 2050 2004 740 720 670 610 599 560 550 520 480 523 560 570 590 600 580 287 290 300 310 330 340 350 341 330 320 300 290 200 210 220 230 253 270 280 250 230 220 190 190 2202 2540 2867 3348 3550 3432 3400 3261 3209 3132 3027 2777 2821 3209 3553 3820 4133 4476 4436 4256 4067 3890 3816 3717 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 82 80 90 100' 102 110 114 111 121 123 120 130 136 134 132 137 130 139 131 $672,232 $665,023 $667,657 $654,198 $659,435 $661,190 $647,321 $666,743 $678,705 $658,990 $656,221 $676,934 $641,571 $634,973 $662,054 $654,962 $645,579 $658,112 $637,711 $638,317 $651,996 $630,766 $645,095 $637,807 $179,138 $9,914 $186,347 $9,954 $183,713 $10,859 $197,172 $9,730 $191,935 $10,430 $190,180 $10,222 $204,049 $10,102 $184,627 $10,610 $172,665 $9,374 $192,380 $10,830 $195,149 $9,017 $174,436 $10,423 $210,589 $9.985 $217,187 $9,766 $190,106 $11,148 $197,198 $9,339 $206,581 $9,468 $194,048 $10,324 $214,449 $9,737 $213,843 $9,290 $200,164 $9,213 $221,394 $10,143 $207,065 $10,383 $214,353 $9,059 200 190 210 220 200 210 230 220 220 230 240 230 780 805 830 890 930 980 1002 970 960 930 920 902 570 550 530 517 490 Fixed Costs of Capacity Increase or New Construction Current Plants Kansas City Kansas City Santiago Santiago Additional Capacity Cost 10000 $605,000.00 20000 $985,000.00 5000 $381,000.00 10000 $680,000.00 Proposed Locations Maximum capacity Auckland 15,000 Auckland 20,000 Birmingham 15,000 Birmingham 20,000 Frankfurt 15,000 Frankfurt 20,000 Mumbai 15,000 Mumbai 25,000 Singapore 15,000 Singapore 20,000 Cost $917,000.00 $1,136,000.00 $962,000.00 $1,180,000.00 $874,000.00 $1,093,000.00 $750,000.00 $959,000.00 $839,000.00 $1,058,000.00