CFIN6
Spreadsheet Problem Solution
Chapter 14
a. & b. For Cooley to have $500,000 available to use to invest, pay bills, and so forth, the company must factor
$515,464 of its receivables. If the factoring arrangement reduces the monthly expenses associated with
managing receivable by $3,500 and bad debts can be reduced by 2.5 percent, the company will save
$923 per month ($11,072 per year) by factoring its receivables.
INPUT DATA:
KEY
OUTPUT:
Funds needed
$500,000
Rec. to be factored
$515,464
Commission (%)
Interest rate
Costs per month
Credit period (months)
Savings per month
Reduction in expenses
$3,500
Net savings per month
Reduction in bad debt losses (%)
Net savings per year
MODEL-GENERATED DATA:
Accounts receivables needed to factor:
$515,464
Monthly costs:
Commission
$10,309
Interest
5,155
Total monthly costs
$15,464
Monthly savings:
Credit expense
Bad debt losses
Total monthly savings
$16,387
Net monthly savings
Net annual savings
$11,072
CFIN6
c. In this case, the factoring arrangement would cost the firm a net $366 per month, or $4,392 annually.
INPUT DATA:
KEY
OUTPUT:
Funds needed
$750,000
Rec. to be factored
$773,196
Commission (%)
Interest rate
Costs per month
Credit period (months)
Savings per month
Reduction in expenses
Net savings per month
Reduction in bad debt losses (%)
2.50%
Net savings per year
2.00%
d. Changing the terms of the factoring arrangement would not be beneficial to Cooley.
INPUT DATA:
KEY
OUTPUT:
Funds needed
$500,000
Rec. to be factored
$517,464
Commission (%)
2.50%
Interest rate
Costs per month
Credit period (months)
Savings per month
Reduction in expenses
Net savings per month
Reduction in bad debt losses (%)
2.50%
Net savings per year
e. The factoring arrangement would be profitable if the firm needs $650,000.
INPUT DATA:
KEY
OUTPUT:
Funds needed
$650,000
Rec. to be factored
$670,103
Commission (%)
2.00%
Interest rate
Costs per month
Credit period (months)
Savings per month
Reduction in expenses
Net savings per month
Reduction in bad debt losses (%)
2.50%
Net savings per year