This is a project in advance corporate finance; it required to use excel

Please complete the following problems: USE MICORSOF EXCEL 1. The TL Corporation currently has no debt outstanding. Josh Culberson, the CFO, is considering restructuring the company by issuing debt and using the proceeds to repurchase outstanding equity. The company's assets are worth $40 million, the stock price is $25 per share, and there are 1,600,000 shares outstanding. In the expected state of the economy, EBIT is predicted to be $3 million. If there is a recession, EBIT would fall to $1.8 million, and in an expansion, EBIT would increase to $4.3 million. If the company issues debt, it will issue a combination of short-term debt and long-term debt. The ratio of short-term debt to long-term debt will be 0.20. The short-term debt will have an interest rate of 3 percent and the long-term debt will have an interest rate of 8 percent. a. On the next worksheet, fill in the values in each table. For the debt-equity ratio, create a spinner that changes the debt-equity ratio. The resulting debt-equity ratio should range from 0 to 10 at increments of 0.1. b. Graph the EBIT and EPS for the TL Corporation on the same graph using a scatter plot. c. What is the breakeven EBIT between the current capital structure and the new capital structure? d. To illustrate the new capital structure, you would like to create a pie chart. Another pie chart that is available is the pie in pie chart. Using the pie in pie chart, graph the equity and total debt in the main pie chart and the short-term debt and long-term debt in the secondary pie chart. Note, if you right-click on a data series in the chart and select Format Data Series, the Series Options will permit you to display the series by a customized choice. In the customization, you can select which data series you want displayed in the primary pie chart and the secondary pie chart. 2. The St. Louis Federal Reserve has historical exchange rates on its website, www.stlouisfed.org. On the website, look for the FRED data. Download the exchange rate with U.S. dollars over the past five years for the following currencies: Brazilian reais, Canadian dollars, Hong Kong dollars, Japanese yen, Mexican new pesos, South Korean won, Indian rupees, Swiss francs, Australian dollars, and the euro. Graph the exchange rate for each of these currencies in a dashboard that can be printed on one page. 3. S&S Air Goes International Mark Sexton and Todd Story, the owners of S&S Air, have been in discussions with a light aircraft dealer in Monaco about selling the company's planes in Europe. Jarek Jachowicz, the dealer, wants to add S&S Air to his current retail line. Jarek has told Mark and Todd that he feels the retail sales will be approximately 5.3 million per month. All sales will be made in euros, and Jarek will retain 5 percent of retail sales as a commission, which will be paid in euros. Because the planes will be customized to order, the first sales will take place in one month. Jarek will pay 1 S&S Air for the order 90 days after it is filled. This payment schedule will continue for the length of the contract between the two companies. Mark and Todd are confident the company can handle the extra volume with its existing facilities, but they are unsure about the potential financial risks of selling their planes in Europe. In their discussion with Jarek, they found that the current exchange rate is $1.37/. At the current exchange rate, the company would spend 80 percent of the sales on production costs. This number does not reflect the sales commission paid to Jarek. Mark and Todd have decided to ask Chris Guthrie, the company's financial analyst, to prepare an analysis of the proposed international sales. Specifically, they ask Chris to answer the following questions. A. What are the pros and cons of the international sales? What additional risks will the company face? B. What happens to the company's profits if the dollar strengthens? What if the dollar weakens? C. Ignoring taxes, what are S&S Air's projected gains or losses from this proposed arrangement at the current exchange rate of $1.37/? What happens to profits if the exchange rate changes to $1.25/? At what exchange rate will the company break even? D. How could the company hedge its exchange rate risk? What are the implications for this approach? E. Taking all factors into account, should the company pursue the international sales further? Why or why not? 4. Cash Management at Webb Corporation Webb Corporation was founded 20 years ago by its president, Bryan Webb. The company originally began as a mail-order company, but has grown rapidly in recent years, in large part due to its website. Because of the wide geographical dispersion of the company's customers, it currently employs a lockbox system with collection centers in San Francisco, St. Louis, Atlanta, and Boston. Holly Lennon, the company's treasurer, has been examining the current cash collection policies. On average, each lockbox center handles $193,000 in payments each day. The company's current policy is to invest these payments in short-term marketable securities daily at the collection center banks. Every two weeks, the investment accounts are swept; the proceeds are wiretransferred to Webb's headquarters in Dallas to meet the company's payroll. The investment accounts each earn .012 percent per day, and the wire transfers cost .20 percent of the amount transferred. Holly has been approached by Third National Bank, located just outside Dallas, about the possibility of setting up a concentration banking system for Webb Corp. Third National will accept each of the lockbox center's daily payments via automated clearinghouse (ACH) transfers 2 in lieu of wire transfers. The ACH-transferred funds will not be available for use for one day. Once cleared, the funds will be deposited in a short-term account, which will yield .012 percent per day. Each ACH transfer will cost $150. Bryan has asked Holly to determine which cash management system will be the best for the company. As her assistant, Holly has asked you to answer the following questions. a. What is Webb Corporation's total net cash flow available from the current lockbox system to meet payroll? b. Under the terms outlined by Third National Bank, should the company proceed with the concentration banking system? c. What cost of ACH transfers would make the company indifferent between the two systems? 5. Heidi Pedersen, the treasurer for Wood Products, Inc., has just been asked by Justin Wood, the company's president, to prepare a memo detailing the company's ending cash balance for the next three months. Below, you will see the relevant estimates for this period. July AugustSeptember Credit sales $1,275,800$1,483,500 $1,096,300 Credit purchases 765,480 890,160 657,780 Cash disbursements Wages, taxes, and expenses 348,600 395,620 337,150 Interest 29,900 29,900 29,900 Equipment 0 158,900 96,300 Credit sales collections Collected in month of sale 35% Collected month after sale 60% Never collected 5% June credit sales $1,135,020 June credit purchases $ 681,012 Beginning cash balance $ 425,000 All credit purchases are paid in the month after the purchase. a. Complete the cash budget for Wood Products for the next three months. b. Heidi knows that the cash budget will become a standard report completed before each quarter. To help reduce the time preparing the report each quarter, she would like a memo with the appropriate information in Excel linked to the memo. Prepare a memo to Justin that will automatically update when the values are changed in Excel. 3