ASSIGNMENT:

• Use Data Table to determine the effect on the NET INCOME AFTER DIVIDENDS for year 2019 by varying the price from $5 to $20 in steps of 0.5 and advertising expenses (in the unit of thousand) from $50 to $250 in steps of 10. (Format the data with 0 decimals.) Print the data table (with row and column headings) for each division option. Decide the best PRICE and ADV combination for each division option.
• Create and print 3D pie graphs of the breakdown of the SALES dollars (i.e., Cost of Goods Sold, Selling, General, and Administrative Expense, Taxes, Dividends, and Net Income After Dividends) for the year 2020 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.

Joy Appliance Corporation is considering diversifying its operations by introducing either one, 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). Before drawing his plan, the owner likes to understand the effects of the pricing and advertising expenditure strategies on the profit after dividends.

After analyzing past five years' data, the owner obtained the following price-advertising-sales relationships for openers for year 2019: (ADV denotes advertising budget, and PRICE denotes the unit price. We assume the Quantity Sold yielded from the following formula is in the unit of thousand.)

Quantity Sold for Openers

= e^7.61 - 100 * PRICE^2.15 + 200 * PRICE * ADV^1/2.2 - 100 * (2*LN(ADV))^1.92 + 0.4 * ADV; however, if the Quantity Sold for Openers < 0, then 0.

-Sales for 2019 = Quantity Sold * Selling Price

-Cost of goods sold is anywhere in the range of (651)% of dollar sales.

-Selling, general, and administrative expenses are

SG&A = * sales + $10 * (1+ number_of_new_divisions²)

-Gross Income = Sales COGS SG&A

-Net Income = Gross Income Tax

-Net Income After Dividends = Net Income Dividends

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

-Depreciation accumulates yearly by $110 (already in the unit of thousand) plus of the increase in fixed assets. However, when there is no increase in fixed assets, depreciation accumulates yearly by $100 (already in the unit of thousand) only.

-Current liabilities (CL) change by (i.e., with respect to last year's amount) 15% of the change in sales.

-Long term debt (LTD) must be paid until it is no more than $50 (already in the unit of thousand) before any long term investment (LTI) can be made.

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

LTD= (CA+FA-AD) (CL +CS + RE)

C37 = (30+C31-C32) (C36+C38+C39)

LTI is (CL + common equity) - (CA + net fixed assets), if LTD is no more than $50 (already in the unit of thousand); otherwise, no LTI will be made.

-The owner of this company decides to issue additional new common stock at 5.15% of the yearly SALES increase if current year's sales amount is larger than the previous year's sales amount by at least $1,950 (already in the unit of thousand), otherwise, the additional new common stock will be issued at 3.35% of the yearly SALES increase. However, if current year's sales amount is less than the previous year's sales amount, the company will not issue any additional new common stock.

-Current Year's Retained Earnings = Previous Year's Retained Earnings + Net Income - Dividends

-The policy for issuing dividends for the current year is: if current year's NET INCOME has increased more than 15 percent, i.e.,

then dividends of 7.5% of NET INCOME will be issued. If the growth rate of net income is between 0% and 15%, i.e.,

then dividends of 3.5% of NET INCOME will be issued. Otherwise, dividends of 0% of NET INCOME will be issued.

However, if the current year's Net Income is less than or equal to 0, the company cannot issue any dividend. (HINT: You need to use the nested IF concept.)

Tax Rate Schedules

If the amount of Then:

Gross Income is:

Over But Not Tax is Of the Amount

Over Over

$0 $600 10% $ 0

600 1,000 $60 + 18% 600

1,000 -- $132 + 30% 1,000