Select Page

Please see attached Excel worksheets. Read the problem carefully and fill in the blanks. Ensure that you fill in the blanks with the correct formulas and not just plug in the numbers. We are using the Financial Management Theory and Practice book in class. Please let me know if you have any questions. Thank you.
ch16_p18_build_a_model.xlsx

ch17_p15_build_a_model.xlsx

Unformatted Attachment Preview

11/26/2018
Chapter:
Problem:
16
18
Rusty Spears, CEO of Rusty’s Renovations, a custom building and repair company, is preparing documentation for
a line of credit request from his commercial banker. Among the required documents is a detailed sales forecast for
parts for the next two years.
Estimates obtained from the credit and collection department are as follows: collections within the month of sale,
15%; collections during the month following the sale, 65%; collections the second month following the sale, 20%.
Payments for labor and raw materials are typically made during the month following the one in which these costs
were incurred. Total costs for labor and raw materials are estimated for each month as shown in the table.
General and administrative salaries will amount to approximately $15,000 a month; lease payments under longterm lease contracts will be $5,000 a month; depreciation charges will be $7,500 a month; miscellaneous expenses
will be $2,000 a month; income tax payments of $25,000 will be due in both September and December; and a
progress payment of $80,000 on a new office suite must be paid in October. Cash on hand on July 1 will amount to
$60,000, and a minimum cash balance of $40,000 will be maintained throughout the cash budget period.
Input Data
Collections during month of sale
Collections during month after sale
Collections during second month after sale
Lease payments
Target cash balance
General and administrative salaries
Depreciation charges
Income tax payments (Sep & Dec)
Miscellaneous expenses
New office suite payment (Oct)
Cash on hand July 1
Sales, labor, and RM adjustment factor
15%
65%
20%
$5,000
$40,000
$15,000
$7,500
$25,000
$2,000
$80,000
$60,000
0%
Note: When the percent collected during
the second month after sale is changed,
the percent for collections during month
after sale is automatically changed so that
100% of sales are collected during the
three-month period.
a. Prepare a monthly cash budget for the last six months of the year.
Original sales estimates
Original labor and raw mat. estimates
Forecasted Sales
Sales (gross)
Collections
During month of sale
During 1st month after sale
During 2nd month after sale
Total collections
May
$60,000
$75,000
June
$100,000
$90,000
July
$130,000
$95,000
August
$120,000
$70,000
September
$100,000
$60,000
October November December
$80,000
$60,000
$40,000
$50,000
$20,000
$20,000
Purchases
Labor and raw materials
Payments for labor and raw materials
Payments
Payments for labor and raw materials
General and administrative salaries
Lease payments
Miscellaneous expenses
Income tax payments
Design studio payment
Total payments
Net Cash Flows
Cash on hand at start of forecast period
Net cash flow (NCF): Total collections – Total payments
Cumulative NCF: Prior month cumulative + this month’s NCF
Cash Surplus (or Loan Requirement)
Target cash balance
Surplus cash or loan needed: Cum NCF – Target cash
Max. Loan
b. How much must Spears borrow each month to maintain the target cash balance?
Answer. Look at the “Surplus cash or loan needed” line at the bottom of the cash budget.
c. Would the cash budget be accurate if inflows came in all during the month but outflows were bunched
early in the month?
d. If the company operates on a seasonal basis, how would this affect the current ratio and the debt ratio?
e. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if
sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two
factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment
factor.
Answer:
January
$30,000
A
B
C
D
E
F
1
2 Chapter:
3 Problem:
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
17
15
Mark Collins, luthier and businessman, builds and sells custom-made acoustic and electric stringed instruments. Although located in Mary
purchases raw materials from around the globe. For example, his top-of-the line acoustic guitar with onboard electronics, the MC-28, is co
rosewood and mahogany imported from a distributer in Mexico, spruce harvested in and imported from Canada, and ebony and the electro
a Japanese distributer. Other parts are obtained in the U.S. When broken down on a per-guitar basis, the component and finishing costs ar
Rosewood and mahogany: 2,750 Mexican pesos
Spruce: 200 Canadian dollars
Ebony and electronics: 12,400 Japanese Yen
Other parts plus woodworking labor: $600
Collins sells some of this model in the U.S., but the majority of the units are sold in England where he has developed a loyal following and
become something of a cult symbol. There, his guitars fetch £1,600, excluding shipping. Mark is concerned about the effect of exchange ra
costs and profit.
You will find Tables 17-1 and 17-2 useful for this problem and are shown in following rows.
TABLE 17-1
Exchange rates of select major currencies, relative to the U.S. dollar
Note: The pound and euro are quoted as direct quotations. Other currencies are quoted as indirect quotations. Blue denotes the actual qu
reported out to digits to the right of the decimal; orange is the inverse of the provided quote and is not rounded.
Note: only blue cells need to be updated with current quotes.
Country
Direct Quote Currencies:
Euro area
U.K.
Indirect Quote Currencies:
Canada
China
India
Japan
Mexico
South Korea
Switzerland
ISO Currency
Code
(1)
Currency Name
(2)
Direct Quote:
Indirect Quote:
U.S. Dollars
Number of Foreign
Required to Buy
Currency Units
a Unit of Foreign Required to buy a
Currency
U.S. Dollar
(3)
(4)
EUR
GPB
Euro (€)
U.K. (British) pound (£)
1,1641
1,3071
0,8590
0,7651
CAD
CNY
INR
JPY
MXN
KRW
CHF
Canadian dollar ($)
Chinese yuan (¥)
Indian rupee (₹ )
Japanese yen (¥)
Mexican peso ($)
South Korea won (₩)
Swiss franc (SFr)
0,7594
0,1469
0,0146
0,0089
0,0529
0,0009
1,0011
1,3168
6,7222
68,4750
112,8400
18,8955
1.131,1600
0,9989
Key Currency Cross-Exchange Rates
Note: these do not need to be updated because they are derived from the quotes in preceding table and are not rounded.
A
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
United States
Euro
United Kingdom
Canada
Japan
Mexico
Switzerland
B
U.S. Dollar
….
0,8590
0,7651
1,3168
112,8400
18,8955
0,9989
C
Euro
1,1641
….
0,8906
1,5329
131,3570
21,9963
1,1628
D
U.K. Pound
1,3071
1,1228
….
1,7212
147,4932
24,6983
1,3057
E
CdnDlr
0,7594
0,6524
0,5810
….
85,6926
14,3496
0,7586
F
Yen
0,0089
0,0076
0,0068
0,0117
….
0,1675
0,0089
a. How much, in dollars, does it cost for Mark Collins to produce his MC-28? What is the dollar sale price of the MC-28 sold in England?
Input Data
Cost of rosewood and mahogany (pesos)
Cost of spruce (CdnDlr)
Cost of ebony and electronics (yen)
Cost of parts and labor (U.S. dollars)
Sale price of the MC-28 (pounds)
2.750
200
¥12.400
$600
£1.600
We will convert the cost of each component to dollars, and find the total cost of the SY-20. We will do the same to find the
dollar sale price.
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
A
B
Rosewood and mahogany
Cost of rosewood and mahogany in $
Cost of rosewood and mahogany in $
Cost of rosewood and mahogany in $
C
D
E
F
=
=
=
Cost in pesos
x
x
Spruce
Cost of spruce in $
Cost of spruce in $
Cost of spruce in $
=
=
=
Cost in CdnDlr
x
x
Ebony and electronics
Cost of ebony and electronics in $
Cost of ebony and electronics in $
Cost of ebony and electronics in $
=
=
=
Cost in yen
x
x
Parts and labor
Cost of parts and labor in $
Cost of parts and labor in $
Cost of parts and labor in $
=
=
=
Cost in dollars
=
=
=
Price in pounds
TOTAL COST OF THE MC-28 (in dollars) =
Revenue from sale of the MC-28 in England
Sale price (in $)
Sale price (in $)
Sale price (in $)
x
x
MC-28 SALES PRICE (in dollars) =
b. What is the dollar profit that Collins makes on the sale of the MC-28? What is the percentage profit?
The dollar profit from the sale of the SY-20 is simply the sales revenue minus the total cost.
Dollar profit =
Dollar profit =
Dollar profit =
Sales price


Total cost
The percentage profit is determined as the dollar profit divided by the total cost.
% profit =
% profit =
% profit =
$ profit
÷
÷
Total cost
c. If the U.S. dollar were to depreciate by 10% against all foreign currencies, what would the dollar and percentage profits be for the MC-2
Since dollar is depreciating, a dollar buys fewer units of foreign currency than before its depreciation. Therefore, the indirect quotes (whic
of units of foreign currency per dollar) should all be reduced by 10%. Then we convert the new indirect quotes to direct quotes (divide 1 by
get a direct quote).
Change in dollar versus all currencies (positive is appreciation, negative is depreciation):
We will reproduce selected data the table from the top of the spreadsheet, but there are two additional columns for the new exchange rates
Old Direct
Quotations
(3)
Old Indirect Quotations
(4)
New Direct
Quotations
(3)
New Indirect
Quotations
(4)
New D
Direct
Quotations
(3)
New Indirect
E
Quotations
(4)
F
Rosewood and mahogany
Cost of rosewood and mahogany in $
Cost of rosewood and mahogany in $
Cost of rosewood and mahogany in $
=
=
=
Cost in pesos
x
x
Spruce
Cost of spruce in $
Cost of spruce in $
Cost of spruce in $
=
=
=
Cost in CdnDlr
x
x
Ebony and electronics
Cost of ebony and electronics in $
Cost of ebony and electronics in $
Cost of ebony and electronics in $
=
=
=
Cost in yen
x
x
Parts and labor
Cost of parts and labor in $
Cost of parts and labor in $
Cost of parts and labor in $
=
=
=
Cost in dollars
=
=
=
Price in pounds
A
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
Euro (€)
U.K. (British) pound (£)
Canadian dollar ($)
Japanese yen (¥)
Mexican peso ($)
Swiss franc (SFr)
Old Direct
B
Quotations
(3)
1,16410
1,30710
0,75942
0,00886
0,05292
1,00110
C
Old Indirect Quotations
(4)
0,85903
0,76505
1,31680
112,84000
18,89550
0,99890
Now, we will recompute the component costs and sales price of the MC-28.
TOTAL COST OF THE MC-28 (in dollars) =
Revenue from sale of the MC-28 in England
Sale price (in $)
Sale price (in $)
Sale price (in $)
MC-28 SALES PRICE (in dollars) =
x
x
A
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
B
C
D
E
F
The dollar profit from the sale of the MC-28 is simply the sales revenue minus the total cost.
Dollar profit =
Dollar profit =
Dollar profit =
Sales price

Total cost
The percentage profit is determined as the dollar profit divided by the total cost.
% profit =
% profit =
% profit =
$ profit
/
/
Total cost
The total costs go up because the dollar has depreciated against the currencies’ of the suppliers, making it more expensive in dollars for C
the raw materials. However, the depreciation of the dollar versus the pound increases the dollar value of sales. The net effect is a small in
this example, but that will not always be the case.
d. If the U.S. dollar were to depreciate by 10% only against the pound and remained constant relative to all other foreign currencies, what
and percentage profits be for the MC-28?
Since dollar is depreciating relative to the pound, a dollar buys fewer pounds than before its depreciation. Therefore, the indirect quote for
shows the number of pounds per dollar) should be reduced by 10%. Then we convert the new indirect quote to a direct quote (divide 1 by t
get the direct quote). The is no need to change any other direct quotes because the dollar is neither depreciation nor appreciating relative
Change in dollar strength against the pound
Euro (€)
U.K. (British) pound (£)
Canadian dollar ($)
Japanese yen (¥)
Mexican peso ($)
Swiss franc (SFr)
Old Direct
Quotations
(3)
1,16410
1,30710
0,75942
0,00886
0,05292
1,00110
Old Indirect Quotations
(4)
0,85903
0,76505
1,31680
112,84000
18,89550
0,99890
New Direct
Quotations
(3)
1,16410
New Indirect
Quotations
(4)
0,75942
0,00886
0,05292
1,00110
Now, we will recompute the component costs and sales price of the MC-28.
Rosewood and mahogany
Cost of rosewood and mahogany in $
Cost of rosewood and mahogany in $
Cost of rosewood and mahogany in $
=
=
=
Cost in pesos
x
x
Spruce
Cost of spruce in $
Cost of spruce in $
Cost of spruce in $
=
=
=
Cost in CdnDlr
x
x
Ebony and electronics
Cost of ebony and electronics in $
Cost of ebony and electronics in $
Cost of ebony and electronics in $
=
=
=
Cost in yen
x
x
Parts and labor
Cost of parts and labor in $
=
Cost in dollars
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
A
Cost of parts and labor in $
Cost of parts and labor in $
B
C
D
=
=
E
F
=
=
=
Price in pounds
x
x
TOTAL COST OF THE MC-28 (in dollars) =
Revenue from sale of the MC-28 in England
Sale price (in $)
Sale price (in $)
Sale price (in $)
MC-28 SALES PRICE (in dollars) =
The dollar profit from the sale of the MC-28 is simply the sales revenue minus the total cost.
Dollar profit =
Dollar profit =
Dollar profit =
Sales price

Total cost
The percentage profit is determined as the dollar profit divided by the total cost.
% profit =
% profit =
% profit =
$ profit
/
/
Total cost
Because sales are made in pounds, dollar revenues increase when the dollar depreciates relative to the pound–there are fewer pounds per
are more dollars per pound.
e. The rate of return on 90-day U.S. treasury securities is 3.9% and the rate of return on 90-day U.K. risk-free securities is 5.0%. Using the
information from Table 17-1, estimate the 90-day forward exchange rate.
Using knowledge of interest rate parity, the following problem is set up.
Spot exchange rate (direct quotation) =
Home nominal interest rate =
Foreign nominal interest rate =
Time to maturity on securities (in years) =
Home periodic interest rate =
Foreign periodic interest rate =
ft / (spot exchange rate)
ft
ft
3,9%
5,0%
0,25
=
=
=
[ (1+rh) / (1+rf) ]
(spot exchange rate) [ (1+rh) / (1+rf) ]
f. Assuming that purchasing power parity holds, what would the sale price of the MC-28 be if it were sold in France rather than England?
England is the home country.)
Purchasing power parity allows us to establish the following problem.
Price in pounds
Exchange rate (pounds per euro) =
=
Ph
(in pounds)
=
=
=
(Pf)
(Pf)
(Pf)
x
x
( e0 )
G
1
H
I
11/26/2018
2
3
4
5
6
7
s. Although located in Maryville, Tennessee, he
8
ectronics, the MC-28, is constructed from
9
, and ebony and the electronics imported from
10
onent and finishing costs are as follows:
11
12
13
14
15
16
17
18
oped a loyal
19following and the guitars have
ut the effect
20of exchange rates on his materials
21
22
23
24
25
26
27
28
29
Blue denotes
30 the actual quotes, which are
.
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
J
G
H
Peso
SFranc
60
61
0,0529
1,0011
62
0,0455
0,8600
63
0,0405
0,7659
64
0,0697
1,3183
65
5,9718 112,9643
66
….
18,9163
67
0,0529
….
68
69
70
e MC-28 sold
71 in England?
72
73
74
75
76
77
78
79
80
81
82
I
J
G
H
I
83
84 Direct spot exchange rate ($/peso)
85
86
87
88
89 Direct spot exchange rate ($/CdnDlr)
90
91
92
93
94 Direct spot exchange rate ($/yen)
95
96
97
98
99
100
101
102
103
104
105
106 Direct spot exchange rate ($/pound)
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
age profits129
be for the MC-28?
130
e, the indirect
131 quotes (which show the number
o direct quotes
132 (divide 1 by an indirect quote to
133
134
135
136
137
138
139
for the new
140
exchange rates.
141
142
J
G
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
H
I
Direct spot exchange rate ($/peso)
Direct spot exchange rate ($/CdnDlr)
Direct spot exchange rate ($/yen)
Direct spot exchange rate ($/pound)
J
G
H
I
184
185
186
187
188
189
190
191
192
193
194
195
196
197
e expensive in dollars for Collins to purchase
198
The net effect is a small increase in profits for
199
200
201
er foreign 202
currencies, what would the dollar
203
204
205
efore, the indirect
quote for pounds (which
206
a direct quote
207 (divide 1 by the indirect quote to
n nor appreciating
208
relative to them.
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227 Direct spot exchange rate ($/peso)
228
229
230
231
232 Direct spot exchange rate ($/CdnDlr)
233
234
235
236
237 Direct spot exchange rate ($/yen)
238
239
240
241
242
J
G
H
I
243
244
245
246
247
248
249 Direct spot exchange rate ($/pound)
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
there are fewer
267 pounds per pound, but there
268
269
270
ecurities is271
5.0%. Using the spot exchange
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
ance rather288
than England? (Hint: assume
289
290
291
292
293
294
295
296 pounds per euro
297
298
J
K
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
L
M
N
O
P

Purchase answer to see full
attachment