Based on the recommendation of some well-known Consultants, the CEO would like to improve profitability. As the CFO, you need to improve net margin (%), defined as Net income as % of sales.

	%	Dollars
Sales
Cost of Goods Sold
Gross Profit
Operating Expenses
Sales and Marketing
General and Administrative
Depreciation
Salaries
Total Operating Expenses
Income Before Taxes
Income Taxes
Net Income

Assumptions

Sales for the year are $1,000,000.

Cost of goods sold is 10% of sales.

Sales and marketing expenses are 11.5% of sales.

General and administrative expenses are 9% of sales.

Depreciation expense is $ 200,000 per year.

Salaries are $ 150,000 per year.

Tax rate is 15%.

Note that: Gross Profit = Sales COGS, IBT = Gross Profit total operating expenses, Net income = IBT tax

Single year optimization

You could increase sales, lower the cost of goods, or lower salaries- including yours. You need to do any one of these in the first year;

What should be the new sales in Year1 to achieve a net margin of 30%

What is the new COGS % to achieve a net margin of 30%

What is the new salary to achieve a net margin of 30%

Multi year

Starting with base scenario, what should be the annual sales growth in Year1 thru 4, to achieve a net margin of 39% for the year 4? Note that the base scenario assumed 5% annual growth for all years.

Start with income statements in Excel. Also refer to Goal Seek, in

http://www.homeandlearn.co.uk/excel2007/Excel2007.html

The website also has a note on relative and absolute copy.

Add Alternative scenario sheets to the Excel file.

-

Your Excel file should have 5 sheets, starting with a base scenario, 3 one year scenarios, and finally, multi year.

You are allowed to do the multi year with 'trial and error'.

-