Lesson $3$ Case Study for Homework $-$

$As$ part $of$ the management $ofL3$ExxelTech Company, you$ve$ 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 will $be$ shown $in$ the $HW3$Template$)$ 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 $isto$ use the Template $as$ a guideline, and build a full month $by$ month profit plan for the coming year. Here are the first pass budget assumptions: $(k$$$,$ monthly amounts$)$

Expected Sales rate per month average over the year: $150$ unit$\frac{s}{m}$onth

Price will $be$ $$5,100$ per unit

There $isa2\%of$ Gross Sales charge for returns and bad debt

For the first pass, $it$ has been assumed that the Total Cost $of$ Goods Sold will $be60\%of$ Gross Sales

Sales People make $2\%of$ Gross Sales $as$ Commission

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.$

Employee Benefits are $30\%of$ Salaries, Wages and Commissions

Advertising

$$18$

Depreciation

$$16$

Other

$$7$

Genera$\frac{l}{A}$dministrative Salaries and wages $$17$

Payroll taxes are $4\mathrm{.}5\%of$ total Salaries, Wages and Commissions. $(Thisis$ matching $of$ tax withholding from paychecks that the firm must file with the IRS!$)$

Insurance $$7$

Rent $$9$

Utilities $$3$

Depreciation & amort. $$5$

Office supplies $$1$

Travel & entertainment $$4$

Postage $$2$

Equipment maint. & rent $$1$

Interest $$2$

Furniture & Equipment $$5$

The company corporate income tax rate $is35\%$

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 $a10\%$ annual net after tax income $as$ a percentage $of$ gross sales.