please Help by showing me on XL how to fill up the Balance Sheet, and anwer question a. and b. PLEASE SHOW WORK

AutoSave of FIN 432 module_9a ss e Search file Home Insert Draw Page Layout Formulas Data Review View Help Arial 2 Wrap Text Percentage ~10 - A A BIU ilil HI Merge & Center $ - %98- Conded Formatte pboard Font Alignment 5 Number X B D F H Module 09. Student Ch 09-10 Build a Model Zieber Corporation's 2012 financial statements are shown below. Forecast Zeiber's 2013 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 2013 as in 2012. (3) Zeiber will not issue any new stock or new long-term bonds. (4) The interest rate is 9% for short-term debt and 11% for long-term debt. (5) No interest is earned on cash. (6) Dividends grow at an 8% rate. (6) Calculate the additional funds needed (AFN). If new financing is required, assume it will be raised as notes payable. Assume that any new notes payable will be borrowed on the last day of the year, so there will be no additional interest expense for the new notes payable. If surplus funds are available, pay a special dividend. a. What are the forecasted levels of notes payable and special dividends? Key Input Data: Used in the forecast Tax rate 40% Dividend growth rate 8% S-TT 9% LOTTO 11% Sales Growth Rate December 31 Income Statements: (in thousands of dollars) 6% Sales Expenses (excluding depr. & amort.) EBITDA Depreciation and Amortization EBIT Na internet CURAREA Ch 09-10 Build a Model Solution Forecasting 2012 2013 2012 basis Ratios Inputs $455,150 Growth 6.00% $386,878 % of sales 85.000% 85.000% $68,273 $14,565 % of fixed assets 8.000% 8.00% $53,708 644 con Internet unto whominninn nfun dat 2013 Forecast $482,459 $410,090 $72,369 $0 $72,369 en D F G H 2013 Inputs 6.00% 85.000% B 25 Sales Growth Rate 6% 26 December 31 Income Statements: 27 (in thousands of dollars) 28 29 30 Sales 31 Expenses (excluding depr. & amort.) 32 EBITDA 33 Depreciation and Amortization 34 EBIT 35 Net Interest Expense 36 EBT 37 Taxes (40%) 38 Net Income 39 Common dividends (regular dividends) 40 Special dividends 41 Addition to retained earnings (DRE) 42 43 Forecasting 2012 2012 basis Ratios $455,150 Growth $386,878 % of sales 85.000% $68,273 $14,565% of fixed assets 8.000% $53,708 $11,880 Interest rate x beginning of year debt $41,828 $16,731 $25,097 $12,554 Growth 8.00% 2013 Forecast $482,459 $410,090 $72,369 SO $72,369 $0 $72,369 F $28,948 $43,421 $13,558 $0 $29,863 8.00% $0 $12,543 2012 Ratios 2013 2013 Inputs Without AFN AFN With AFN 45 December 31 Balance Sheets 46 (in thousands of dollars) 47 Forecasting 48 2012 basis 49 Assets 50 Cash $18,206 % of sales 51 Accounts Receivable $100,133 % of sales 52 Inventories $45,515 % of sales 53 Total current assets $163,854 54 Fixed assets $182,060 % of sales 55 Total assets $345,914 56 57 Liabilities and equity 58 Accounts payable $31,861 % of sales 50 herre 677 nfslae Ch 09-10 Build a Model Solution Edit Type here to search 2012 Ratios 2013 Inputs 2013 AFN Without AFN With AFN 45 December 31 Balance Sheets 46 in thousands of dollars) 47 Forecasting 48 2012 basis 49 Assets: 50 Cash $18,206 % of sales 51 Accounts Receivable $100,133 % of sales 52 Inventories $45,515 % of sales 53 Total current assets $ 163,854 54 Fixed assets $182,060 % of sales 55 Total assets $345,914 56 57 Liabilities and equity 58 Accounts payable $31,861 % of sales 50 Aceruals $27,309 % of sales 60 Notes payable $0 Previous 61 Total current liabilities $59,170 62 Long-term debt $120,000 Previous 63 Total liabilities $179,170 64 Common stock $60,000 65 Retained Earnings $106,745 Previous + ARE 66 Total common equity $166,745 67 Total liabilities and equity $345,914 68 50.000 69 Total assets 70 Planned liabilities and equity 71 Additional funds needed (AFN) - 72 73 Required additional notes payable 74 Special dividends 75 76 SO $0 50 77 a. What are the forecasted levels of notes payable and special dividends? 78 79 Required additional notes payable Note: we copied values from G73.674 when sales growth in 30 % 80 Special dividends 81 Bb. Now assume that the growth in sales is only 3%. What are the forecasted levels of notes payable and special B3 dividends? Note: we copied values from 073674 when sales growth in 30 - 3% 85 Required additional notes payable 36 Special dividends 87 89 90 90