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, selling more in the spring and summer than in the fall and winter. (The monthly seasonality factors are attached in Template below) 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. Using Template as a guideline, build a full month by month profit plan for the coming year.

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.

L3ExxelTech Company Pro-Forma Budgeted Income Statement Sales Forecast Seasonal Factor Selling Price (ks) (all numbers in k$ ) 1 REVENUE Gross sales Less sales Less sales returns and bad debt Net Sales COST OF SALES Total Cost of Goods Sold Gross Profit (Loss) OPERATING EXPENSES Selling Salaries and wages Commissions Employee Benefits Advertising Depreci Other Total Selling Expenses General/Administrative Salaries and wages \begin{tabular}{|r|r} \hline Employee benefits & 0.3 \\ \hline Payroll taxes (all S \& W + Comm.) & 0.05 \end{tabular} Insurance Rent Utilities Depreciation \& amortization Office supplies Travel \& entertainment Postage 2 Equipment maintenance \& rental Interest Furniture \& Equipment Total General/Administrative Expenses Total Operating Expenses Net Income Before Taxes Taxes on income Net Income After Taxes 0.35