CFIN6
Spreadsheet Problem Solution
Chapter 15
a. The change in credit terms should not be made, because the NPV of the current credit policy is greater than
it would be with the change.
INPUT DATA:
Old
New
Annual Data:
Sales
$3,000,000
$2,600,000
Variable cost ratio
70.0%
70.0%
Variable cost
Collection costs
Bad debt percent
DSO—discount customers
DSO—nondiscount customers
Cash discount
% discount customers
$2,100,000
$1,820,000
% nondiscount customers
100.0%
100.0%
Required rate of return, r
11.0%
11.0%
Daily Data (360 days):
Sales
$8,333.33
$7,222.22
Collection—discount cust.
$0.00
$0.00
Collection—nondiscount cust.
$7,916.67
$7,005.56
Variable cost
Collection costs
Required rate of return, r/360
KEY OUTPUT:
Net present value
$1,522.87
$1,389.38
MODEL GENERATED DATA:
Cash Flows of Existing Policy:
Outflow on Day
0
Inflow on Day
0
$0.00
CFIN6
Outflow on Day
0
Inflow on Day
0
Inflow on Day
$7,005.56
b. If sales only decrease to $2.8 million with the change in terms, then the change should be made.
INPUT DATA:
Old
New
Annual Data:
Sales
$3,000,000
$2,800,000
Variable cost ratio
Variable cost
$2,100,000
$1,960,000
Collection costs
Bad debt percent
5.0%
3.0%
70.0%
70.0%
Cash discount
0.0%
0.0%
% discount customers
0.0%
0.0%
% nondiscount customers
100.0%
100.0%
Required rate of return, r
11.0%
11.0%
Daily Data (360 days):
Sales
$7,916.67
$7,544.44
Variable cost
Collection costs
Required rate of return, r/360
$8,333.33
$7,777.78
KEY OUTPUT:
Net present value
$1,522.87
$1,533.65
c. Because NPVnew > NPVold, the proposed policy should be enacted.
INPUT DATA:
Old
New
Annual Data:
Sales
$3,000,000
$3,300,000
Variable cost ratio
70.0%
70.0%
Variable cost
$2,100,000
$2,310,000
CFIN6
Bad debt percent
5.0%
5.0%
Cash discount
0.0%
0.0%
% discount customers
0.0%
0.0%
% nondiscount customers
Required rate of return, r
11.0%
11.0%
Daily Data (360 days):
Sales
$8,333.33
$9,444.44
$7,916.67
$8,972.22
Variable cost
Collection costs
$150,000
$175,000
Bad debt percent
Cash discount
0.0%
0.0%
% discount customers
0.0%
0.0%
% nondiscount customers
Required rate of return, r
11.0%
11.0%
5.0%
6.0%
Daily Data (360 days):
Sales
$8,333.33
$9,166.67
$7,916.67
$8,616.67
Variable cost
Collection costs
Required rate of return, r/360
KEY OUTPUT:
Net present value
$1,522.87
$1,583.27
d. If sales increase to $3.4 million by leaving the policy as is, the NPV will increase.
INPUT DATA:
Old
New
Annual Data:
Sales
$3,000,000
$3,400,000
Variable cost ratio
70.0%
70.0%
Variable cost
$2,100,000
$2,380,000
Collection costs
$150,000
$175,000
CFIN6
Collection costs
($416.67)
($486.11)
Required rate of return, r/360
0.0306%
0.0306%
KEY OUTPUT:
Net present value
$1,522.87
$1,712.03
e. The cash discount should be offered.
INPUT DATA:
Old
New
Annual Data:
Sales
$3,000,000
$3,500,000
Variable cost ratio
70.0%
70.0%
Variable cost
Collection costs
Bad debt percent
DSO—discount customers
DSO—nondiscount customers
Cash discount
% discount customers
15.0%
% nondiscount customers
85.0%
Required rate of return, r
11.0%
11.0%
$2,100,000
$2,450,000
Daily Data (360 days):
Sales
$8,333.33
$9,722.22
Collection—discount cust.
$0.00
$1,429.17
Collection—nondiscount cust.
$7,916.67
$7,892.01
Variable cost
Collection costs
Required rate of return, r/360
0.0306%
0.0306%
KEY OUTPUT:
Net present value
$1,522.87
$1,836.07
f. The terms of credit that offer Muscarella the highest NPV require the firm to offer a cash discount. As a result,
the firm should offer credit terms of 2/10 net 30.