Due to economic recession a motor parts company is considering diversifying its operations by introducing either one or two or three new divisions. Please note that this company can also consider the option of maintaining its current structure (i.e. no new divisions). The owner forecasts the annual sales for the company based on the following formula:

Previous Years Sales * (1 + Base Sales Growth Rate)

Where n stands for the number of years away from the base year. For example, if 2010 is the base year, then the number n for 2018 is 8. The Base Sales Growth Rate is computed as: (n)/50.

The spreadsheet template on the last page shows this companys present financial position. For planning purposes, the Chief Financial Officer (CFO) prepares pro forma statements using the percentage-of-sales method. In line with this method, the following relationships have been developed:

• Cost of goods sold is about 60% of dollar sales. Use RAND() to generate a number between 59.5% and 60.5% as an estimate.

• Selling, general, and administrative expenses are

SG&A = $12,000 + * sales + $15,000 * number_of_new_divisions ^2.333

• In the percentage-of-sales method, dollar increases in revenues allow an increase in assets. For planning purposes, the CFO usually assumes that current assets (CA) change by (i.e. with respect to last years amount) 16% of the change in sales, and undepreciated fixed assets change by 35% of the change in sales.

• Depreciation accumulates yearly by $150,000 plus (1 + number_of_new_divisions)^5/7 / 150 of the increase in fixed assets. However, when there is no increase in fixed assets, depreciation accumulates yearly by $150,000 only.

• Current liabilities (CL) change by (i.e. with respect to last years amount) 15% of the change in sales.

• Long term debt (LTD) must be paid off before any long term investment (LTI) can be made.

LTD = (CA + net fixed assets) (CL + common equity). If LTD is negative, then LTD = 0

LTI is (CL + common equity) (CA + net fixed assets), if LTD is at most $100,000; otherwise, no LTI will be made.

• Net Fixed Assets = Fixed Assets Accumulated Depreciation

• Common Equity = Common Stock + Retained Earnings (RE)

• Total Assets = Current Assets + Net Fixed Assets + LTI

• The owner of this company decides to issue additional new common stock at 7.5% of the yearly SALES INCREASE if the current years sales amount is larger than the previous years sales amount by at least $1,500,000; otherwise, the additional new common stock will be issued at 5.8% of the yearly SALES INCREASE if current years sales amount is larger than the previous years sales amount by at least $700,000. Due to the generosity of the owner, the company will still issue additional new common stock at 2.5% of the yearly SALES INCREASE if only current years sales amount is at least as large as the previous years. However, if current years sales amount is less than one-half of the previous years sales amount, the company will not issue any additional new common stock. (Note that you are required to use the nested IF statement for this formulation.)

• Current Years Retained Earnings = Previous Years Retained Earnings + Net Income Dividends

• The policy for issuing dividends for the current year is based on the following Table: (Use nested IF statement for this formulation.)

Note that the Net Income (NI) increase rate, r_NI is defined as:

r_NI = (NI_c NI_p) / absolute value of NI_p

where NI_c is the current years Net Income and NI_p is previous years Net Income.

NI	NI < 0%	0% NI < 10%	10% NI < 20%	20% NI < 25%	25% NI
Dividend Rate	0.00%	1.50%	3.25%	5.30%	7.60%

The above Table tells us that if _NI is at least 25 percent, i.e. (NI_c NI_p)/(absolute value of NI_p) 25%, then dividends of 7.60% of current years NET INCOME will be issued. Similarly, when _NI is less than 25% but is at least 20%, dividends of 5.30% of current years NET INCOME will be issued. When 10% _NI < 20%, dividends of 3.25% of current years NET INCOME will be issued. If 0% _NI < 10%, then dividends of 1.5% of current years NET INCOME will be issued. Otherwise, no dividends will be issued.

• Total Liability & Equity = CL + LTD + Common Equity

• Financial rates are computed as follows:

CURRENT = Current Assets / Current Liability

PROFIT MARGIN = Net Income / Sales

RETURN ON TOTAL ASSETS = Net Income / Total Assets

RETURN ON TOTAL EQUITY = Net Income / (Total Liability & Equity (Current Liability + LTD))

• Tax computation is based on the tax rate schedules given below: (Note that you are required to use the nested IF statement for this formulation. When the gross income is negative, then no taxes need to be paid.)

		Tax Rates Schedules
If the amount of Gross Income is:		Then:
Over	But not over	Tax is			Of the amount Over
$0	$500,000	-	+	10%	$0
$500,000	$750,000	$50,000	+	16%	$500,000
$750,000	$1,000,000	$90,000	+	25%	$750,000
$1,000,000	-	$152,500	+	30%	$1,000,000

ASSIGNMENT:

• Create 3D pie graphs of the breakdown of the SALES dollar (i.e., Cost of Goods Sold, Selling, General and Administrative Expense, Taxes, Dividends, and Net Income after Dividends) for the year 2019 for each of the division addition options. Explode NET INCOME AFTER DIVIDENDS on the 3D pie chart. Each 3D pie graph must include the label and percentage for each component, and the percentage figure must contain two decimals. (If there is any negative component, copy these five components to any available range of cells and convert that negative number to 0 before creating your pie chart.)

• Create 2D line graphs showing PROFIT MARGIN, RETURN ON TOTAL ASSETS and RETURN ON TOTAL EQUITY for the years 2017 through 2022 for each of the division addition options. Label the x-axis as years and y-axis as financial ratios (%).

• The figures in your worksheet (except financial ratios) must be in the format of , with 0 decimal places. The formats for the financial ratios: CURRENT is in two decimals (i.e. xx.xx), while BASE SALES GROWTH RATE, DIVIDEND RATES, PROFIT MARGIN, RETURN ON TOTAL ASSETS, and RETURN ON TOTAL EQUITY are all in % with two decimals (i.e. xx.xx%).

• Insert the names of each member of your group in the header section.