Please help me resolve this problem using formulas in excel

Case Study Keywords: Capital Asset Pricing Model (cost of equity), Internal Rate of Return, Net Present Value, Payback Period, Yield to Maturity (cost of debt), Weighted Average Cost of Capital Introduction Doctor Shepard is the CEO (Chief Executive Officer) of Shepard Offices Company, a male clothing-manufacturing firm located in New York City, and he is looking to expand the company's product line. As of 2018, the company manufactures ties of luxury for an affordable price to the men of Wall Street. Based upon marketing studies commissioned by Shepard's team, the company's profits have the potential to increase 15 to 20% by adding more diversity. Doctor Shepard has two Vice-Chairmen; each have selected a potential investment opportunity. Vice-Chairman Ilanna selected a handkerchief company through an acquisition. While the other Vice-Chairman Maria selected cufflinks to create and add to their manufacturing shop. Company Background Shepard Offices Company is a tie company located in New York City and has been in business since the early 1980s. The idea came to him one afternoon during a meeting when his fellow coworkers began discussing ties. The main problems discussed were that they often untied easily, lost color, or even were just unflattering. Shepard decided that he would create a brand that they could all love. Shepard did just that, he began to research and create a tie in his apartment at night and on the weekends. He was able to create these ties at a low or no cost just by using his old material; he then would sell these ties to coworkers. Eventually, Shepard was able to create a brand large enough to begin to buy new material. Quickly the company received favorable reviews from top fashion company's, celebrities, and even top men on Wall Street. Due to Shepard's low startup costs, all of his revenue could be reinvested. Eventually Shepard calculated his opportunity cost for the business and decided it would be more profitable to leave Wall Street and work on his ties full time. He was able to move his business from an apartment to a larger office space and eventually a manufacturing facility. As time grew he began to hire more employees, two fellow coworkers Ilanna and Maria eventually left Wall Street and joined his company to help expand it. Maria emphasized the marketing side of the company while Ilanna worked more on the financing and productivity side. Between the three of them, they were able to expand Shepard Offices Company from Wall Street in New York City to all over the United States. The biggest factor that has made Shepard's company successful compared to their competition is they only produce in the United States and have not moved oversees to lower costs. Current Situation As stated, both Ilanna and Maria picked an investment opportunity with the hopes of choosing one to add for diversity. They will present their ideas to Doctor Shepard. They plan to run the new investment out of their manufacturing factory in order to save money. However, in order to add the new business they will need to purchase new machines to make either the cufflinks or handkerchiefs. Maria did the research for the cufflinks and found that they will need six new machines for the cufflinks and it would cost $3.3 million. The machines will be operative for 7 years Ilanna also did her research for the handkerchiefs and discovered that they would need to purchase six new machines for $3 million. The machines will be operative for 7 years Financial Information Maria and Ilanna calculated the beta for both projects as the following the cufflinks beta is 1.1 and the handkerchiefs beta is 1. This is considered using the pure play approach for the cost of equity or risk or the investment It is important to note some key financial facts about Shepard's office Company. Shepard's office company has one bond outstanding with a coupon rate of 4.3% with a face value of $1,000 and will mature in 15 years. This bond pays interest semi-annually, and the corporate tax rate is 21%. The market price of the debt is 101. Shepard's company is a public company and the common stock is trading at $250 a share, and pays dividends quarterly of $2.00. The company also has preferred stock outstanding at a par value of $75 per share, and pays a dividend of 11%. 50% of the company is equity 25% of the company is Preferred stock and 25% of the company is debt. Shepard's company beta is 98 The risk free rate is 5% and the historical market return is 8%. They were also able to create the projected cash flows for both ventures. The Shepard Company requires an IRR of 20% before investing in a project and a payback period of 3 years. Year 0 1 2 3 4 5 6 7 Handkerchief $(3,000,000) $700,000 $850,000 $1,000,000 $1,150,000 $1,300,000 $1,450.000 $1.600.000 Cufflink $(3,300,000) $400,000 $600,000 $800.000 $1,000,000 $1,200.000 $1,400.000 $1.600.000 Each question should be answered in full detail, using the understanding of key concepts. Note, you cannot state that the ventures are a tie; one must be selected for every question. For each of the Ventures find the following: 1) Find the Payback Period for the two projects 2) For deciding the best possible venture, rank NPV, IRR, and Payback Period from most useful to least