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.

Month NA SA Eur Pac China World Jan-14 60000 571 13091 1045 0 74662 Feb-14 77184 611 17679 1111 0 96585 Mar-14 77885 658 22759 1068 0 102369 Apr-14 86190 778 27966 1237 0 116171 May-14 96117 886 27895 1313 0 126210 Jun-14 97143 882 30566 1176 0 129768 Jul-14 84757 848 29444 1359 0 116409 Aug-14 79804 735 28364 1238 0 110141 Sep-14 64800 657 28393 1215 0 95065 Oct-14 59307 595 24444 1154 0 85500 Nov-14 52157 553 18000 1262 0 71972 Dec-14 45049 462 12453 1386 0 59349 Jan-15 58627 553 12778 1443 0 73401 Feb-15 76200 615 18214 1515 0 96545 Mar-15 82871 658 23889 1373 0 108791 Apr-15 84904 784 29455 1442 0 116584 May-15 93100 846 29464 1215 0 124625 Jun-15 93000 838 27414 1333 0 122585 Jul-15 83048 763 27368 1415 0 112594 Aug-15 74854 694 27321 1296 0 104164 Sep-15 60769 625 29444 1402 0 92241 Oct-15 55619 610 23774 1468 0 81470 Nov-15 48155 571 17308 1351 0 67386 Dec-15 42647 512 12941 1389 0 57489 Jan-16 57885 537 10962 1509 0 70892 Feb-16 77647 595 15273 1402 0 94917 Mar-16 81845 659 20556 1524 0 104583 Apr-16 86095 756 26786 1574 0 115211 May-16 91776 878 24828 1468 0 118949 Jun-16 100680 825 24737 1560 0 127801 Jul-16 86190 756 24828 1441 0 113216 Aug-16 71887 714 25179 1545 0 99325 Sep-16 60000 651 24545 1667 0 86863 Oct-16 55566 643 19286 1698 0 77193 Nov-16 50857 619 15273 1810 0 68558 Dec-16 42596 548 9107 1731 0 53982 Jan-17 58095 581 8571 1887 0 69135 Feb-17 75566 614 13158 1845 0 91182 Mar-17 80286 622 19655 1923 0 102486 Apr-17 85140 727 25179 1981 125 113027 May-17 90093 826 23103 1810 142 115832 Jun-17 95472 783 24286 1942 226 122482 Jul-17 87308 681 24737 1961 230 114686 Aug-17 74476 646 26607 2000 122 103729 Sep-17 61698 625 22982 2075 56 87381 Oct-17 57238 617 16897 2019 10 76771 Nov-17 50673 587 13750 2095 0 67105 Dec-17 51238 591 7818 2150 0 61797 Jan-18 59712 563 7547 1852 0 69673 Feb-18 77961 571 13889 1743 0 94165 Mar-18 83725 625 18302 1892 0 104544 Apr-18 90297 723 25192 2037 236 118250 May-18 91143 848 24706 1887 542 118583 Jun-18 99320 792 25306 1944 875 127363 Jul-18 93922 745 27083 2170 1386 123919 Aug-18 73143 739 26042 2037 1193 101961 Sep-18 66699 667 26304 2018 421 95688 Oct-18 56476 660 22558 2072 56 81766 Nov-18 51068 625 14773 2182 0 68648 Dec-18 46893 608 6977 2035 0 56510