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: n+20 Previous Year's Sales * (1 + Base Sales Growth Rate) 20 vn 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 company's 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. O Selling, general, and administrative expenses are SG&A = $12,000 + (1+number_of_new_divisions)1.95 20*(1 + number_of_new_divisions) 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 year's 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 year's amount) 15% of the change in sales. O 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. D Net Fixed Assets = Fixed Assets Accumulated Depreciation D 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 year's sales amount is larger than the previous year's 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 year's sales amount is larger than the previous year's 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 year's sales amount is at least as large as the previous years. However, if current year's sales amount is less than one-half of the previous year's 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.) D Current Year's Retained Earnings = Previous Year's 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, rniis defined as: rni = (NIc - Nip) / absolute value of NIR where Nic is the current year's Net Income and Nip is previous year's Net Income. INI 25%, then dividends of 7.60% of current year's NET INCOME will be issued. Similarly, when r ni is less than 25% but is at least 20%, dividends of 5.30% of current year's NET INCOME will be issued. When 10% 25%, then dividends of 7.60% of current year's NET INCOME will be issued. Similarly, when r ni is less than 25% but is at least 20%, dividends of 5.30% of current year's NET INCOME will be issued. When 10%