FIN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter9 1. Retrieve the file HWChapter9a and solve Problem 9-4 using the organizational design displayed in the worksheet file. Do not modify the fonts, format, or cell addresses 2. Follow the instructions in Cells A23:A31. The initial value in Cell D7 (2.00%) is automatically converted to a new value, the self-supporting growth rate. 3. Copy the self-supporting growth rate into Cell F7. Note: Use the Copy icon--do not manually type the growth rate. Cell F7 is formatted to display two decimal places, but more decimal places will be retained in Excel, enabling more precise financial results.) 4. Restore the original value of 2.00% in cell D7 . Then enter equations that solve for the values required in Cells F7:F9 and F18:F21. (Hint: Use the Copy icon.) In Cell F33, identify the dollar amount of growth in sales that does not require any external capital funding 1 PROBLEM 9-4 3 USING GOAL SEEK 4 5 Financial Data 6 Current sales 7 Forecasted growth in sales (assume a 2% growth rate as the initial guess) 8 Forecasted sales 9 Change in sales 10 Total assets that must increase to support the increase in sales 11 Required assets per dollar of sales 12 Spontaneous liabilities: payables + accruals 13 Spontaneous liabilities per dollar of sales 14 Profit margin 15Dividend payout ratio 16 17 Calculating AFN 18 Required Increase in Assets 19 Increase in Spontaneous Liabilities 20 Increase in Retained Earnings 21 Additional Funds Needed (AFN) Self- Supportin S5,000,000 Initial S5,000,000 2.00% S5,100,000 S100,000 S2,500,000 50.00% $700,000 14.00% 7.00% 80.00% S2,500,000 50.00% $700,000 14.00% 7.00% 80.00% S50,000 $14,000 $71,400 -$35,400 23 Excel's Goal Seek Function 24 Highlight cell D7 (forecasted growth in sales) 25 Data 26 What If Analysis 27 Goal Seek 28 Set cell: D21 (additional funds needed) 29 To: O 30 By changing cell: D7 (forecasted growth in sales) 31 OK 32 33 Identify the dollar amount of growth in sales that does not require external capital funding 34 35 36 37 38 FIN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter9 1. Retrieve the file HWChapter9a and solve Problem 9-4 using the organizational design displayed in the worksheet file. Do not modify the fonts, format, or cell addresses 2. Follow the instructions in Cells A23:A31. The initial value in Cell D7 (2.00%) is automatically converted to a new value, the self-supporting growth rate. 3. Copy the self-supporting growth rate into Cell F7. Note: Use the Copy icon--do not manually type the growth rate. Cell F7 is formatted to display two decimal places, but more decimal places will be retained in Excel, enabling more precise financial results.) 4. Restore the original value of 2.00% in cell D7 . Then enter equations that solve for the values required in Cells F7:F9 and F18:F21. (Hint: Use the Copy icon.) In Cell F33, identify the dollar amount of growth in sales that does not require any external capital funding 1 PROBLEM 9-4 3 USING GOAL SEEK 4 5 Financial Data 6 Current sales 7 Forecasted growth in sales (assume a 2% growth rate as the initial guess) 8 Forecasted sales 9 Change in sales 10 Total assets that must increase to support the increase in sales 11 Required assets per dollar of sales 12 Spontaneous liabilities: payables + accruals 13 Spontaneous liabilities per dollar of sales 14 Profit margin 15Dividend payout ratio 16 17 Calculating AFN 18 Required Increase in Assets 19 Increase in Spontaneous Liabilities 20 Increase in Retained Earnings 21 Additional Funds Needed (AFN) Self- Supportin S5,000,000 Initial S5,000,000 2.00% S5,100,000 S100,000 S2,500,000 50.00% $700,000 14.00% 7.00% 80.00% S2,500,000 50.00% $700,000 14.00% 7.00% 80.00% S50,000 $14,000 $71,400 -$35,400 23 Excel's Goal Seek Function 24 Highlight cell D7 (forecasted growth in sales) 25 Data 26 What If Analysis 27 Goal Seek 28 Set cell: D21 (additional funds needed) 29 To: O 30 By changing cell: D7 (forecasted growth in sales) 31 OK 32 33 Identify the dollar amount of growth in sales that does not require external capital funding 34 35 36 37 38