Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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.
Tractor Unit Sales | ||||||
Month | NA | SA | Eur | Pacific | China | World |
Jan-14 | 570 | 250 | 560 | 212 | 0 | 1592 |
Feb-14 | 611 | 270 | 600 | 230 | 0 | 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 | 0 | 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 | 571 | 320 | 620 | 250 | 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 | 0 | 2280 |
Jun-15 | 760 | 490 | 721 | 300 | 0 | 2271 |
Jul-15 | 681 | 481 | 680 | 312 | 0 | 2154 |
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 | 250 | 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 |
Jan-17 | 730 | 650 | 500 | 287 | 35 | 2202 |
Feb-17 | 930 | 680 | 590 | 290 | 50 | 2540 |
Mar-17 | 1160 | 724 | 620 | 300 | 63 | 2867 |
Apr-17 | 1510 | 730 | 730 | 310 | 68 | 3348 |
May-17 | 1650 | 760 | 740 | 330 | 70 | 3550 |
Jun-17 | 1490 | 800 | 720 | 340 | 82 | 3432 |
Jul-17 | 1460 | 840 | 670 | 350 | 80 | 3400 |
Aug-17 | 1390 | 830 | 610 | 341 | 90 | 3261 |
Sep-17 | 1360 | 820 | 599 | 330 | 100 | 3209 |
Oct-17 | 1340 | 810 | 560 | 320 | 102 | 3132 |
Nov-17 | 1240 | 827 | 550 | 300 | 110 | 3027 |
Dec-17 | 1103 | 750 | 520 | 290 | 114 | 2777 |
Jan-18 | 1250 | 780 | 480 | 200 | 111 | 2821 |
Feb-18 | 1550 | 805 | 523 | 210 | 121 | 3209 |
Mar-18 | 1820 | 830 | 560 | 220 | 123 | 3553 |
Apr-18 | 2010 | 890 | 570 | 230 | 120 | 3820 |
May-18 | 2230 | 930 | 590 | 253 | 130 | 4133 |
Jun-18 | 2490 | 980 | 600 | 270 | 136 | 4476 |
Jul-18 | 2440 | 1002 | 580 | 280 | 134 | 4436 |
Aug-18 | 2334 | 970 | 570 | 250 | 132 | 4256 |
Sep-18 | 2190 | 960 | 550 | 230 | 137 | 4067 |
Oct-18 | 2080 | 930 | 530 | 220 | 130 | 3890 |
Nov-18 | 2050 | 920 | 517 | 190 | 139 | 3816 |
Dec-18 | 2004 | 902 | 490 | 190 | 131 | 3717 |
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 |
Unit Production Costs | ||
Month | Tractor | Mower |
Jan-14 | $1,750 | $50 |
Feb-14 | $1,755 | $50 |
Mar-14 | $1,763 | $51 |
Apr-14 | $1,770 | $51 |
May-14 | $1,778 | $51 |
Jun-14 | $1,785 | $51 |
Jul-14 | $1,792 | $51 |
Aug-14 | $1,795 | $51 |
Sep-14 | $1,801 | $52 |
Oct-14 | $1,804 | $52 |
Nov-14 | $1,810 | $52 |
Dec-14 | $1,813 | $52 |
Jan-15 | $1,835 | $55 |
Feb-15 | $1,841 | $55 |
Mar-15 | $1,848 | $55 |
Apr-15 | $1,854 | $55 |
May-15 | $1,860 | $56 |
Jun-15 | $1,866 | $56 |
Jul-15 | $1,872 | $56 |
Aug-15 | $1,878 | $56 |
Sep-15 | $1,885 | $56 |
Oct-15 | $1,892 | $57 |
Nov-15 | $1,897 | $57 |
Dec-15 | $1,903 | $57 |
Jan-16 | $1,925 | $59 |
Feb-16 | $1,931 | $59 |
Mar-16 | $1,938 | $59 |
Apr-16 | $1,944 | $59 |
May-16 | $1,950 | $59 |
Jun-16 | $1,956 | $60 |
Jul-16 | $1,963 | $60 |
Aug-16 | $1,969 | $60 |
Sep-16 | $1,976 | $60 |
Oct-16 | $1,983 | $60 |
Nov-16 | $1,990 | $61 |
Dec-16 | $1,996 | $61 |
Jan-17 | $2,011 | $59 |
Feb-17 | $2,001 | $59 |
Mar-17 | $2,024 | $59 |
Apr-17 | $2,030 | $59 |
May-17 | $2,039 | $60 |
Jun-17 | $2,026 | $60 |
Jul-17 | $2,034 | $60 |
Aug-17 | $2,033 | $60 |
Sep-17 | $2,056 | $60 |
Oct-17 | $2,065 | $60 |
Nov-17 | $2,068 | $61 |
Dec-17 | $2,069 | $61 |
Jan-18 | $2,073 | $63 |
Feb-18 | $2,077 | $63 |
Mar-18 | $2,081 | $63 |
Apr-18 | $2,086 | $63 |
May-18 | $2,092 | $63 |
Jun-18 | $2,098 | $63 |
Jul-18 | $2,104 | $64 |
Aug-18 | $2,110 | $64 |
Sep-18 | $2,116 | $64 |
Oct-18 | $2,122 | $64 |
Nov-18 | $2,129 | $64 |
Dec-18 | $2,135 | $64 |
Operating and Interest Expenses | |||
Month | 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 |
Jan-17 | $672,232 | $179,138 | $9,914 |
Feb-17 | $665,023 | $186,347 | $9,954 |
Mar-17 | $667,657 | $183,713 | $10,859 |
Apr-17 | $654,198 | $197,172 | $9,730 |
May-17 | $659,435 | $191,935 | $10,430 |
Jun-17 | $661,190 | $190,180 | $10,222 |
Jul-17 | $647,321 | $204,049 | $10,102 |
Aug-17 | $666,743 | $184,627 | $10,610 |
Sep-17 | $678,705 | $172,665 | $9,374 |
Oct-17 | $658,990 | $192,380 | $10,830 |
Nov-17 | $656,221 | $195,149 | $9,017 |
Dec-17 | $676,934 | $174,436 | $10,423 |
Jan-18 | $641,571 | $210,589 | $9,985 |
Feb-18 | $634,973 | $217,187 | $9,766 |
Mar-18 | $662,054 | $190,106 | $11,148 |
Apr-18 | $654,962 | $197,198 | $9,339 |
May-18 | $645,579 | $206,581 | $9,468 |
Jun-18 | $658,112 | $194,048 | $10,324 |
Jul-18 | $637,711 | $214,449 | $9,737 |
Aug-18 | $638,317 | $213,843 | $9,290 |
Sep-18 | $651,996 | $200,164 | $9,213 |
Oct-18 | $630,766 | $221,394 | $10,143 |
Nov-18 | $645,095 | $207,065 | $10,383 |
Dec-18 | $637,807 | $214,353 | $9,059 |
Unit Shipping Cost | |||||
Plant | Customer | Mowers | Tractors | ||
Singapore | Toronto | $1.71 | $2.03 | ||
Birmingham | Toronto | $1.34 | $1.78 | ||
Frankfurt | Toronto | $1.52 | $1.87 | ||
Mumbai | Toronto | $1.67 | $2.14 | ||
Kansas City | Toronto | $1.36 | $1.79 | ||
Auckland | Toronto | $1.86 | $2.19 | ||
Santiago | Toronto | $1.49 | $2.13 | ||
Singapore | Shanghai | $1.44 | $1.78 | ||
Birmingham | Shanghai | $1.60 | $2.15 | ||
Frankfurt | Shanghai | $1.65 | $2.32 | ||
Mumbai | Shanghai | $1.21 | $1.47 | ||
Kansas City | Shanghai | $1.58 | $2.13 | ||
Auckland | Shanghai | $1.18 | $1.63 | ||
Santiago | Shanghai | $1.47 | $2.03 | ||
Singapore | Mexico City | $1.72 | $2.09 | ||
Birmingham | Mexico City | $1.29 | $1.79 | ||
Frankfurt | Mexico City | $1.54 | $2.04 | ||
Mumbai | Mexico City | $1.56 | $2.22 | ||
Kansas City | Mexico City | $1.32 | $1.76 | ||
Auckland | Mexico City | $1.50 | $2.07 | ||
Santiago | Mexico City | $1.22 | $1.58 | ||
Singapore | Melbourne | $1.43 | $1.70 | ||
Birmingham | Melbourne | $1.52 | $2.06 | ||
Frankfurt | Melbourne | $1.73 | $2.28 | ||
Mumbai | Melbourne | $1.38 | $1.63 | ||
Kansas City | Melbourne | $1.72 | $2.34 | ||
Auckland | Melbourne | $0.91 | $1.17 | ||
Santiago | Melbourne | $1.49 | $1.80 | ||
Singapore | London | $1.88 | $2.68 | ||
Birmingham | London | $1.47 | $1.77 | ||
Frankfurt | London | $1.37 | $1.64 | ||
Mumbai | London | $1.44 | $1.82 | ||
Kansas City | London | $1.49 | $1.86 | ||
Auckland | London | $1.98 | $2.60 | ||
Santiago | London | $1.58 | $2.14 | ||
Singapore | Caracas | $1.50 | $2.01 | ||
Birmingham | Caracas | $1.37 | $1.86 | ||
Frankfurt | Caracas | $1.59 | $1.88 | ||
Mumbai | Caracas | $1.61 | $2.08 | ||
Kansas City | Caracas | $1.54 | $1.90 | ||
Auckland | Caracas | $1.54 | $1.98 | ||
Santiago | Caracas | $1.00 | $1.26 | ||
Singapore | Atlanta | $1.73 | $2.35 | ||
Birmingham | Atlanta | $1.02 | $1.25 | ||
Frankfurt | Atlanta | $1.42 | $1.70 | ||
Mumbai | Atlanta | $1.57 | $2.23 | ||
Kansas City | Atlanta | $1.31 | $1.82 | ||
Auckland | Atlanta | $1.74 | $2.26 | ||
Santiago | Atlanta | $1.31 | $1.76 |
Fixed Costs of Capacity Increase or New Construction | ||
Current Plants | Additional Capacity | Cost |
Kansas City | 10000 | $605,000.00 |
Kansas City | 20000 | $985,000.00 |
Santiago | 5000 | $381,000.00 |
Santiago | 10000 | $680,000.00 |
Proposed Locations | Maximum capacity | Cost |
Auckland | 15,000 | $917,000.00 |
Auckland | 20,000 | $1,136,000.00 |
Birmingham | 15,000 | $962,000.00 |
Birmingham | 20,000 | $1,180,000.00 |
Frankfurt | 15,000 | $874,000.00 |
Frankfurt | 20,000 | $1,093,000.00 |
Mumbai | 15,000 | $750,000.00 |
Mumbai | 25,000 | $959,000.00 |
Singapore | 15,000 | $839,000.00 |
Singapore | 20,000 | $1,058,000.00 |
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