explain how to do step by step in excel thanks

Learning Outcomes By the end of this case study, students should be able to: Understand the concept and importance of capital budgeting and investment decision- making. Discuss and identify the various information required for financial evaluation of investment proposals. Understand and calculate after-tax operating cash flows for capital budgeting analysis, taking into account the revenues, costs, depreciation, and working capital. Determine terminal year cash flows after considering salvage value and working capital. Understand the computation of cost of capital for determination of discounting rate. Determine the Net Present Value (NPV) and Internal Rate of Return (IRR) for accepting or rejecting a capital budgeting proposal. Introduction On September 20, 2020, Mahesh Sharma, Senior Vice President of Vardhman Group, a leader of the textile industry, was preparing for a meeting with the management committee scheduled the next week. On his desk was a capital budgeting and investment proposal - a new product line of branded shirts that the committee was considering for launch. As the head of the finance department, Mahesh was required to work along with his team on a detailed capital budgeting analysis and present the findings to the management committee for their approval. As per standard company practice, each capital budgeting and investment project was evaluated using the traditional Net Present Value (NPV) approach and the Internal Rate of Return (IRR) criterion for determining if the company would undertake the project. Mahesh had a lot to think about as he considered the analysis of the capital budgeting project using the traditional Net Present Value (NPV) approach and the Internal Rate of Return (IRR) criterion. What would be the basis for calculating the after-tax operating cash flows for the capital project? How would he arrive at the depreciation and working capital requirements for computing the NPV? What would be the basis for calculating the terminal year cash flows? With all these questions in mind, Mahesh decided to focus on the proposed capital budgeting project for the next few days. Indian Retail Market The Indian retail market is at the cusp of a sweet spot driven by strong GDP (Gross Domestic Product) growth, benign inflation, and rising per capita income and purchasing power of consumers. Currently, the retail industry accounts for more than ten percent of the Indian Gross Domestic Product and approximately eight percent of employment. The industry is expected to nearly double, from US$ 600 billion in 2020 to US$ 1 trillion, by 2025 driven by income growth, urbanization, and attitudinal shifts. It has been estimated that, by 2035, the Indian apparel market, in particular, is expected to grow at a CAGR (compounded annual growth rate) of approximately 10-12%, backed by increasing affordability on account of an increase in disposable incomes, increase in aspirations, and a shift from unbranded to branded products by the burgeoning middle class. This trend is likely to be further accentuated by the rise of e-commerce companies that enable shopping from anywhere, thereby leading to increased penetration in small cities and towns. Company Background Vardhman Group is a leading textile firm based in Ludhiana, India. It was founded in 1965 by Lala Oswal, a retired executive. Since then, the company had grown steadily by catering to middle to low income consumers in the Punjab Region. The company recorded a stellar growth of 50% in its sales during the last financial year of 2019-20. With a healthy operating margin ratio and low leverage levels, the company had been able to grow its profits at a CAGR of 25% during the last 10 years. With a good brand name and healthy financial metrics, the company was now looking to expand its footprint to new product lines catering to middle to high income customers. Project Investment Proposal Details The project is estimated to be of 10 years duration. It involves setting up new machinery with an estimated cost of as much as INR 500 million, including installation. This amount could be depreciated using the straight line method (SLM) over a period of 10 years with a resale value of INR 15 million. The project would require an initial working capital of INR 20 million with cumulative investment in net working capital to be maintained at 10% of each year's projected revenue. With the planned new capacity, the company would be able to produce 240,000 pieces of shirts each year for the next 10 years. In terms of pricing, each shirt can initially be sold at INR 1,300 apiece, which takes into account the target segment and competitor pricing. The project proposal incorporates an annual increase of 3% in the price of the shirt to compensate for inflationary impact. With regards to the raw material costs and other expenses, the project estimated the following details: Raw material cost for manufacturing shirts at INR 400 per shirt, slated to rise by 5% per annum on account of inflation. Other direct manufacturing costs at INR 125 per shirt with an annual increase of 5% per annum on account of inflation Selling, general, and administrative expenses (including employee expenses) at INR 35 million per annum, expected to increase by 10% each year. Depreciation expense on the basis of SLM. Tax rate was assumed to be 25%. . 1. Optimistic Scenario: a. Construct yearly Income Statement for the project b. Identify the yearly operating after-tax cash flows (OCF) for this project c. What is the initial investment? d. Identify terminal cash flow for this project. e. Perform NPV and IRR calculations for this project and provide results of decision rules. 2. Expected Scenario: a. Construct yearly Income Statement for the project b. Identify the yearly operating after-tax cash flows (OCF) for this project c. What is the initial investment? d. Identify terminal cash flow for this project. e. Perform NPV and IRR calculations for this project. Learning Outcomes By the end of this case study, students should be able to: Understand the concept and importance of capital budgeting and investment decision- making. Discuss and identify the various information required for financial evaluation of investment proposals. Understand and calculate after-tax operating cash flows for capital budgeting analysis, taking into account the revenues, costs, depreciation, and working capital. Determine terminal year cash flows after considering salvage value and working capital. Understand the computation of cost of capital for determination of discounting rate. Determine the Net Present Value (NPV) and Internal Rate of Return (IRR) for accepting or rejecting a capital budgeting proposal. Introduction On September 20, 2020, Mahesh Sharma, Senior Vice President of Vardhman Group, a leader of the textile industry, was preparing for a meeting with the management committee scheduled the next week. On his desk was a capital budgeting and investment proposal - a new product line of branded shirts that the committee was considering for launch. As the head of the finance department, Mahesh was required to work along with his team on a detailed capital budgeting analysis and present the findings to the management committee for their approval. As per standard company practice, each capital budgeting and investment project was evaluated using the traditional Net Present Value (NPV) approach and the Internal Rate of Return (IRR) criterion for determining if the company would undertake the project. Mahesh had a lot to think about as he considered the analysis of the capital budgeting project using the traditional Net Present Value (NPV) approach and the Internal Rate of Return (IRR) criterion. What would be the basis for calculating the after-tax operating cash flows for the capital project? How would he arrive at the depreciation and working capital requirements for computing the NPV? What would be the basis for calculating the terminal year cash flows? With all these questions in mind, Mahesh decided to focus on the proposed capital budgeting project for the next few days. Indian Retail Market The Indian retail market is at the cusp of a sweet spot driven by strong GDP (Gross Domestic Product) growth, benign inflation, and rising per capita income and purchasing power of consumers. Currently, the retail industry accounts for more than ten percent of the Indian Gross Domestic Product and approximately eight percent of employment. The industry is expected to nearly double, from US$ 600 billion in 2020 to US$ 1 trillion, by 2025 driven by income growth, urbanization, and attitudinal shifts. It has been estimated that, by 2035, the Indian apparel market, in particular, is expected to grow at a CAGR (compounded annual growth rate) of approximately 10-12%, backed by increasing affordability on account of an increase in disposable incomes, increase in aspirations, and a shift from unbranded to branded products by the burgeoning middle class. This trend is likely to be further accentuated by the rise of e-commerce companies that enable shopping from anywhere, thereby leading to increased penetration in small cities and towns. Company Background Vardhman Group is a leading textile firm based in Ludhiana, India. It was founded in 1965 by Lala Oswal, a retired executive. Since then, the company had grown steadily by catering to middle to low income consumers in the Punjab Region. The company recorded a stellar growth of 50% in its sales during the last financial year of 2019-20. With a healthy operating margin ratio and low leverage levels, the company had been able to grow its profits at a CAGR of 25% during the last 10 years. With a good brand name and healthy financial metrics, the company was now looking to expand its footprint to new product lines catering to middle to high income customers. Project Investment Proposal Details The project is estimated to be of 10 years duration. It involves setting up new machinery with an estimated cost of as much as INR 500 million, including installation. This amount could be depreciated using the straight line method (SLM) over a period of 10 years with a resale value of INR 15 million. The project would require an initial working capital of INR 20 million with cumulative investment in net working capital to be maintained at 10% of each year's projected revenue. With the planned new capacity, the company would be able to produce 240,000 pieces of shirts each year for the next 10 years. In terms of pricing, each shirt can initially be sold at INR 1,300 apiece, which takes into account the target segment and competitor pricing. The project proposal incorporates an annual increase of 3% in the price of the shirt to compensate for inflationary impact. With regards to the raw material costs and other expenses, the project estimated the following details: Raw material cost for manufacturing shirts at INR 400 per shirt, slated to rise by 5% per annum on account of inflation. Other direct manufacturing costs at INR 125 per shirt with an annual increase of 5% per annum on account of inflation Selling, general, and administrative expenses (including employee expenses) at INR 35 million per annum, expected to increase by 10% each year. Depreciation expense on the basis of SLM. Tax rate was assumed to be 25%. . 1. Optimistic Scenario: a. Construct yearly Income Statement for the project b. Identify the yearly operating after-tax cash flows (OCF) for this project c. What is the initial investment? d. Identify terminal cash flow for this project. e. Perform NPV and IRR calculations for this project and provide results of decision rules. 2. Expected Scenario: a. Construct yearly Income Statement for the project b. Identify the yearly operating after-tax cash flows (OCF) for this project c. What is the initial investment? d. Identify terminal cash flow for this project. e. Perform NPV and IRR calculations for this project