Question

USE excel

contains the 2018 financial statements of Zeiber Corporation. Forecast Zeiber's 2019 income statement and balance sheets. Use the following assumptions: (1) Sales grow by 6%. (2) The ratios of expenses to sales, depreciation to fixed assets, cash to sales, accounts receivable to sales, and inventories to sales will be the same in 2019 as in 2018. (3) Zeiber will not issue any new stock or new long-term bonds (4) The interest rate is 11% for long-term debt and the interest expense on long-term debt is based on the average balance during the year (5) No interest is earned on cash. (6) Regular dividends grow at an 8 % rate . Calculate the additional funds needed (AFN). If new financing is required, assume it will be raised by drawing on a line of credit with an interest rate of 12%. If surplus funds are available, pay a special dividend Key Input Data: Used in the forecast Tax rate 40% Dividend growth rate Rate on notes payable-term debt, r 8% 9% Rate on long-term debt, ra Rate on line of credit, roc 11% 12% a. What are the forecasted levels of the line of credit and special dividends? (Hints: Create a column showing the ratios for the current year; then create a new column showing the ratios used in the forecast. Also, create a preliminary forecast that doesn't include any new line of credit or special dividends. Identify the financing deficit or surplus in this preliminary forecast and then add a new column that shows the final forecast that includes any new line of credit or special dividend.) Begin by calculating the appropriate historical ratios in Column E. Then put these ratios and any other input ratios in Column G Forecast the preliminary balance sheets and income statements in Column H. Don't include any line of credit or special dividend in the preliminary forecast. 2019 Preliminary Balance Sheets (December 31, in thousands of dollars 2019 Final forecast (includes special dividend or LOC) forecast (doesn't include special dividend or LOC) 2019 Input Forecasting basis 2018 ratios Assets: $18,206 $100,133 $45,515 $163,854 $182,060 $345,914 Cash % of sales Accounts Receivable Inventories % of sales % of sales Total current assets Fixed assets % of sales Total assets Liabilities and equity Accounts payable $31,861 $27,309 $0 Zero in preliminary forecast $59,170 $120,000 Same as previous $179,170 $60,000 Same as previous $106,745 $166,745 $345,914 % of sales % of sales Accruals Line of credit Total current liabilities Long-term debt Total liabilities Common stock Retained Earnings Total common equity Total liabilities and equity PreviousAddition to RE Balance Sheet Check Total Assets Total Liabilities & Equity $0 $0 Identify Financing Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) Increase in long-term bonds, preferred stock and common stock Net income (in preliminary forecast) minus regular common dividends Increase in financing - Increase in total assets Amount of financing deficit or surplus: Use if) statement. Use if) statement. If deficit in financing (negative), show the amount for the line of credit If surplus in financing (positive), show the amount of the special dividend a. What are the forecasted levels of the line of credit and special dividends with a 6% qrowth in sales? Assume that any draw on the line of credit will be made on the last day of the year, so there will be no additional interest expense for the new line of credit (assume a value of zero for G:H60). Required line of credit Special dividends Note: copyand paste exact values from H104:H105 when sales growth in F55 = Input the line of credit amount and the special dividend amount into the appropriate cells (H84,, H66) to make sure the balance sheet balances (H92-0) b. Now assume that the growth in sales is only 3% (do this by changing the growth rate in Cell F58) What are the forecasted levels of line of credit and special dividends? Assme that any draw on the line of credit will be made on the last day of the year, so there will be no additional interest expense for the new line of credit (assume a value of zero for G:H60) Required line of credit Special dividends Note: copy and paste exact values from H104:H105 when sales growth in F55 = Input the line of credit amount and the special dividend amount into the appropriate cells (H84,, H66) to make sure the balance sheet balances (H92-0). c. Now assume that the growth in sales is 6% (do this by changing the growth rate in Cell F58). What are the forecasted levels of line of credit and special dividends? Assume that any draw on the line of credit will be made throughout the year, so there will be additional interest expense for the new line of credit. The interest expense will be calculated based on the average line of credit during the year. Required line of credit Special dividends Note: copy and paste exact value from $0 H84 after using the Goal Seek function when sales growth in F55 = 6 % Input the line of credit amount and the special dividend amount into the appropriate cells (H84, H66) to make sure the balance sheet balances (H92=0). If H92 0, then use Goal Seek to get it too balance How to use the goal seek function to find the exact amount: 1) Type your initial estimate for the line of credit from G104 into cell H84 You'll notice that the balance sheet does not balance due to the financing feedback (see check in H92). 2) Go to Data> What-lf Analysis Goal Seek... 3) You want to find the exact amount for the line of credit that makes the balance sheet balance (i.e., total assets total liabilities total equity) Set cell: This is your "goal" cell. H92 is the cell that checks whether or not the balance sheet balances. The balance sheet is balanced when the check (H92) is equal to zero (0) To Value: By changing cell: We want to change the amount of the line of credit (H84) until the balance sheet balances. Goal Seek Status Goal Seek ? X 7 X Goal Seeking with Cell H92 SH$92 Step Set cell found a solution To yalue Pause Target value: 0 SHS84 By changing cell Current value: $0 OK Cancel OK Cancel d. Now assume that the growth in sales is only 3 % ( do this by changing the growth rate in Cell F58) What are the forecasted levels of line of credit and special dividends? Assume that any draw on the line of credit will be made throughout the year, so there will be additional interest expense for the new line of credit. The interest expense will be calculated based on the average line of credit during Required line of credit Special dividends Note: copy and paste exact values from H66 after using the goal seek (if required) when sales growth in F55 = Input the line of credit amount and the special dividend amount into the appropriate cells (H84, H66) to make sure the balance sheet balances (H92-0). If H92 0, then use Goal Seek to get it to balance Why does the financing feedback impact the line of credit but not the special dividends?

Answer 1

Oncome statcments ol picl,2ol prel Dec 31, in'o00sol 4) aol8 Forecasting aon input foecast alios (thout incl-spl fortecast Basis Spl.div div oY Loc) Sales 455150 sales grocoth G Expenss(addep1 &amort) 36878-/ ol sales 851 48 2459 4859 ul0010 4t0o10 Depn. & Amort 14565.of FIA 15439 15439 EBIT 53-10 56130 56930 Inl ep.On LTdebt i88o interest 20000 3a00 13 a 00 Tale Int exp on Line of C C CTedit EBT 41891 43130 43130 Taycs at yo. 1613 Net income a509G 26238 a6 2 38 divideots Common 255G10wth 87 355 13558 CReqular div.) Spl.div Addition to Rle 2680 I3680 2542

FOTccasting 2019 ioputaol9 prel ao19 prel YGlios Balance sheet basis FOTCcast Fo1e cast Ccof thout Cinc. spl div Spl-div 07 Loc Or Loc) Assets Cash 41. . of Sales 18aoG ACCounts ReceiVoble lool33 I0614 /. of sales 06lyl loventories . ofSales 48 a46 4246 45515 Total cuT1eoi assets1638 54 173685 3685 Fiaed assets g a060. 0l sales 407 92984 192984 Total asselS 345914 366669 366669 Ligbilities &equitq ACCOunts payale 33113 33413 31861 . of Sales ACCaYuals 67. 2894 2130 .of sales Line of CYedit 45a4 Total cuTTent 591H0 67244 62120 ligbilities Long -term debt ao000 Same as 20000 20000 Pievi ous Total liabilities 18Q120 18744 Common StoCk 60000 Same as 6 0000 Go000 pievious Relained Caniogs 106745 pievt Addns. 106145 1q425 19425 to Rle a6g0

94a5 Total common 66145 equity 362ly5 366669 Tolal iabilties З45915 Equity nc. In Spontaneous lHabilities AcCounls payabe 33143-3186 912 a894- a30=| 1639 3551 ACCYuals Add Inc in L bonds C Pief & Common Stocks AddiNet income- 36938-13558- 089e1 Teqal ar div l623 IncTease in inancing Less:incease in 366669 ao155 345914 total assets Aml o tinaociog |4524 deficit 4524 Loc