Question
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
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.
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started