You boss needs help again. With the partial model please forecast Zieber’s 2017
ID: 2614962 • Letter: Y
Question
You boss needs help again.
With the partial model please forecast Zieber’s 2017 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 2017 as in 2016. (3) Zieber 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%. 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. If surplus funds are available, pay a special dividend.
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 thatdoesn’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.)
Now assume that the growth in sales is only 3%. What are the forecasted levels of the line of credit and special dividends?
3 Start with the partial model in the file Ch12 P10 Build a Model.xis x on the textbook's Web site, which contains 4 the 2016 financial statements of Zieber Corporation. Forecast Zeiber's 2017 income statement and balance 5 sheets. Use the following assumptions: (1) Sales grow by 6%. (2) The ratios of expenses to sales, 6 depreciation to fixed assets, cash to sales, accounts receivable to sales, and inventories to sales will be the same in 2017 as in 2016. (3) Zeiber will not issue any new stock or new long-term bonds. (4) The interest 8 rate is 11% for long-term debt and the interest expense on long-term debt is based on the average balance 9 during the year. (5) No interest is earned on cash. (6) Regular dividends grow at an 8% rate. (6) Calculate the 0 additional funds needed (AFN). If new financing is required, assume it will be raised by drawing on a line of 11 credit with an interest rate of 12%. Assume that any draw on the line of credit will be made on the last day of 12 the year, so there will be no additional interest expense for the new line of credit. If surplus funds are 14 Key Input Data Used in the forecast 16 Tax rate Dividend growth rate 18 Rate on notes payable-term debt, rstd 19 Rate on long-term debt, rd 20 Rate on line of credit, rioc 40% 8% 9% 11% 12% 22 a. What are the forecasted levels of the line of credit and special dividends? (Hints: Create a column 23 showing the ratios for the current year; then create a new column showing the ratios used in the forecast 24 Also, create a preliminary forecast that doesn't include any new line of credit or special dividends. Identify 25 the financing deficit or surplus in this preliminary forecast and then add a new column that shows the final 26 forecast that includes any new line of credit or special dividend.) 27 28 Begin by calculating the appropriate historical ratios in Column E. Then put these ratios and any other input 29 ratios in Column G 31 Forecast the preliminary balance sheets and income statements in Column H. Don't include any line of credit 32 or special dividend in the preliminary forecast 34 Atter completing the preliminary forecast of the balance sheets and income statement, go to the area below the preliminary forecast and identify the financing deficit or surplus. Then use Excel's IF statements to specity the amount of any new line of credit OR special dividend (you should not have a new line of credit 37 AND a special dividend, only one or the other) 38 39 After specifying the amounts of the special dividend or line ot credit, ereate a second column (U) for the final 40 forecast next to the column for the preliminary forecast (H). In this final forecast, be sure to include the 41 etfect of the special dividend or line of credit 42 43Explanation / Answer
2016(in $) 2016 Historical Ratios Forecasting basis 2017 Input ratios 2017 preliminary forecast(doesn’t include special dividend or LOC) 2017 preliminary forecast(includes special dividend or LOC) Workings If Sales growth rate is 3% 2017 preliminary forecast(doesn’t include special dividend or LOC) 2017 preliminary forecast(includes special dividend or LOC) Sales 455150 Growth 455150*1.06 4,82,459 482459 455150*1.03 468805 468805 Expenses(excluding depr and amort) 386878 386878/455150 = 85% % of sales 482459*85% 4,10,090 410090 468805*85% 398484 398484 Depreciation and Amortization 14565 14565/182060 = 8% % of fixed assets 192983.60*0.08 15,439 15439 187522*8% 15002 15002 EBIT 53708 56,930 56930 55318 55318 Interest Expense on long-term debt 11880 Interest rate * average debt during the year 11%*(120000) 13,200 13200 13200 13200 Interest Expense on line of credit 0 Since made at the last day of the year - 0 0 0 EBT 41828 43,730 43,730 42118 42118 Taxes (40%) 16731 17,492 17,492 16847 16847 Net Income 25097 26,238 26,238 25271 25271 Common dividends(regular dividends) 12554 Growth 12554*1.08 13,558 13,558 13558 13558 Special dividends Zero in preliminary forecast 3110 3110 Addition to retained earnings 12543 12,680 12,680 8603 8603 Balance Sheets(December 31, in thousands of dollar) 2016(in $) 2016 Historical Ratios Forecasting basis 2017 Input ratios 2017 preliminary forecast(doesn’t include special dividend or LOC) 2017 preliminary forecast(includes special dividend or LOC) Workings 2017 preliminary forecast(doesn’t include special dividend or LOC) 2017 preliminary forecast(includes special dividend or LOC) Assets Cash 18206 18206/455150 = 4% % of sales 4%*482459 19298 19298 4%*468805 18752 18752 Accounts receivable 100133 100133/455150 = 22% % of sales 22%*482459 106141 106141 22%*468805 103137 103137 Inventories 45515 45515/455150 = 10% % of sales 10%*482459 48246 48246 10%*468805 46881 46881 Total current assets 163854 173685 173685 168770 168770 Fixed assets 182060 182060/455150 = 40% % of sales 40%*482459 192984 192984 40%*468805 187522 187522 Total assets 345914 366669 366669 356292 356292 Liabilities: Accounts payable 31861 31861/455150 =7% % of sales 7%*482459 33772 33772 7%*468805 32816 32816 Accruals 27309 27309/455150 = 6% % of sales 6%*482459 28947 28947 6%*468805 28127 28127 Line of credit 0 Zero in preliminary forecast 0 4525 0 Total current liabilities 59170 62719 67244 60944 60944 Long Term debt 120000 Previous 120000 120000 120000 120000 Total Liabilities 179170 182719 187244 180944 180944 Common stock 60000 Previous 60000 60000 60000 60000 Retained earnings 106745 Previous + Additions to retained earnings 106745+12680 119425 119425 106745+11713 118458 115348 106745+8603 Total common equity 166745 179425 179425 178458 175348 Total Liabilities and equity 345914 362144 366669 359402 356292 Identify financing deficit or Surplus If Sale growth is 3% Increase in Spontaneous Liabilities(Accounts payable and accurals) 3550 62720-59170 1775 60945-59170 Add: Increase in long term bonds,preferred stock and common stock 0 0 Add: Net Income(in preliminary forecast) minus regular common dividends 12680 11713 Increase in financing 16230 13488 Less: Increase in total assets 20755 10378 Amount of financing or surplus -4525 3110 If deficit in financing(negative) show the amount for the line of credit 4525 If surplus in financing(positive) show the amount for the dividend 0 3110 a) What are the forecasted levels of the line of credit and special dividends Required line of credit 4525 Special dividends 0 b) Now assume that the growth in sales is only 3% What are the forecasted levels of the line of credit and special dividends Required line of credit Special dividends 3110
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.