As part of the management of L3ExxelTech Company, youve been asked to prepare a first pass budgeted income statement for the coming year. L3ET produces a relatively high priced technology product ($5,100 each) and requires a direct commissioned sales force to support sales levels. The company has a seasonal pattern of sales as shown below, selling more in the spring and summer than in the fall and winter.

0.8

0.8

0.9

1

1.1

1.2

1.3

1.2

1.1

1

0.8

0.8

12

Based on a review of the past years actual costs, the following monthly cost amounts have been judged to apply for the first-pass budget. Your assignment is to use the Template as a guideline, and build a full month by month profit plan for the coming year.

Total Sales

$765

Here are the first pass budget assumptions: (k$, monthly amounts)

1) Expected Sales rate per month average over the year: 150 units/month

2) Price will be $5,100 per unit

3) There is a 2% of Gross Sales charge for returns and bad debt

4) For the first pass, it has been assumed that the Total Cost of Goods Sold will be 60% of Gross Sales

5) Sales People make 2% of Gross Sales as Commission

6) Based on the current complement of Sales People, the monthly Salaries and Wages for Sales People are $49k per month. This includes all raises starting January 1.

7) Employee Benefits are 30% of Salaries, Wages and Commissions

8) Advertising $18

9) Depreciation $16

10) Other $7

11) General/Administrative Salaries and wages $17

12) Payroll taxes are 4.5% of total Salaries, Wages and Commissions. (This is matching of tax withholding from paychecks that the firm must file with the IRS!)

13) Insurance $7

14) Rent $9

15) Utilities $3

16) Depreciation & amort. $5

17) Office supplies $1

18) Travel & entertainment $4

19) Postage $2

20) Equipment maint. & rent $1

21) Interest $2

22) Furniture & Equipment $5

23) The company corporate income tax rate is 35%

The result of the profit plan is a monthly profit plan and an annual total profit with the forecast average sales level. When you have completed the spreadsheet, note the total after tax profit. Then use goal seek to find the monthly average sales level in units that produces a 10% annual net after tax income as a percentage of gross sales.