Lesson $3$ Case Study for Homework $$

As part of the management of L$3$ExxelTech Company, you$$ve been asked to prepare a first pass budgeted income statement for the coming year. L$3$ET 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 HW$3$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 is to 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$)$

$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 $$49$k 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\mathrm{.}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. Use Goal Seek and determine the average monthly sales level that corresponds to a $12\%$ annual net after tax income as a percentage of gross sales.

$1.197$

$2.185$

$3.207$

$4.177$ For the base case, what is the annual net profit after tax in k$$?$

$1.$ $$790$

$2.$ $$890$

$3.$ $$590$

$4.$ $$690$

Use Goal Seek to determine the minimum number of units that must be sold in any month so that net after tax income will not be negative in that month.

$1.94$

$2.102$

$3.117$

$4.85$