Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page.

A company with annual sales of $22,000,000 is considering changing its payment terms from net 40 to net 30 to encourage customers to pay more promptly. The company forecasts that customers would respond by paying on day 32 rather than day 44 as at present (assume a 360 day year) but would decrease their purchases by $400,000 per year. The company also forecasts that its idle cash balance would decrease by $80,000 and administrative costs would be reduced by $30,000 per year. The company's variable costs average 62% of sales, it is in the 35% marginal tax bracket, and it has an 8% cost of capital.

Part A: Calculate the incremental cash flows from accepting this proposal, and organize your cash flows into a cash flow spreadsheet.

Part B: Calculate the proposal's NPV, IRR, and NAB.

Part C: Should the company shorten its payment terms?

D E F G H. J A B 1 Chapter 12 Homework 2 3 A Old investment in A/R: 4 Daily sales 5 Average age of A/R 6 Contribution margin % 7 Old investment in A/R 8 9 New investment in A/R: 10 Daily sales 11 1 Average age of A/R 12 Contribution margin % 13 New investment in A/R 14 15 Net decrease in A/R 16 17 Profit on change in collection period: 18 New daily sales 19 Change in average age of A/R 20 Contribution margin % 21 Profit on change 22 23 24 Time Zero Amounts 25 Change in A/R 26 Profit on change in collection period 27 Other W/C change 28 Total 29 30 31 32 33 B NPV Calculation: 34 Present value of cash inflows 35 Present value of cash outflows 36 Net Present Value 37 38 IRR Calculation: 39 Annual cashflow 40 Investment in A/R 41 Internal Rate of Return 42 43 NAB Calculation: 44 Allowed annual cost 45 Actual annual cost 46 Net Annual Benefit Years 1 to Infinity Admin costs Bad debts Contribution margin Discounts Tax on above Total 27 D E F G H. J A B 1 Chapter 12 Homework 2 3 A Old investment in A/R: 4 Daily sales 5 Average age of A/R 6 Contribution margin % 7 Old investment in A/R 8 9 New investment in A/R: 10 Daily sales 11 1 Average age of A/R 12 Contribution margin % 13 New investment in A/R 14 15 Net decrease in A/R 16 17 Profit on change in collection period: 18 New daily sales 19 Change in average age of A/R 20 Contribution margin % 21 Profit on change 22 23 24 Time Zero Amounts 25 Change in A/R 26 Profit on change in collection period 27 Other W/C change 28 Total 29 30 31 32 33 B NPV Calculation: 34 Present value of cash inflows 35 Present value of cash outflows 36 Net Present Value 37 38 IRR Calculation: 39 Annual cashflow 40 Investment in A/R 41 Internal Rate of Return 42 43 NAB Calculation: 44 Allowed annual cost 45 Actual annual cost 46 Net Annual Benefit Years 1 to Infinity Admin costs Bad debts Contribution margin Discounts Tax on above Total 27