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. 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.

Develop a pro forma income statement

Predict Net Income over next 3 years with estimates for:

Annual Rate of change of sales revenue

Cost of goods sold

Operating Expense

Interest Expense

Discuss pertinent what-if analysis

Prices for PLE Products

Year Mower Price Tractor Price

2014 $150 $3,250

2015 $175 $3,400

2016 $180 $3,600

2017 $185 $3,700

2018 $190 $3,800

Mower Unit Sales

Month NA SA Europe Pacific China World

Jan-14 6000 200 720 100 0 7020

Feb-14 7950 220 990 120 0 9280

Mar-14 8100 250 1320 110 0 9780

Apr-14 9050 280 1650 120 0 11100

May-14 9900 310 1590 130 0 11930

Jun-14 10200 300 1620 120 0 12240

Jul-14 8730 280 1590 140 0 10740

Aug-14 8140 250 1560 130 0 10080

Sep-14 6480 230 1590 130 0 8430

Oct-14 5990 220 1320 120 0 7650

Nov-14 5320 210 990 130 0 6650

Dec-14 4640 180 660 140 0 5620

Jan-15 5980 210 690 140 0 7020

Feb-15 7620 240 1020 150 0 9030

Mar-15 8370 250 1290 140 0 10050

Apr-15 8830 290 1620 150 0 10890

May-15 9310 330 1650 130 0 11420

Jun-15 10230 310 1590 140 0 12270

Jul-15 8720 290 1560 150 0 10720

Aug-15 7710 270 1530 140 0 9650

Sep-15 6320 250 1590 150 0 8310

Oct-15 5840 250 1260 160 0 7510

Nov-15 4960 240 900 150 0 6250

Dec-15 4350 210 660 150 0 5370

Jan-16 6020 220 570 160 0 6970

Feb-16 7920 250 840 150 0 9160

Mar-16 8430 270 1110 160 0 9970

Apr-16 9040 310 1500 170 0 11020

May-16 9820 360 1440 160 0 11780

Jun-16 10370 330 1410 170 0 12280

Jul-16 9050 310 1440 160 0 10960

Aug-16 7620 300 1410 170 0 9500

Sep-16 6420 280 1350 180 0 8230

Oct-16 5890 270 1080 180 0 7420

Nov-16 5340 260 840 190 0 6630

Dec-16 4430 230 510 180 0 5350

Jan-17 6100 250 480 200 0 7030

Feb-17 8010 270 750 190 0 9220

Mar-17 8430 280 1140 200 0 10050

Apr-17 9110 320 1410 210 0 11050

May-17 9730 380 1340 190 0 11640

Jun-17 10120 360 1360 200 0 12040

Jul-17 9080 320 1410 200 0 11010

Aug-17 7820 310 1490 210 0 9830

Sep-17 6540 300 1310 220 0 8370

Oct-17 6010 290 980 210 0 7490

Nov-17 5270 270 770 220 0 6530

Dec-17 5380 260 430 230 0 6300

Jan-18 6210 270 400 200 0 7080

Feb-18 8030 280 750 190 0 9250

Mar-18 8540 300 970 210 5 10025

Apr-18 9120 340 1310 220 16 11006

May-18 9570 390 1260 200 22 11442

Jun-18 10230 380 1240 210 26 12086

Jul-18 9580 350 1300 230 14 11474

Aug-18 7680 340 1250 220 15 9505

Sep-18 6870 320 1210 220 11 8631

Oct-18 5930 310 970 230 3 7443

Nov-18 5260 300 650 240 1 6451

Dec-18 4830 290 300 230 0 5650

Tractor Unit Sales Industry Mower Total Sales

Month NA SA Eur Pacific China World Month NA SA Eur Pac China World

Jan-14 570 250 560 212 0 1592 Jan-14 60000 571 13091 1045 0 74662

Feb-14 611 270 600 230 0 1711 Feb-14 77184 611 17679 1111 0 96585

Mar-14 630 260 680 240 0 1810 Mar-14 77885 658 22759 1068 0 102369

Apr-14 684 270 650 263 0 1867 Apr-14 86190 778 27966 1237 0 116171

May-14 650 280 580 269 0 1779 May-14 96117 886 27895 1313 0 126210

Jun-14 600 270 590 280 0 1740 Jun-14 97143 882 30566 1176 0 129768

Jul-14 512 264 760 290 0 1826 Jul-14 84757 848 29444 1359 0 116409

Aug-14 500 280 645 270 0 1695 Aug-14 79804 735 28364 1238 0 110141

Sep-14 478 290 650 263 0 1681 Sep-14 64800 657 28393 1215 0 95065

Oct-14 455 280 670 258 0 1663 Oct-14 59307 595 24444 1154 0 85500

Nov-14 407 290 888 240 0 1825 Nov-14 52157 553 18000 1262 0 71972

Dec-14 360 280 850 230 0 1720 Dec-14 45049 462 12453 1386 0 59349

Jan-15 571 320 620 250 0 1761 Jan-15 58627 553 12778 1443 0 73401

Feb-15 650 350 760 275 0 2035 Feb-15 76200 615 18214 1515 0 96545

Mar-15 740 390 742 270 0 2142 Mar-15 82871 658 23889 1373 0 108791

Apr-15 840 440 780 280 0 2340 Apr-15 84904 784 29455 1442 0 116584

May-15 830 470 690 290 0 2280 May-15 93100 846 29464 1215 0 124625

Jun-15 760 490 721 300 0 2271 Jun-15 93000 838 27414 1333 0 122585

Jul-15 681 481 680 312 0 2154 Jul-15 83048 763 27368 1415 0 112594

Aug-15 670 460 711 305 0 2146 Aug-15 74854 694 27321 1296 0 104164

Sep-15 640 460 695 290 0 2085 Sep-15 60769 625 29444 1402 0 92241

Oct-15 620 440 650 260 0 1970 Oct-15 55619 610 23774 1468 0 81470

Nov-15 570 436 680 250 0 1936 Nov-15 48155 571 17308 1351 0 67386

Dec-15 533 420 657 240 0 1850 Dec-15 42647 512 12941 1389 0 57489

Jan-16 620 510 610 250 10 2000 Jan-16 57885 537 10962 1509 0 70892

Feb-16 792 590 680 250 12 2324 Feb-16 77647 595 15273 1402 0 94917

Mar-16 890 610 730 260 20 2510 Mar-16 81845 659 20556 1524 0 104583

Apr-16 960 600 820 270 22 2672 Apr-16 86095 756 26786 1574 0 115211

May-16 1040 620 810 290 20 2780 May-16 91776 878 24828 1468 0 118949

Jun-16 1032 640 807 310 24 2813 Jun-16 100680 825 24737 1560 0 127801

Jul-16 1006 590 760 340 20 2716 Jul-16 86190 756 24828 1441 0 113216

Aug-16 910 600 720 320 31 2581 Aug-16 71887 714 25179 1545 0 99325

Sep-16 803 670 660 313 30 2476 Sep-16 60000 651 24545 1667 0 86863

Oct-16 730 630 630 290 37 2317 Oct-16 55566 643 19286 1698 0 77193

Nov-16 699 710 603 280 32 2324 Nov-16 50857 619 15273 1810 0 68558

Dec-16 647 570 570 260 33 2080 Dec-16 42596 548 9107 1731 0 53982

Jan-17 730 650 500 287 35 2202 Jan-17 58095 581 8571 1887 0 69135

Feb-17 930 680 590 290 50 2540 Feb-17 75566 614 13158 1845 0 91182

Mar-17 1160 724 620 300 63 2867 Mar-17 80286 622 19655 1923 0 102486

Apr-17 1510 730 730 310 68 3348 Apr-17 85140 727 25179 1981 125 113027

May-17 1650 760 740 330 70 3550 May-17 90093 826 23103 1810 142 115832

Jun-17 1490 800 720 340 82 3432 Jun-17 95472 783 24286 1942 226 122482

Jul-17 1460 840 670 350 80 3400 Jul-17 87308 681 24737 1961 230 114686

Aug-17 1390 830 610 341 90 3261 Aug-17 74476 646 26607 2000 122 103729

Sep-17 1360 820 599 330 100 3209 Sep-17 61698 625 22982 2075 56 87381

Oct-17 1340 810 560 320 102 3132 Oct-17 57238 617 16897 2019 10 76771

Nov-17 1240 827 550 300 110 3027 Nov-17 50673 587 13750 2095 0 67105

Dec-17 1103 750 520 290 114 2777 Dec-17 51238 591 7818 2150 0 61797

Jan-18 1250 780 480 200 111 2821 Jan-18 59712 563 7547 1852 0 69673

Feb-18 1550 805 523 210 121 3209 Feb-18 77961 571 13889 1743 0 94165

Mar-18 1820 830 560 220 123 3553 Mar-18 83725 625 18302 1892 0 104544

Apr-18 2010 890 570 230 120 3820 Apr-18 90297 723 25192 2037 236 118250

May-18 2230 930 590 253 130 4133 May-18 91143 848 24706 1887 542 118583

Jun-18 2490 980 600 270 136 4476 Jun-18 99320 792 25306 1944 875 127363

Jul-18 2440 1002 580 280 134 4436 Jul-18 93922 745 27083 2170 1386 123919

Aug-18 2334 970 570 250 132 4256 Aug-18 73143 739 26042 2037 1193 101961

Sep-18 2190 960 550 230 137 4067 Sep-18 66699 667 26304 2018 421 95688

Oct-18 2080 930 530 220 130 3890 Oct-18 56476 660 22558 2072 56 81766

Nov-18 2050 920 517 190 139 3816 Nov-18 51068 625 14773 2182 0 68648

Dec-18 2004 902 490 190 131 3717 Dec-18 46893 608 6977 2035 0 56510

Unit Production Costs Operating and Interest Expenses Industry Tractor Total Sales

Month Tractor Mower Month Administrative Depreciation Interest Month NA SA Eur Pac China World

Jan-14 $1,750 $50 Jan-14 $633,073.00 $140,467.00 $7,244.00 Jan-14 8143 984 5091 987 278 15483

Feb-14 $1,755 $50 Feb-14 $607,904.00 $165,636.00 $7,679.00 Feb-14 8592 1051 5310 1090 283 16325

Mar-14 $1,763 $51 Mar-14 $630,687.00 $142,853.00 $6,887.00 Mar-14 8630 1016 6071 1127 285 17129

Apr-14 $1,770 $51 Apr-14 $613,401.00 $160,139.00 $6,917.00 Apr-14 8947 1027 5856 1209 288 17327

May-14 $1,778 $51 May-14 $607,664.00 $165,876.00 $8,316.00 May-14 8442 1057 5273 1221 286 16278

Jun-14 $1,785 $51 Jun-14 $632,967.00 $140,573.00 $7,428.00 Jun-14 7500 1019 5315 1327 287 15448

Jul-14 $1,792 $51 Jul-14 $609,604.00 $163,936.00 $8,737.00 Jul-14 6145 977 7170 1324 289 15905

Aug-14 $1,795 $51 Aug-14 $607,749.00 $165,791.00 $7,054.00 Aug-14 5882 1057 5926 1268 290 14422

Sep-14 $1,801 $52 Sep-14 $603,367.00 $170,173.00 $8,862.00 Sep-14 5595 1086 6075 1209 293 14258

Oct-14 $1,804 $52 Oct-14 $629,083.00 $144,457.00 $8,488.00 Oct-14 5233 1045 6321 1168 295 14061

Nov-14 $1,810 $52 Nov-14 $611,995.00 $161,545.00 $7,049.00 Nov-14 4494 1078 8381 1127 298 15378

Dec-14 $1,813 $52 Dec-14 $625,712.00 $147,828.00 $8,807.00 Dec-14 3913 1029 7944 1085 301 14272

Jan-15 $1,835 $55 Jan-15 $656,123.00 $175,447.00 $7,430.00 Jan-15 5938 1172 5688 1185 306 14289

Feb-15 $1,841 $55 Feb-15 $652,679.00 $178,891.00 $6,791.00 Feb-15 6633 1273 7037 1286 302 16530

Mar-15 $1,848 $55 Mar-15 $655,521.00 $176,049.00 $8,013.00 Mar-15 7327 1423 6981 1286 303 17320

Apr-15 $1,854 $55 Apr-15 $676,581.00 $154,989.00 $8,979.00 Apr-15 8077 1612 7500 1346 307 18842

May-15 $1,860 $56 May-15 $676,581.00 $154,989.00 $7,484.00 May-15 7830 1728 6571 1388 309 17826

Jun-15 $1,866 $56 Jun-15 $656,440.00 $175,130.00 $7,858.00 Jun-15 7103 1815 6990 1449 312 17669

Jul-15 $1,872 $56 Jul-15 $661,969.00 $169,601.00 $7,424.00 Jul-15 6239 1776 6667 1490 315 16487

Aug-15 $1,878 $56 Aug-15 $677,212.00 $154,358.00 $6,848.00 Aug-15 6036 1685 6762 1449 318 16250

Sep-15 $1,885 $56 Sep-15 $653,545.00 $178,025.00 $6,751.00 Sep-15 5664 1679 6635 1394 321 15692

Oct-15 $1,892 $57 Oct-15 $657,388.00 $174,182.00 $8,160.00 Oct-15 5345 1618 6311 1256 315 14844

Nov-15 $1,897 $57 Nov-15 $672,475.00 $159,095.00 $7,898.00 Nov-15 4831 1564 6476 1214 318 14402

Dec-15 $1,903 $57 Dec-15 $656,325.00 $175,245.00 $8,953.00 Dec-15 4454 1522 6250 1171 320 13716

Jan-16 $1,925 $59 Jan-16 $723,594.00 $226,526.00 $9,443.00 Jan-16 5299 1835 5922 1208 333 14597

Feb-16 $1,931 $59 Feb-16 $759,042.00 $191,078.00 $8,464.00 Feb-16 6529 2115 6667 1214 313 16836

Mar-16 $1,938 $59 Mar-16 $749,187.00 $200,933.00 $10,264.00 Mar-16 7120 2202 7228 1256 606 18412

Apr-16 $1,944 $59 Apr-16 $751,499.00 $198,621.00 $8,547.00 Apr-16 7619 2151 8200 1311 571 19852

May-16 $1,950 $59 May-16 $741,452.00 $208,668.00 $8,578.00 May-16 8387 2214 7941 1415 556 20513

Jun-16 $1,956 $60 Jun-16 $729,122.00 $220,998.00 $9,519.00 Jun-16 8110 2278 7921 1520 526 20355

Jul-16 $1,963 $60 Jul-16 $734,783.00 $215,337.00 $9,343.00 Jul-16 7752 2100 7677 1675 513 19716

Aug-16 $1,969 $60 Aug-16 $748,208.00 $201,912.00 $8,448.00 Aug-16 6894 2128 7200 1584 769 18575

Sep-16 $1,976 $60 Sep-16 $738,186.00 $211,934.00 $9,957.00 Sep-16 6015 2367 6735 1527 750 17394

Oct-16 $1,983 $60 Oct-16 $759,403.00 $190,717.00 $9,738.00 Oct-16 5368 2211 6495 1422 732 16226

Nov-16 $1,990 $61 Nov-16 $726,183.00 $223,937.00 $9,785.00 Nov-16 4964 2483 6061 1366 714 15587

Dec-16 $1,996 $61 Dec-16 $757,037.00 $193,083.00 $8,191.00 Dec-16 4444 1986 5816 1262 698 14207

Jan-17 $2,011 $59 Jan-17 $672,232.00 $179,138.00 $9,914.00 Jan-17 5000 2257 5051 1373 714 14394

Feb-17 $2,001 $59 Feb-17 $665,023.00 $186,347.00 $9,954.00 Feb-17 6284 2353 6082 1436 1063 17218

Mar-17 $2,024 $59 Mar-17 $667,657.00 $183,713.00 $10,859.00 Mar-17 7785 2457 6327 1478 1264 19310

Apr-17 $2,030 $59 Apr-17 $654,198.00 $197,172.00 $9,730.00 Apr-17 9934 2517 7604 1512 1333 22901

May-17 $2,039 $60 May-17 $659,435.00 $191,935.00 $10,430.00 May-17 10645 2612 7789 1642 1556 24244

Jun-17 $2,026 $60 Jun-17 $661,190.00 $190,180.00 $10,222.00 Jun-17 9491 2749 7347 1667 1739 22993

Jul-17 $2,034 $60 Jul-17 $647,321.00 $204,049.00 $10,102.00 Jul-17 9182 2887 6979 1733 1702 22483

Aug-17 $2,033 $60 Aug-17 $666,743.00 $184,627.00 $10,610.00 Aug-17 8528 2833 6489 1700 1915 21465

Sep-17 $2,056 $60 Sep-17 $678,705.00 $172,665.00 $9,374.00 Sep-17 8293 2789 6316 1642 2083 21123

Oct-17 $2,065 $60 Oct-17 $658,990.00 $192,380.00 $10,830.00 Oct-17 8221 2765 5833 1576 2128 20523

Nov-17 $2,068 $61 Nov-17 $656,221.00 $195,149.00 $9,017.00 Nov-17 7470 2746 5789 1493 2292 19789

Dec-17 $2,069 $61 Dec-17 $676,934.00 $174,436.00 $10,423.00 Dec-17 6509 2534 5591 1450 2245 18329

Jan-18 $2,073 $63 Jan-18 $641,571.00 $210,589.00 $9,985.00 Jan-18 7267 2635 5106 1010 2292 18311

Feb-18 $2,077 $63 Feb-18 $634,973.00 $217,187.00 $9,766.00 Feb-18 8807 2703 5474 1045 2449 20477

Mar-18 $2,081 $63 Mar-18 $662,054.00 $190,106.00 $11,148.00 Mar-18 10168 2795 6022 1106 2400 22489

Apr-18 $2,086 $63 Apr-18 $654,962.00 $197,198.00 $9,339.00 Apr-18 11044 2997 6064 1150 2353 23607

May-18 $2,092 $63 May-18 $645,579.00 $206,581.00 $9,468.00 May-18 12120 3131 6344 1244 2600 25439

Jun-18 $2,098 $63 Jun-18 $658,112.00 $194,048.00 $10,324.00 Jun-18 13459 3311 6593 1357 2653 27374

Jul-18 $2,104 $64 Jul-18 $637,711.00 $214,449.00 $9,737.00 Jul-18 13048 3390 6304 1421 2600 26764

Aug-18 $2,110 $64 Aug-18 $638,317.00 $213,843.00 $9,290.00 Aug-18 12275 3277 6064 1263 2549 25428

Sep-18 $2,116 $64 Sep-18 $651,996.00 $200,164.00 $9,213.00 Sep-18 11347 3232 5789 1173 2453 23995

Oct-18 $2,122 $64 Oct-18 $630,766.00 $221,394.00 $10,143.00 Oct-18 10667 3131 5699 1128 2517 23142

Nov-18 $2,129 $64 Nov-18 $645,095.00 $207,065.00 $10,383.00 Nov-18 10459 3087 5604 974 2541 22666

Dec-18 $2,135 $64 Dec-18 $637,807.00 $214,353.00 $9,059.00 Dec-18 10082 3030 5444 979 2453 21989

This is all the Data. I had to copy and paste it as it seems the link cannot be excessed. Thank you to anyone that can help me.

Please use this link: https://1drv.ms/x/s!AoxUzwRMUf1vkyd7N0vGOXkPCSck?e=ZqW9DG