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