Fluent Essays

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 months 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