Rework Problem 15-10 using a spreadsheet model. After completing Parts

QUESTION

Rework Problem 15-10 using a spreadsheet model. After completing Parts a through d, respond to the following: If Bowers customers began to pay late, collections would slow down, thus increasing the required loan amount. If sales declined, this also would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the maximum loan requirement.
a) The sales adjustment factor can be used to cause sales to vary from the base levels. Similarly we can change the late paying customers. Here is the relevant data table Change in sales $155,100 0% 15% 30% 45% 60% 75% 90% -100% $496,200 $496,200 $496,200 $496,200 $496,200 $496,200 $496,200 -75% $318,450 $325,200 $331,950 $338,700 $345,450 $353,550 $373,800 -50% $148,800 $189,300 $229,800 $270,300 $310,800 $351,300 $391,800 -25% $110,100 $150,600 $191,100 $231,600 $288,300 $349,050 $409,800 0% $101,100 $155,100 $209,100 $263,100 $317,100 $371,100 $427,800 25% $92,100 $159,600 $227,100 $294,600 $362,100 $429,600 $497,100 50% $83,100 $164,100 $245,100 $326,100 $407,100 $488,100 $569,100 75%¦

$74,100 $168,600 $263,100 $357,600 $452,100 $546,600 $641,100 100% $65,100 $173,100 $281,100 $389,100 $497,100 $605,100 $713,100 You an see from the table that, from the base case (collection=15%, change in sales=0), an increase in late payers increases the loan requirement. The patteren is mixed for change in sales. As sales increase the maximum loan amount increases to support the high level of sales. As sales decrease the maximum amount generally decreases.

ANSWER:

CLICK REQUEST FOR  AN EXPERT SOLUTION