Hi!!! Please see attached and answer using formulas on excel. Thank you.

Problem 27-4 Quarterly working capital levels for your firm for the next year are included in the following table. What are the permanent working capital needs of your company? What are the tempo needs? Quarter (000) Cash Accounts receivable Inventory Accounts payable 1 2 $100 200 200 100 3 $100 100 500 100 $100 100 900 100 Quarter (000) Net working capital Permanent working capital Temporary working capital 1 2 3 Requirements 1. In cell D14, by using cell references, calculate the net working capital for year 1 (1 p Copy cell D14, and paste it onto cells E14:G14 (1 pt.). 2. To calculate the permanent working capital, you need to find the minimum net workin capital by using the function MIN. In cell D15, by using the function MIN and cell references, calculate the permanent working capital (1 pt.). 3. In cell D16, by using relative and absolute cell references, calculate the temporary working capital needs for year 1 (1 pt.). Copy cell D16, and paste it onto cells E16:G (1 pt.). included in the following any? What are the temporary 4 $100 600 50 100 4 ng capital for year 1 (1 pt.). the minimum net working function MIN and cell lculate the temporary paste it onto cells E16:G16 Problem 27-6 The Hand-to-Mouth Company needs a $10,000 loan for the next 30 days. It is trying to d Alternative A: Forgo the discount on its trade credit agreement that offers terms o Alternative B: Borrow the money from Bank A, which has offered to lend the firm (no-interest) compensating balance of 5% of the face value of the l Hand-to-Mouth must borrow even more than the $10,000. Alternative C: Borrow the money from Bank B, which has offered to lend the firm origination fee. Which alternative is the cheapest source of financing for Hand-to-Mouth? Principal Term of loan $10,000 30 Alternative A: Forego trade discount Credit Terms 2.00% Additional days Interest rate per period Annual rate Alternative B: Borrow from Bank A APR 12.00% Compensating balance 5.00% Fee $100.00 Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate Alternative C: Borrow from Bank B APR 15.00% Compensating balance 0.00% Origination fee 1.00% Fee Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate Cheapest loan cost This is: Requirements 1. In cell D16, by using cell references, calculate the additional days of credit (1 pt.). 2. In cell D17, by using cell references, calculate the implicit interest rate charged for the ad 3. In cell D18, by using cell references, calculate the annual cost of payables (1 pt.). 4. In cell D25, by using cell references, calculate the total amount to borrow (1 pt.). 5. In cell D26, by using cell references, calculate the interest paid (1 pt.). 6. In cell D27, by using cell references, calculate the interest & fee paid (1 pt.). 7. In cell D28, by using cell references, calculate the periodic rate by dividing the interest & 8. In cell D29, by using cell references, calculate the annual rate (1 pt.). 9. In cell D37, by using cell references, calculate the fee to be paid (1 pt.). 10. In cell D38, by using cell references, calculate the total amount to borrow (1 pt.). 11. In cell D39, by using cell references, calculate the interest paid (1 pt.). 12. In cell D40, by using cell references, calculate the interest & fee paid (1 pt.). 13. In cell D41, by using cell references, calculate the periodic rate (1 pt.). 14. In cell D42, by using cell references, calculate the annual rate (1 pt.). 15. You will find the cheapest loan cost by using the function MIN. In cell D44, by using the the cheapest loan cost (1 pt.). 16. In cell D45, identify the cheapest alternative by typing Alternative A, Alternative B or he next 30 days. It is trying to decide which of three alternatives to use: it agreement that offers terms of 2/10, net 30. hich has offered to lend the firm $10,000 for 30 days at an APR of 12%. The bank will require a of 5% of the face value of the loan and will charge a $100 loan origination fee, which means more than the $10,000. hich has offered to lend the firm $10,000 for 30 days at an APR of 15%. The loan has a 1% loan Hand-to-Mouth? 10 net 30 onal days of credit (1 pt.). it interest rate charged for the additional days of credit (1 pt.). cost of payables (1 pt.). mount to borrow (1 pt.). t paid (1 pt.). t & fee paid (1 pt.). ic rate by dividing the interest & fee paid (1 pt.). rate (1 pt.). be paid (1 pt.). mount to borrow (1 pt.). t paid (1 pt.). t & fee paid (1 pt.). ic rate (1 pt.). rate (1 pt.). n MIN. In cell D44, by using the function MIN and cell references, find lternative A, Alternative B or Alternative C (1 pt.). k will require a which means n has a 1% loan Problem 28-9 Your company has earnings per share of $4. It has 1 million shares outstanding, each of w price of $40. You are thinking of buying TargetCo, which has earnings per share of $2, 1 shares outstanding, and a price per share of $25. You will pay for TargetCo by issuing ne There are no expected synergies from the transaction. a. If you pay no premium to buy TargetCo, what will your earnings per share be after b. If you pay a 20% premium to buy TargetCo, what will your earnings per share be merger? c. What explains the change in earnings per share in part (a)? Are your shareholders worse off? d. What will your price-earnings ratio be after the merger (if you pay no premium)? H this compare to your P/E ratio before the merger? How does this compare to Targe merger P/E ratio? Your Company: Earnings per share Shares outstanding Price per share $4.00 1,000,000 $40.00 Target: Earnings per share Shares outstanding Price per share $2.00 1,000,000 $25.00 Total consolidated earnings a. If you pay no premium to buy TargetCo, what will your earnings per share be after Value of target company Shares of acquirer issued Total shares outstanding New EPS b. If you pay a 20% premium to buy TargetCo, what will your earnings per share be merger? Premium 20% Purchase price Value of target company Shares of acquirer issued Total shares outstanding New EPS c. What explains the change in earnings per share in part (a)? Are your shareholders worse off? Focusing on EPS alone cannot tell you whether they're better or worse off. d. What will your price-earnings ratio be after the merger (if you pay no premium)? H this compare to your P/E ratio before the merger? How does this compare to Targe merger P/E ratio? Acquirer's P/E ratio before Acquirer's P/E ratio after Target's P/E ratio before Requirements To calculate the acquirer's new EPS, you need to calculate the total consolidated earning 1. of shares outstanding after the merger. Use cell references in all of the following requir In cell D20, calculate the total consolidated earnings by adding both companies' earnings 2. In cell D24, calculate the total value of the target company. (1 point.) 3. In cell D25, calculate the shares that the acquirer needs to issue. (1 point.) 4. In cell D26, calculate the total number of shares outstanding after the merger. (1 point.) In cell D27, by using cell references, calculate the new EPS. by dividing the total consoli 5. total number of shares outstanding after the merger. (1 point.) 6. In cell D33, calculate the new price per share of the target company if a premium is paid. 7. In cell D34, calculate the new total value of the target company. (1 point.) 8. In cell D35, calculate the shares that the acquirer needs to issue. (1 point.) 9. In cell D36, calculate the total number of shares outstanding after the merger. (1 point.) In cell D37, by using cell references, calculate the new EPS by dividing the total consolid 10. total number of shares outstanding after the merger. (1 point.) 11. In cell D45, by using cell references, calculate the acquirer's P/E ratio before the merger. 12. In cell D46, calculate the acquirer's P/E ratio after the merger. (1 point.) 13. In cell D47, calculate the target company's P/E ratio before the merger. (1 point.) outstanding, each of which has a ngs per share of $2, 1 million argetCo by issuing new shares. ings per share be after the merger? earnings per share be after the Are your shareholders any better or ou pay no premium)? How does this compare to TargetCo's pre- ings per share be after the merger? earnings per share be after the Are your shareholders any better or etter or worse off. ou pay no premium)? How does this compare to TargetCo's pre- consolidated earnings and the total number the following requirements. h companies' earnings. (1 point.) he merger. (1 point.) iding the total consolidated earnings by the y if a premium is paid. (1 point.) he merger. (1 point.) ding the total consolidated earnings by the tio before the merger. (1 point.) ger. (1 point.) Problem 30-6 Your utility company will need to buy 100,000 barrels of oil in ten days time, and it is wo you go long 100 oil futures contracts, each for 1000 barrels of oil, at the current futures p futures prices change each day as follows: 63 62 $ Future Price ($/bbl) 61 $60.75 $60.50 60 $60.00 $59.50 $59.75 $59.50 59 58 $58.00 $57.75 $57.50 57 0 1 2 3 4 5 6 7 8 9 Day a. What is the mark-to-market profit or loss (in dollars) that you will have on each da b. What is your total profit or loss after ten days? Have you been protected against a c. What is the largest cumulative loss you will experience over the ten-day period? In problem? Position (long contracts) Contract (barrels) Current price 100 1,000 $60.00 a. What is the mark-to-market profit or loss (in dollars) that you will have on each da Day 0 1 2 3 4 5 6 Price ($) 60.00 59.50 57.50 57.75 58.00 59.50 60.50 Gain/Loss ($) 7 8 9 10 60.75 59.75 61.75 62.50 b. What is your total profit or loss after ten days? Have you been protected against a Total Protected? c. What is the largest cumulative loss you will experience over the ten-day period? In problem? Day 0 1 2 3 4 5 6 7 8 9 10 Gain/Loss ($) Cumulative ($) Largest cumulative loss This loss would be a problem if you had to liquidate that day.\" Requirements 1. In cell F31, by using cell references, calculate the profit or loss for day 1. Hint: Use absolute cell reference on cells E22 and E23 in order to get cell F31 ready to b Copy and paste cell F31 onto cells F32:F40. (2 points.) 2. In cell E44, calculate the total profit or loss by using the function SUM. (1 point.) To see whether you have been protected against a rise in oil prices, you need to assess wh 3. by using the function IF. In cell E45, input the function IF to compare whether the total profit (or loss) is greater t greater than zero, otherwise show NO. (1 point.) 4. To calculate the largest daily cumulative loss, you need to calculate the daily cumulative In cell F52, calculate the cumulative gain or loss for day 1 by making a cell reference to E52. (1 point.) In cell F53, calculate the cumulative gain or loss for day 2 by adding the profit or loss fo 5. loss for day 1. Copy and paste cell F53 onto cells F54:F61. (2 points.) 6. To find the largest daily cumulative loss, in cell E63, use the function MIN. (1 point.) en days time, and it is worried about fuel costs. Suppose il, at the current futures price of $60 per barrel. Suppose $62.50 $61.75 $60.75 $60.50 $59.75 6 7 8 9 10 you will have on each date? been protected against a rise in oil prices? ver the ten-day period? In what case might this be a you will have on each date? been protected against a rise in oil prices? ver the ten-day period? In what case might this be a for day 1. to get cell F31 ready to be copied onto cell F32 to F40. n SUM. (1 point.) es, you need to assess whether you experienced a total profit or loss profit (or loss) is greater than 0, and show YES if total profit is late the daily cumulative gain or loss. aking a cell reference to the profit or loss for day 1, cell dding the profit or loss for day 2, and the cumulative gain or points.) nction MIN. (1 point.) Problem 31-8 Manzetti Foods, a U.S. food processing and distribution company, is considering an inve in the euro area. You are in Manzetti's corporate finance department and are responsible deciding whether to undertake the project. The expected free cash flows, in euros, are uncorrelated to the spot exchange rate and are shown here: Year 0 1 2 3 4 Free Cash Flow (EUR million) -25 12 14 15 15 The new project has similar dollar risk to Manzetti's other projects. The company knows overall dollar WACC is 9.5%, so it feels comfortable using this WACC for the project. Th free interest rate on dollars is 4.5% and the risk-free interest rate on euros is 7%. a. Manzetti is willing to assume that capital markets in the United States and the euro are internationally integrated. What is the company's euro WACC? b. What is the present value of the project in euros? Risk-free rate on USD Risk-free rate on EUR Year 0 1 2 3 4 US WACC 4.50% 7.00% Free Cash Flow (EUR million) (25.00) 12.00 14.00 15.00 15.00 9.50% a. Manzetti is willing to assume that capital markets in the United States and the euro are internationally integrated. What is the company's euro WACC? Euro WACC b. What is the present value of the project in euros? Project value (million EUR) Undertake project? Requirements 1. In cell E33, calculate the euro WACC. To calculate the euro WACC use the following fo WACC_EUR = (1 + WACC_USD) * (1 + r_EUR)/(1 + r_USD) - 1. (1 point.) 2. To calculate the project value, you will use the function NPV. In cell E37, input the func 3. To decide on the attractiveness of the project, you need to assess the value of the project In cell E38, if the project value, cell E37, is larger than 0; show Yes; otherwise display N any, is considering an investment rtment and are responsible for cash flows, in euros, are jects. The company knows that its s WACC for the project. The riskate on euros is 7%. e United States and the euro area uro WACC? e United States and the euro area uro WACC? WACC use the following formula: SD) - 1. (1 point.) In cell E37, input the function NPV. (1 point.) ess the value of the project by using the function IF. w Yes; otherwise display No. (1 point.) Problem 24-12 coupon payment date. It has a price of $99. What is the bond's yield to maturity and yield call? Coupon rate Payment frequency Time until first call date (years) Term of bond (years) Call price Current price 5.00% Semi-annually 2 3 $100.00 $99.00 Coupon payment Yield to call Yield to maturity Requirements 1. In cell D13, by using cell references, calculate the semi-annual coupon payment (1 To calculate the yield to call of the bond, use the function RATE. In cell 2. To calculate the yield to maturity of the bond, use the function RATE. In cell function RATE and cell references, calculate the yield to call of the bond 3. using the function RATE and cell references, calculate the yield to maturity of the pt.). o maturity and yield to coupon payment (1 pt.). E. In cell D14, by using the RATE. In cell D15, by f the bond (1 pt.). d to maturity of the bond (1 Problem 23-12 Three years ago, you founded Outdoor Recreation, Inc., a retailer specializing in the sale equipment and clothing for recreational activities such as camping, skiing, and hiking. So your company has gone through three funding rounds: Round Series A Series B Series C Date Feb. 2009 Aug. 2010 Sept. 2011 Investor You Angels Venture Capital Currently, it is 2012 and you need to raise additional capital to expand your business. You decided to take your firm public through an IPO. You would like to issue an additional 6 million new shares through this IPO. Assuming that your firm successfully completes its you forecast that 2012 net income will be $7.5 million. a. Your investment banker advises you that the prices of other recent IPOs have been that the P/E ratios, based on 2012 forecasted earnings, average 20.0. Assuming tha IPO is set at a price that implies a similar multiple, what will your IPO price per sh b. What percentage of the firm will you own after the IPO? New shares 2012 net income forecast Forward P/E 6,500,000 $7,500,000 20.00 a. Your investment banker advises you that the prices of other recent IPOs have been that the P/E ratios, based on 2012 forecasted earnings, average 20.0. Assuming tha IPO is set at a price that implies a similar multiple, what will your IPO price per sh New shares outstanding Earnings per share New price with comparable P/E b. What percentage of the firm will you own after the IPO? Percentage you own post IPO Requirements 1. In cell E21, by using cell references, calculate the number of new shares outstandi pt.). 2. In cell E22, by using cell references, calculate the forecasted earnings per share (1 3. In cell E23, by using cell references, calculate the share price for the IPO (1 pt.). 4. In cell E27, by using cell references, calculate the percentage of the firm that you after the IPO (1 pt.). c., a retailer specializing in the sale of h as camping, skiing, and hiking. So far, : Shares Share Price ($) 500,000 1.00 1,000,000 2.00 2,000,000 3.50 capital to expand your business. You have would like to issue an additional 6.5 your firm successfully completes its IPO, n. ices of other recent IPOs have been set such rnings, average 20.0. Assuming that your ple, what will your IPO price per share be? the IPO? ices of other recent IPOs have been set such rnings, average 20.0. Assuming that your ple, what will your IPO price per share be? the IPO? he number of new shares outstanding (1 he forecasted earnings per share (1 pt.). he share price for the IPO (1 pt.). he percentage of the firm that you own Problem 26-4 The Greek Connection had sales of $32 million in 2009, and a cost of goods sold of $20 balance sheet for the firm appears below: THE GREEK CONNECTION Balance Sheet As of December 31, 2009 (000) Assets Cash Accounts receivable Inventory Total current assets $2,000 3,950 1,300 $7,250 Net plant, property and equipment Total Assets $8,500 $15,750 a. Calculate The Greek Connection's net working capital in 2009. b. Calculate the cash conversion cycle of The Greek Connection in 2009. c. The industry average days sales outstanding ratio is 30 days. What would the cash Greek Connection have been in 2009 had it matched the industry average days sal Sales (000) Cost of Goods Sold (000) $32,000 $20,000 a. Calculate The Greek Connection's net working capital in 2009. Net working capital (000) b. Calculate the cash conversion cycle of The Greek Connection in 2009. Accounts receivable days Inventory days Accounts payable days Cash conversion cycle (days) c. The industry average days sales outstanding ratio is 30 days. What would the cash Greek Connection have been in 2009 had it matched the industry average days sal Industry accounts receivable days 30 Cash conversion cycle (days) Requirements 1. In cell D31, by using cell references, calculate the company's net working capital 2. To calculate the cash conversion cycle, you need to calculate accounts receivable d days, and accounts payable days. In cell D35, by using cell references, calculate the accounts receivable days(1 pt.) 3. In cell D36, by using cell references, calculate the inventory days (1 pt.). 4. In cell D37, by using cell references, calculate the accounts payable days (1 pt.). 5. In cell D38, by using cell references, calculate the cash conversion cycle (1 pt.). 6. In cell D44, by using cell references, calculate the cash conversion cycle based on accounts receivable days (1 pt.). nd a cost of goods sold of $20 million. A simplified CONNECTION e Sheet ber 31, 2009 0) Liabilities and Equity Accounts payable Notes payable Accruals Total current liabilities Long-term debt Total liabilities Common equity Total liabilities and equity $1,500 1,000 1,220 $3,720 $3,000 $6,720 9,030 $15,750 ital in 2009. Connection in 2009. s 30 days. What would the cash conversion cycle for The d the industry average days sales outstanding? Days in a year ital in 2009. Connection in 2009. 365 s 30 days. What would the cash conversion cycle for The d the industry average days sales outstanding? company's net working capital (1 pt.). calculate accounts receivable days, inventory accounts receivable days(1 pt.). nventory days (1 pt.). accounts payable days (1 pt.). cash conversion cycle (1 pt.). cash conversion cycle based on the industry Problem 27-4 Quarterly working capital levels for your firm for the next year are included in the following table. What are the permanent working capital needs of your company? What are the tempo needs? Quarter (000) 1 2 3 Cash $100 $100 $100 Accounts receivable 200 100 100 Inventory 200 500 900 Accounts payable 100 100 100 Quarter (000) Net working capital Permanent working capital Temporary working capital 1 2 $400 $400 $0 $600 3 $1,000 $200 $600 Requirements 1. In cell D14, by using cell references, calculate the net working capital for year 1 (1 p Copy cell D14, and paste it onto cells E14:G14 (1 pt.). 2. To calculate the permanent working capital, you need to find the minimum net workin capital by using the function MIN. In cell D15, by using the function MIN and cell references, calculate the permanent working capital (1 pt.). 3. In cell D16, by using relative and absolute cell references, calculate the temporary working capital needs for year 1 (1 pt.). Copy cell D16, and paste it onto cells E16:G (1 pt.). Working notes: how we calculate these Net Working capital = Permanent working capital Temporary working capital Current assets - Current liabilities minimum of the working capital Net working capital - Permanet working capital included in the following any? What are the temporary 4 $100 600 50 100 4 $650 $250 ng capital for year 1 (1 pt.). d the minimum net working function MIN and cell alculate the temporary paste it onto cells E16:G16 Problem 27-6 The Hand-to-Mouth Company needs a $10,000 loan for the next 30 days. It is trying to d Alternative A: Forgo the discount on its trade credit agreement that offers terms o Alternative B: Borrow the money from Bank A, which has offered to lend the firm (no-interest) compensating balance of 5% of the face value of the l Hand-to-Mouth must borrow even more than the $10,000. Alternative C: Borrow the money from Bank B, which has offered to lend the firm origination fee. Which alternative is the cheapest source of financing for Hand-to-Mouth? Principal Term of loan $10,000 30 Alternative A: Forego trade discount Credit Terms 2.00% Additional days Interest rate per period Annual rate 20 2.041% 37.24% Alternative B: Borrow from Bank A APR 12.00% Compensating balance 5.00% Fee $100.00 Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate $10,600.00 $104.55 $204.55 2.05% 24.89% Alternative C: Borrow from Bank B APR 15.00% Compensating balance 0.00% Origination fee 1.00% Fee Total borrowed Interest paid $100.00 $10,100.00 $124.52 Interest & fee paid Periodic rate Annual rate $224.52 2.25% 27.32% Cheapest loan cost This is: 24.89% Alternative B Requirements 1. In cell D16, by using cell references, calculate the additional days of credit 2. In cell D17, by using cell references, calculate the implicit interest rate charged for the a 3. In cell D18, by using cell references, calculate the annual cost of payables 4. In cell D25, by using cell references, calculate the total amount to borrow 5. In cell D26, by using cell references, calculate the interest paid (1 pt.) 6. In cell D27, by using cell references, calculate the interest & fee paid 7. In cell D28, by using cell references, calculate the periodic rate by dividing the interest & 8. In cell D29, by using cell references, calculate the annual rate (1 pt.) 9. In cell D37, by using cell references, calculate the fee to be paid (1 pt.) 10. In cell D38, by using cell references, calculate the total amount to borrow 11. In cell D39, by using cell references, calculate the interest paid (1 pt.) 12. In cell D40, by using cell references, calculate the interest & fee paid 13. In cell D41, by using cell references, calculate the periodic rate (1 pt.) 14. In cell D42, by using cell references, calculate the annual rate (1 pt.) 15. You will find the cheapest loan cost by using the function MIN. In cell D44, by using the the cheapest loan cost (1 pt.). 16. In cell D45, identify the cheapest alternative by typing Alternative A he next 30 days. It is trying to decide which of three alternatives to use: dit agreement that offers terms of 2/10, net 30. which has offered to lend the firm $10,000 for 30 days at an APR of 12%. The bank will require a of 5% of the face value of the loan and will charge a $100 loan origination fee, which means more than the $10,000. which has offered to lend the firm $10,000 for 30 days at an APR of 15%. The loan has a 1% loan Hand-to-Mouth? 10 net 30 onal days of credit (1 pt.). it interest rate charged for the additional days of credit (1 pt.). l cost of payables (1 pt.). mount to borrow (1 pt.). st paid (1 pt.). st & fee paid (1 pt.). ic rate by dividing the interest & fee paid (1 pt.). l rate (1 pt.). be paid (1 pt.). mount to borrow (1 pt.). st paid (1 pt.). st & fee paid (1 pt.). ic rate (1 pt.). l rate (1 pt.). n MIN. In cell D44, by using the function MIN and cell references, find lternative A, Alternative B or Alternative C (1 pt.). k will require a which means n has a 1% loan Problem 28-9 Your company has earnings per share of $4. It has 1 million shares outstanding, each of w price of $40. You are thinking of buying TargetCo, which has earnings per share of $2, 1 shares outstanding, and a price per share of $25. You will pay for TargetCo by issuing ne There are no expected synergies from the transaction. a. If you pay no premium to buy TargetCo, what will your earnings per share be afte b. If you pay a 20% premium to buy TargetCo, what will your earnings per share be merger? c. What explains the change in earnings per share in part (a)? Are your shareholders worse off? d. What will your price-earnings ratio be after the merger (if you pay no premium)? H this compare to your P/E ratio before the merger? How does this compare to Targe merger P/E ratio? Your Company: Earnings per share Shares outstanding Price per share $4.00 1,000,000 $40.00 Target: Earnings per share Shares outstanding Price per share $2.00 1,000,000 $25.00 Total consolidated earnings $6,000,000 a. If you pay no premium to buy TargetCo, what will your earnings per share be afte Value of target company Shares of acquirer issued Total shares outstanding New EPS $25,000,000 625,000 1,625,000 $3.69 b. If you pay a 20% premium to buy TargetCo, what will your earnings per share be merger? Premium 20% Purchase price Value of target company Shares of acquirer issued Total shares outstanding New EPS $30.00 $25,000,000 750,000 1,750,000 $3.43 c. What explains the change in earnings per share in part (a)? Are your shareholders worse off? Focusing on EPS alone cannot tell you whether they're better or worse off. d. What will your price-earnings ratio be after the merger (if you pay no premium)? H this compare to your P/E ratio before the merger? How does this compare to Targe merger P/E ratio? Acquirer's P/E ratio before Acquirer's P/E ratio after Target's P/E ratio before 10.00 10.83 12.50 Requirements To calculate the acquirer's new EPS, you need to calculate the total consolidated earning 1. of shares outstanding after the merger. Use cell references in all of the following requir In cell D20, calculate the total consolidated earnings by adding both companies' earnings 2. In cell D24, calculate the total value of the target company. (1 point.) 3. In cell D25, calculate the shares that the acquirer needs to issue. (1 point.) 4. In cell D26, calculate the total number of shares outstanding after the merger. In cell D27, by using cell references, calculate the new EPS. by dividing the total consoli 5. total number of shares outstanding after the merger. (1 point.) 6. In cell D33, calculate the new price per share of the target company if a premium is paid. 7. In cell D34, calculate the new total value of the target company. (1 point.) 8. In cell D35, calculate the shares that the acquirer needs to issue. (1 point.) 9. In cell D36, calculate the total number of shares outstanding after the merger. In cell D37, by using cell references, calculate the new EPS by dividing the total consoli 10. total number of shares outstanding after the merger. (1 point.) 11. In cell D45, by using cell references, calculate the acquirer's P/E ratio before the merger. 12. In cell D46, calculate the acquirer's P/E ratio after the merger. (1 point.) 13. In cell D47, calculate the target company's P/E ratio before the merger. (1 point.) outstanding, each of which has a ngs per share of $2, 1 million argetCo by issuing new shares. ings per share be after the merger? earnings per share be after the Are your shareholders any better or ou pay no premium)? How does this compare to TargetCo's pre- ings per share be after the merger? earnings per share be after the Are your shareholders any better or etter or worse off. ou pay no premium)? How does this compare to TargetCo's pre- l consolidated earnings and the total number the following requirements. h companies' earnings. (1 point.) he merger. (1 point.) viding the total consolidated earnings by the y if a premium is paid. (1 point.) he merger. (1 point.) iding the total consolidated earnings by the atio before the merger. (1 point.) rger. (1 point.) Problem 30-6 Future Price ($/bbl) Your utility company will need to buy 100,000 barrels of oil in ten days time, and it is wo you go long 100 oil futures contracts, each for 1000 barrels of oil, at the current futures p futures prices change each day as follows: 63 62 $6 61 $60.75 $60.50 60 $60.00 $59.50 $59.75 $59.50 59 58 $58.00 $57.75 $57.50 57 0 1 2 3 4 5 6 7 8 9 Day a. What is the mark-to-market profit or loss (in dollars) that you will have on each da b. What is your total profit or loss after ten days? Have you been protected against a c. What is the largest cumulative loss you will experience over the ten-day period? In problem? Position (long contracts) Contract (barrels) Current price 100 1,000 $60.00 a. What is the mark-to-market profit or loss (in dollars) that you will have on each da Day 0 1 2 3 4 5 6 7 Price ($) 60.00 59.50 57.50 57.75 58.00 59.50 60.50 60.75 Gain/Loss ($) -50,000 -200,000 25,000 25,000 150,000 100,000 25,000 8 9 10 59.75 61.75 62.50 -100,000 200,000 75,000 b. What is your total profit or loss after ten days? Have you been protected against a Total Protected? $250,000 Yes c. What is the largest cumulative loss you will experience over the ten-day period? In problem? Day 0 1 2 3 4 5 6 7 8 9 10 Largest cumulative loss Gain/Loss ($) -50,000 -200,000 25,000 25,000 150,000 100,000 25,000 -100,000 200,000 75,000 Cumulative ($) -50,000 -250,000 -225,000 -200,000 -50,000 50,000 75,000 -25,000 175,000 250,000 -$250,000 This loss would be a problem if you had to liquidate that day.\" Requirements 1. In cell F31, by using cell references, calculate the profit or loss for day 1. Hint: Use absolute cell reference on cells E22 and E23 in order to get cell F31 ready to b Copy and paste cell F31 onto cells F32:F40. (2 points.) 2. In cell E44, calculate the total profit or loss by using the function SUM. (1 point.) To see whether you have been protected against a rise in oil prices, you need to assess wh 3. by using the function IF. In cell E45, input the function IF to compare whether the total profit (or loss) is greater t greater than zero, otherwise show NO. (1 point.) 4. To calculate the largest daily cumulative loss, you need to calculate the daily cumulative In cell F52, calculate the cumulative gain or loss for day 1 by making a cell reference to E52. (1 point.) In cell F53, calculate the cumulative gain or loss for day 2 by adding the profit or loss fo 5. or loss for day 1. Copy and paste cell F53 onto cells F54:F61. (2 points.) 6. To find the largest daily cumulative loss, in cell E63, use the function MIN ten days time, and it is worried about fuel costs. Suppose oil, at the current futures price of $60 per barrel. Suppose $62.50 $61.75 $60.75 $60.50 $59.75 6 7 8 9 10 you will have on each date? been protected against a rise in oil prices? ver the ten-day period? In what case might this be a you will have on each date? been protected against a rise in oil prices? ver the ten-day period? In what case might this be a for day 1. to get cell F31 ready to be copied onto cell F32 to F40. on SUM. (1 point.) ces, you need to assess whether you experienced a total profit or loss profit (or loss) is greater than 0, and show YES if total profit is late the daily cumulative gain or loss. making a cell reference to the profit or loss for day 1, cell dding the profit or loss for day 2, and the cumulative gain 2 points.) nction MIN. (1 point.) Problem 31-8 Manzetti Foods, a U.S. food processing and distribution company, is considering an inve in the euro area. You are in Manzetti's corporate finance department and are responsible deciding whether to undertake the project. The expected free cash flows, in euros, are uncorrelated to the spot exchange rate and are shown here: Year 0 1 2 3 4 Free Cash Flow (EUR million) -25 12 14 15 15 The new project has similar dollar risk to Manzetti's other projects. The company knows overall dollar WACC is 9.5%, so it feels comfortable using this WACC for the project. Th free interest rate on dollars is 4.5% and the risk-free interest rate on euros is 7%. a. Manzetti is willing to assume that capital markets in the United States and the euro are internationally integrated. What is the company's euro WACC? b. What is the present value of the project in euros? Risk-free rate on USD Risk-free rate on EUR Year 0 1 2 3 4 US WACC 4.50% 7.00% Free Cash Flow (EUR million) (25.00) 12.00 14.00 15.00 15.00 9.50% a. Manzetti is willing to assume that capital markets in the United States and the euro are internationally integrated. What is the company's euro WACC? Euro WACC 12.12% b. What is the present value of the project in euros? Project value (million EUR) Undertake project? 16.97 YES Requirements 1. In cell E33, calculate the euro WACC. To calculate the euro WACC use the following fo WACC_EUR = (1 + WACC_USD) * (1 + r_EUR)/(1 + r_USD) - 1. (1 point.) 2. To calculate the project value, you will use the function NPV. In cell E37 3. To decide on the attractiveness of the project, you need to assess the value of the project In cell E38, if the project value, cell E37, is larger than 0; show Yes; otherwise display pany, is considering an investment rtment and are responsible for cash flows, in euros, are ojects. The company knows that its is WACC for the project. The riskate on euros is 7%. e United States and the euro area uro WACC? e United States and the euro area uro WACC? WACC use the following formula: SD) - 1. (1 point.) . In cell E37, input the function NPV. (1 point.) ess the value of the project by using the function IF. ow Yes; otherwise display No. (1 point.) Boeing Corporation has just issued a callable (at par) three-year, 5% coupon bond with s Problem 24-12payments. The bond can be called at par in two years or anytime thereafte annual coupon coupon payment date. It has a price of $99. What is the bond's yield to maturity and yield call? Coupon rate Payment frequency Time until first call date (years) Term of bond (years) Call price Current price Coupon payment Yield to call Yield to maturity 5.00% Semi-annually 2 3 $100.00 $99.00 $2.50 0.03 0.03 Requirements 1. In cell D13, by using cell references, calculate the semi-annual coupon payment To calculate the yield to call of the bond, use the function RATE. In cell D14, by u 2. To the yield to cell maturity of the calculate bond, usethe theyield function RATE. cell (1 D15 the calculate function RATE and references, to call of theInbond pt 3. using the function RATE and cell references, calculate the yield to maturity of the (1 pt.). oupon bond with semior anytime thereafter on a o maturity and yield to coupon payment (1 pt.). E. In cell D14, by using RATE. cell (1 D15, all of theInbond pt.).by d to maturity of the bond Problem 23-12 Three years ago, you founded Outdoor Recreation, Inc., a retailer specializing in the sale equipment and clothing for recreational activities such as camping, skiing, and hiking. So your company has gone through three funding rounds: Round Series A Series B Series C Date Feb. 2009 Aug. 2010 Sept. 2011 Investor You Angels Venture Capital Currently, it is 2012 and you need to raise additional capital to expand your business. Yo decided to take your firm public through an IPO. You would like to issue an additional 6 million new shares through this IPO. Assuming that your firm successfully completes its you forecast that 2012 net income will be $7.5 million. a. Your investment banker advises you that the prices of other recent IPOs have been such that the P/E ratios, based on 2012 forecasted earnings, average 20.0. Assumin your IPO is set at a price that implies a similar multiple, what will your IPO price share be? b. What percentage of the firm will you own after the IPO? New shares 2012 net income forecast Forward P/E 6,500,000 $7,500,000 20.00 a. Your investment banker advises you that the prices of other recent IPOs have been such that the P/E ratios, based on 2012 forecasted earnings, average 20.0. Assumin your IPO is set at a price that implies a similar multiple, what will your IPO price share be? New shares outstanding Earnings per share New price with comparable P/E 10,000,000 $0.75 $15.00 b. What percentage of the firm will you own after the IPO? Percentage you own post IPO Requirements 5.00% 1. In cell E21, by using cell references, calculate the number of new shares outstandi pt.). 2. In cell E22, by using cell references, calculate the forecasted earnings per share (1 3. In cell E23, by using cell references, calculate the share price for the IPO (1 pt.). 4. In cell E27, by using cell references, calculate the percentage of the firm that you after the IPO (1 pt.). c., a retailer specializing in the sale of h as camping, skiing, and hiking. So far, : Shares Share Price ($) 500,000 1.00 1,000,000 2.00 2,000,000 3.50 capital to expand your business. You have u would like to issue an additional 6.5 your firm successfully completes its IPO, n. ices of other recent IPOs have been set ted earnings, average 20.0. Assuming that multiple, what will your IPO price per r the IPO? ices of other recent IPOs have been set ted earnings, average 20.0. Assuming that multiple, what will your IPO price per r the IPO? the number of new shares outstanding (1 the forecasted earnings per share (1 pt.). the share price for the IPO (1 pt.). the percentage of the firm that you own Problem 26-4 The Greek Connection had sales of $32 million in 2009, and a cost of goods sold of $20 balance sheet for the firm appears below: THE GREEK CONNECTION Balance Sheet As of December 31, 2009 (000) Assets Cash Accounts receivable Inventory Total current assets $2,000 3,950 1,300 $7,250 Net plant, property and equipment Total Assets $8,500 $15,750 a. Calculate The Greek Connection's net working capital in 2009. b. Calculate the cash conversion cycle of The Greek Connection in 2009. c. The industry average days sales outstanding ratio is 30 days. What would the cash Greek Connection have been in 2009 had it matched the industry average days sal Sales (000) Cost of Goods Sold (000) $32,000 $20,000 a. Calculate The Greek Connection's net working capital in 2009. Net working capital (000) $3,530 b. Calculate the cash conversion cycle of The Greek Connection in 2009. Accounts receivable days Inventory days Accounts payable days Cash conversion cycle (days) 45.055 23.725 27.4 41.4 c. The industry average days sales outstanding ratio is 30 days. What would the cash Greek Connection have been in 2009 had it matched the industry average days sal Industry accounts receivable days Cash conversion cycle (days) 30 26.35 Requirements 1. In cell D31, by using cell references, calculate the company's net working capital 2. To calculate the cash conversion cycle, you need to calculate accounts receivable d days, and accounts payable days. In cell D35, by using cell references, calculate the accounts receivable days 3. In cell D36, by using cell references, calculate the inventory days 4. In cell D37, by using cell references, calculate the accounts payable days 5. In cell D38, by using cell references, calculate the cash conversion cycle 6. In cell D44, by using cell references, calculate the cash conversion cycle based on accounts receivable days (1 pt.). and a cost of goods sold of $20 million. A simplified CONNECTION e Sheet ber 31, 2009 0) Liabilities and Equity Accounts payable Notes payable Accruals Total current liabilities Long-term debt Total liabilities Common equity Total liabilities and equity $1,500 1,000 1,220 $3,720 $3,000 $6,720 9,030 $15,750 ital in 2009. Connection in 2009. s 30 days. What would the cash conversion cycle for The d the industry average days sales outstanding? Days in a year ital in 2009. Connection in 2009. 365 s 30 days. What would the cash conversion cycle for The d the industry average days sales outstanding? company's net working capital (1 pt.). o calculate accounts receivable days, inventory accounts receivable days(1 pt.). inventory days (1 pt.). accounts payable days (1 pt.). cash conversion cycle (1 pt.). cash conversion cycle based on the industry