Finish the Problem Please: Download spreadsheet Ch$09$ P$10$ Build a Model$-336$a$85.$xlsx

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.$)$

Required line of credit

$

thousand

Special dividends

$

thousand

b$.$ Now assume that the growth in sales is only $4\%.$ What are the forecasted levels of the line of credit and special dividends?

Required line of credit

$

thousand

Special dividends

$

thousand

Important Numbers provided from excel:

Sales growth rate $7\%$ Dividend growth rate $9\%$ Interest rate on long$-$term debt $12\%$ Interest rate on line of credit $13\%$ Tax rate $25\%$ a$.$ Determining the forecasted levels of the line of credit and special dividends Zeiber's Projected Financial Statements $($Thousands of Dollars$)1.$ Balance Sheets $2021$ Assets: Cash $$18,970\mathrm{.}00$ Accounts receivable $104,335\mathrm{.}00$ Inventories $37,940\mathrm{.}00$ Total current assets $$161,245\mathrm{.}00$ Fixed assets $189,700\mathrm{.}00$ Total assets $$350,945\mathrm{.}00$ Liabilities and equity Accounts payable $$28,455\mathrm{.}00$ Accruals $23,712\mathrm{.}50$ Line of credit $0\mathrm{.}00$ Total current liabilities $$52,167\mathrm{.}50$ Long$-$term debt $120,000\mathrm{.}00$ Total liabilities $$172,167\mathrm{.}50$ Common stock $50,000\mathrm{.}00$ Retained earnings $128,777\mathrm{.}50$ Total common equity $$178,777\mathrm{.}50$ Total liabilities and equity $$350,945\mathrm{.}002.$ Income Statements $2021$ Sales $$474,250\mathrm{.}00$ Expenses $($excluding depr. & amort.$)398,370\mathrm{.}00$ Depreciation and amortization $15,176\mathrm{.}00$ EBIT $$60,704\mathrm{.}00$ Interest expense on long$-$term debt $11,640\mathrm{.}00$ Interest expense on line of credit $0\mathrm{.}00$ EBT $$49,064\mathrm{.}00$ Taxes $(25\%)12,266\mathrm{.}00$ Net income $$36,798\mathrm{.}00$ Regular common dividends $$18,407\mathrm{.}00$ Special dividends $0\mathrm{.}00$ Addition to retained earnings$$ $$18,391\mathrm{.}00$