thats excel

This mint-case gives you another look at the finaneial forecasting process. Find below the 2019 financial statements of Ziober Corporation. By completing the shaded cells, you can forecast Zelber's 2020 income statement and balance shoets. Please note the hints providedi Use the following assumptions: (1) Sales grow by 6\%. (2) The ratlos of oxpenses to sales, depreelation to fixed assots, cash to sales, accounts recelvable to sales, and inventorles to sales will be the same in 2020 as in 2019 . (3) Zelber will not lssue any new stock or new long-term bonds. (4) The interest rate is 11% for long-tom dabt and the interent expense on long-term debt is based on the average balance during the year. (5) No interest is eamed on cash. (5) Regular dlvidends grow at an 8% rate, (5) Caleulate 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%. 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 eredit. If surplus funds are avallable, pay a special divldend. 23 3. What are the foreeasted levele of the line of credis and special dividiends? (Hints: Create a column ahowing the ratios for the current 24 year; then ereate a new column showing the ratios used in the forecaat. Also, ereate a prellminary forecast that doesn't include any new line of credit or spectal dividends. Identily the finaneing defleit or murplus in this preliminary forecast and then add a now column. that shows the final forecast that includes any new line of eredit or speciat dividend.) Begin by caleulating the appropriate historioal ratios in Column E. Then put these ratios and any other input ratios in Column C. Forecant the preiliminary baiance sheets and income statements in Column H. Dent include any line of credit or apecial dividend in the preliminary forecast. After completing the preliminary forecast of the balance sheets and income statement, go to the area below the preliminary forecast and identify the finaneing deficit or surplus. Then use Excers iF statements to apeclly the amount of any new line of eredit OR spocial dividend (you should not have a new line of credit AND a special dividend, only one or the othar). Affer specifying the amounts of the special dividend or line of credit, ereate a second column (1) for the final forecast next to the column for the preliminary forecast (H). In this final forecast, be sure to include the effect of the special dividend or line of eredit. Income Statements: (December 31, in thousands of dollars) Potontially AFN 2020 Proliminary forecast (doesn't