Hello, this is the document I was given. This contains all the information I need to answer the following question, but the problem is I don't even know where to begin. So if you could please help and show me what to do. If you don't know how to the answer the following question, then don't answer it and please don't report the question, this is my homework and I'm trying to get help to learn. Thanks

Question: Produce a detailed free cash flow statement for the Sneaker 13 project and calculate the NPV and IRR for the project. Use a table similar to the one provided in the template below for your calculations and make any necessary adjustments. Express all $-amounts in millions of dollars and round to two decimals.

It must be done in excel Thanks fo your help. Greatly appreciated.

Rodriguez was still contemplating the Sneaker 2013 project when she began reviewing another proposal for a new hiking shoe being considered. The hiking shoe would be named Persistence. The hiking and active walking sector was one of the fastest growing areas of the footwear industry and one they had not yet entered. She was confident that hiking shoes would be the newest footwear trend in the coming decade. The target market for this shoe would be men and women in the 25- to 40-year-old age category. The business case for the hiking shoe needed some work; but after preliminary analysis, she focused on the following information: 1. The life of the Persistence project would be only three years, given the steep technological learning curve for this new product line. 2. The wholesale price of Persistence (net to New Balance) would be $90.00. 3. The hiking segment of the athletic shoe market was projected to reach $350 million during 2013, and it was growing at a rate of 15% per year. New Balance's market share projections for Persistence were: 2013, 15\%; 2014, 18\%; and 2015, 20%. observers felt James was still improving, made him a likely contender for years to come. New Balance saw James as the perfect athlete to appeal to a younger audience, and Davis wanted him on board. As the afterglow of the Olympic flame faded, Davis knew they needed to act quickly to capitalize on James's new-found global fame. In spite of the economic downturn and subsequent forecasts of the demise of athletic footwear, the multi-billion dollar athletic footwear industry continued to grow steadily, if not spectacularly. A recent industry report claimed that 2012 would be the best athletic footwear market in over a decade. At the high end of the market, new high-tech shoes were coming out at a rapid pace. Air Jordan Retros, the Nike Mag Flux Capacitor, Reebok Pump Twilight Zone, and the Ewing 33Hi were selling well. The highly anticipated LeBron Nike X Plus, due out in fall 2012 and forecast to retail for a staggering $315, incorporated technology and pressure/motion sensors that would track and store data on distance, speed, and jumping height. The shoe New Balance had designed for James was envisioned as a medium-tech, high quality running shoe at a reasonable price just under $200 retail. It would be marketed globally and had been tentatively dubbed Sneaker 2013 until a final name could be selected. The business case for Sneaker 2013 needed to be thorough and complete. It required input from sales and marketing, technology engineers, manufacturing, and finance. The data were organized and thorough, and it was up to Rodriguez to come up with a compelling analysis and a recommendation about whether to proceed. She knew her boss and the New Balance CEO were excited at the idea of Sneaker 2013 and at having Kirani James as their newest athlete endorser. Template for Project Analysis 3. The global athletic footwear market in 2011 totaled approximately $74.5 billion and was expected to grow at a CAGR of 1.8% from 2011 to 2018 , reaching $84.4 billion by 2018.3 Based on market research and analysis of other recent athlete endorsements, the New Balance marketing division estimated the following sales volumes for Sneaker 2013: The 2016 number assumed Kirani James participated in the 2016 games in Rio de Janeiro, Brazil, and won at least one medal.4 4. For the first two years, the introduction of Sneaker 2013 would reduce sales of existing New Balance shoes as follows: Lost sales: 2013: $35 million 2014: $15 million Assume the lost revenue had the same margins as Sneaker 2013. 5. In order to produce the shoe, the firm needed to build a factory in Vietnam. This required an immediate outlay of $150 million, to be depreciated on a 39-year MACRS 5 basis. Depreciation percentages for the first six years respectively were: 2.6%,5%,4.7%, 4.5%,4.3%, and 4.0%. The firm's analysts estimated the building would be sold for $102 million at project termination. This "salvage value" has not been taken into consideration when computing annual depreciation charges. 6 10. Kirani James would be paid \$2 million per year for his endorsement of Sneaker 2013, with an additional \$1 million Olympic bonus in 2016. 11. Other advertising and promotion costs were estimated as follows: 12. New Balance had already spent $2 million in research and development on Sneaker 2013. 13. The Sneaker 2013 project was to be financed using a combination of equity and debt. The interest costs on the debt were expected to be approximately $1.2 million per year. The New Balance discount rate for new projects such as this was 11%. 14. New Balance's effective tax rate was 40%. Rodriguez was worried about the marketing approach for Sneaker 2013 targeting 12- to 18year-old males. Recent market data showed the average age of athletic footwear purchasers to be just over 27 years, up from 24 three years earlier. This trend was expected to continue as the population aged. Success would depend on an effective marketing and advertising campaign which targeted not only the younger consumer, but which reached the ultimate purchaser who was more likely to be a parent. The Situation It was 6:35 p.m. on a Friday as Michelle Rodriguez held her head in her hands and sought to regain focus. Her company, New Balance, based in Brighton, Massachusetts, had recently implemented a policy on work-life balance. She had just made the mistake of opening an email from the senior VP of product development, Monte Holliday, who needed a position report by Monday morning on one of New Balance's most promising new athletic shoes. On the heels of the 2012 London Olympics, New Balance saw an opportunity in the 12- to 18-year-old male segment of the market, which their larger competitors had ignored. Established, well-known Olympic athletes like Usain Bolt already had multi-million dollar endorsement deals for athletic footwear, and they dominated the age 18 to 24 male market for running shoes. 1 New Balance did not have the resources or the star power to compete in this segment. However, New Balance saw an opportunity to target a younger consumer if they could craft an effective marketing and advertising campaign around the right athlete. Holliday and New Balance CEO Jim Davis had just returned from London after holding preliminary meetings with several potential new endorsers. The most promising was a 19-year-old phenomenon from Grenada named Kirani James. By winning gold in the 400-meter dash in London, James became the first runner from 9. General and administrative expenses for Persistence would be 12% of revenue in 2013. This would drop to 10% in 2014 and 8\% in 2015. 10. The product would not have a celebrity endorser. Advertising and promotion costs would initially be \$3 million in 2013, then \$2 million in both 2014 and 2015. 11. The company's federal plus state marginal tax rate was 40%. 12. In order to begin immediate production of Persistence, the design technology and the manufacturing specifications for a new hiking shoe would be purchased from an outside source for $50 million. This outlay was to take place immediately and be expensed immediately for tax purposes. 13. Annual interest costs on the debt for this project would be $600,000. In addition, Rodriguez estimated the cost of capital for the hiking shoe would be 14%. But she also knew that all the excitement and flash in the world could not make up for a project if the financials did not work. Sneaker 2013 The business case team had compiled the following baseline information surrounding the Sneaker 2013 project: 1. The life of the Sneaker 2013 project was expected to be six years. Assume the analysis took place at the end of 2012. 2. The suggested retail price of the shoe was $190. Gross margins for high-end athletic footwear averaged about 40% at the retail level, meaning each pair sold would net New Balance \$115. 4. The firm would be able to use an idle section of one of its factories to produce the hiking shoe. A cost accountant estimated that, according to the square footage in the factory, this section's overhead allocation would amount to $1.8 million per year. The firm would still incur these costs if the product were not undertaken. In addition, this section would remain idle for the life of the project if the Persistence project were not undertaken. 5. The firm must purchase manufacturing equipment costing $8 million. The equipment fell into the five-year MACRS depreciation category. Depreciation percentages for the first three years respectively were: 20%,32%, and 19%. The cash outlay would be at Time 0 , and depreciation would start in 2013. Analysts estimated the equipment could be sold for book value at the end of the project's life. 6. Inventory and accounts receivable would increase by $25 million at Time o and would be recovered at the end of the project (2015). The accounts payable balance was projected to increase by $10 million at Time 0 and would also be recovered at the end of the project. 7. Because the firm had not yet entered the hiking shoe market, introduction of this product was not expected to impact sales of the firm's other shoe lines. 6. The company must immediately purchase equipment costing $15 million. Freight and installation of the equipment would cost $5 million. The cost of equipment and freight/installation was to be depreciated on a five-year MACRS basis. Depreciation percentages for the six years respectively were: 20%,32%,19%,12%,11%, and 6%. It was believed the equipment could be sold for $3 million upon project termination. 7. In order to manufacture Sneaker 2013, two of the firm's working capital accounts were expected to increase immediately. Approximately $15 million of inventory would be needed quickly to fill the supply chain, and accounts payable were expected to increase by $5 million. By the end of 2013 , the accounts receivable balance would be 8% of project revenue; the inventory balance would be 25% of the project's variable costs; and accounts payable would be 20% of the project's variable costs. All working capital would be recovered at the end of the project by the end of the sixth year. 8. Variable costs were expected to be 55% of revenue. By winning gold in the 400 meter dash in London, James became the first runner from outside the United States to win the event in more than three decades. 2 With a winning time of 43.94 seconds, he was also the first non-U.S. runner to finish the 400 under 44 seconds. James's victory was a huge feat for his country. Grenada had never won any Olympic medal, much less gold, and he returned home to a hero's welcome. His relative youth, and the fact that many Rodriguez was still contemplating the Sneaker 2013 project when she began reviewing another proposal for a new hiking shoe being considered. The hiking shoe would be named Persistence. The hiking and active walking sector was one of the fastest growing areas of the footwear industry and one they had not yet entered. She was confident that hiking shoes would be the newest footwear trend in the coming decade. The target market for this shoe would be men and women in the 25- to 40-year-old age category. The business case for the hiking shoe needed some work; but after preliminary analysis, she focused on the following information: 1. The life of the Persistence project would be only three years, given the steep technological learning curve for this new product line. 2. The wholesale price of Persistence (net to New Balance) would be $90.00. 3. The hiking segment of the athletic shoe market was projected to reach $350 million during 2013, and it was growing at a rate of 15% per year. New Balance's market share projections for Persistence were: 2013, 15\%; 2014, 18\%; and 2015, 20%. observers felt James was still improving, made him a likely contender for years to come. New Balance saw James as the perfect athlete to appeal to a younger audience, and Davis wanted him on board. As the afterglow of the Olympic flame faded, Davis knew they needed to act quickly to capitalize on James's new-found global fame. In spite of the economic downturn and subsequent forecasts of the demise of athletic footwear, the multi-billion dollar athletic footwear industry continued to grow steadily, if not spectacularly. A recent industry report claimed that 2012 would be the best athletic footwear market in over a decade. At the high end of the market, new high-tech shoes were coming out at a rapid pace. Air Jordan Retros, the Nike Mag Flux Capacitor, Reebok Pump Twilight Zone, and the Ewing 33Hi were selling well. The highly anticipated LeBron Nike X Plus, due out in fall 2012 and forecast to retail for a staggering $315, incorporated technology and pressure/motion sensors that would track and store data on distance, speed, and jumping height. The shoe New Balance had designed for James was envisioned as a medium-tech, high quality running shoe at a reasonable price just under $200 retail. It would be marketed globally and had been tentatively dubbed Sneaker 2013 until a final name could be selected. The business case for Sneaker 2013 needed to be thorough and complete. It required input from sales and marketing, technology engineers, manufacturing, and finance. The data were organized and thorough, and it was up to Rodriguez to come up with a compelling analysis and a recommendation about whether to proceed. She knew her boss and the New Balance CEO were excited at the idea of Sneaker 2013 and at having Kirani James as their newest athlete endorser. Template for Project Analysis 3. The global athletic footwear market in 2011 totaled approximately $74.5 billion and was expected to grow at a CAGR of 1.8% from 2011 to 2018 , reaching $84.4 billion by 2018.3 Based on market research and analysis of other recent athlete endorsements, the New Balance marketing division estimated the following sales volumes for Sneaker 2013: The 2016 number assumed Kirani James participated in the 2016 games in Rio de Janeiro, Brazil, and won at least one medal.4 4. For the first two years, the introduction of Sneaker 2013 would reduce sales of existing New Balance shoes as follows: Lost sales: 2013: $35 million 2014: $15 million Assume the lost revenue had the same margins as Sneaker 2013. 5. In order to produce the shoe, the firm needed to build a factory in Vietnam. This required an immediate outlay of $150 million, to be depreciated on a 39-year MACRS 5 basis. Depreciation percentages for the first six years respectively were: 2.6%,5%,4.7%, 4.5%,4.3%, and 4.0%. The firm's analysts estimated the building would be sold for $102 million at project termination. This "salvage value" has not been taken into consideration when computing annual depreciation charges. 6 10. Kirani James would be paid \$2 million per year for his endorsement of Sneaker 2013, with an additional \$1 million Olympic bonus in 2016. 11. Other advertising and promotion costs were estimated as follows: 12. New Balance had already spent $2 million in research and development on Sneaker 2013. 13. The Sneaker 2013 project was to be financed using a combination of equity and debt. The interest costs on the debt were expected to be approximately $1.2 million per year. The New Balance discount rate for new projects such as this was 11%. 14. New Balance's effective tax rate was 40%. Rodriguez was worried about the marketing approach for Sneaker 2013 targeting 12- to 18year-old males. Recent market data showed the average age of athletic footwear purchasers to be just over 27 years, up from 24 three years earlier. This trend was expected to continue as the population aged. Success would depend on an effective marketing and advertising campaign which targeted not only the younger consumer, but which reached the ultimate purchaser who was more likely to be a parent. The Situation It was 6:35 p.m. on a Friday as Michelle Rodriguez held her head in her hands and sought to regain focus. Her company, New Balance, based in Brighton, Massachusetts, had recently implemented a policy on work-life balance. She had just made the mistake of opening an email from the senior VP of product development, Monte Holliday, who needed a position report by Monday morning on one of New Balance's most promising new athletic shoes. On the heels of the 2012 London Olympics, New Balance saw an opportunity in the 12- to 18-year-old male segment of the market, which their larger competitors had ignored. Established, well-known Olympic athletes like Usain Bolt already had multi-million dollar endorsement deals for athletic footwear, and they dominated the age 18 to 24 male market for running shoes. 1 New Balance did not have the resources or the star power to compete in this segment. However, New Balance saw an opportunity to target a younger consumer if they could craft an effective marketing and advertising campaign around the right athlete. Holliday and New Balance CEO Jim Davis had just returned from London after holding preliminary meetings with several potential new endorsers. The most promising was a 19-year-old phenomenon from Grenada named Kirani James. By winning gold in the 400-meter dash in London, James became the first runner from 9. General and administrative expenses for Persistence would be 12% of revenue in 2013. This would drop to 10% in 2014 and 8\% in 2015. 10. The product would not have a celebrity endorser. Advertising and promotion costs would initially be \$3 million in 2013, then \$2 million in both 2014 and 2015. 11. The company's federal plus state marginal tax rate was 40%. 12. In order to begin immediate production of Persistence, the design technology and the manufacturing specifications for a new hiking shoe would be purchased from an outside source for $50 million. This outlay was to take place immediately and be expensed immediately for tax purposes. 13. Annual interest costs on the debt for this project would be $600,000. In addition, Rodriguez estimated the cost of capital for the hiking shoe would be 14%. But she also knew that all the excitement and flash in the world could not make up for a project if the financials did not work. Sneaker 2013 The business case team had compiled the following baseline information surrounding the Sneaker 2013 project: 1. The life of the Sneaker 2013 project was expected to be six years. Assume the analysis took place at the end of 2012. 2. The suggested retail price of the shoe was $190. Gross margins for high-end athletic footwear averaged about 40% at the retail level, meaning each pair sold would net New Balance \$115. 4. The firm would be able to use an idle section of one of its factories to produce the hiking shoe. A cost accountant estimated that, according to the square footage in the factory, this section's overhead allocation would amount to $1.8 million per year. The firm would still incur these costs if the product were not undertaken. In addition, this section would remain idle for the life of the project if the Persistence project were not undertaken. 5. The firm must purchase manufacturing equipment costing $8 million. The equipment fell into the five-year MACRS depreciation category. Depreciation percentages for the first three years respectively were: 20%,32%, and 19%. The cash outlay would be at Time 0 , and depreciation would start in 2013. Analysts estimated the equipment could be sold for book value at the end of the project's life. 6. Inventory and accounts receivable would increase by $25 million at Time o and would be recovered at the end of the project (2015). The accounts payable balance was projected to increase by $10 million at Time 0 and would also be recovered at the end of the project. 7. Because the firm had not yet entered the hiking shoe market, introduction of this product was not expected to impact sales of the firm's other shoe lines. 6. The company must immediately purchase equipment costing $15 million. Freight and installation of the equipment would cost $5 million. The cost of equipment and freight/installation was to be depreciated on a five-year MACRS basis. Depreciation percentages for the six years respectively were: 20%,32%,19%,12%,11%, and 6%. It was believed the equipment could be sold for $3 million upon project termination. 7. In order to manufacture Sneaker 2013, two of the firm's working capital accounts were expected to increase immediately. Approximately $15 million of inventory would be needed quickly to fill the supply chain, and accounts payable were expected to increase by $5 million. By the end of 2013 , the accounts receivable balance would be 8% of project revenue; the inventory balance would be 25% of the project's variable costs; and accounts payable would be 20% of the project's variable costs. All working capital would be recovered at the end of the project by the end of the sixth year. 8. Variable costs were expected to be 55% of revenue. By winning gold in the 400 meter dash in London, James became the first runner from outside the United States to win the event in more than three decades. 2 With a winning time of 43.94 seconds, he was also the first non-U.S. runner to finish the 400 under 44 seconds. James's victory was a huge feat for his country. Grenada had never won any Olympic medal, much less gold, and he returned home to a hero's welcome. His relative youth, and the fact that many