I need solution without excel. I need you to solve them manually, I need to do t
ID: 2787355 • Letter: I
Question
I need solution without excel. I need you to solve them manually, I need to do them in MS WORD, so please don't use excel
I need help in Finance.
Case Study:
the case study started here that Shiner Threads Limited (STL) is in the business of textile and is a renowned supplier of toddler and teens outfits in the industry. The company is equipped with hi-tech machinery and is recognized for its unique designs. Due to its key investment in technology and human capital, company’s financials of last five years depict a continuous increasing trend in sales and profits at the rates of 30% and 35% respectively. Company’s management is now planning to expand its business operations, for which, Research and Development (R&D) department of STL has proposed following three Mutually Exclusive projects based on their market surveys:
(It is notable that feasibility report of each project is based on the estimates of six years).
Project 1:
The first project proposed by R&D is to open company’s own Clothing Stores. Estimates presented in the feasibility report tell that this project requires an investment of Rs. 1,950,000. This will generate after tax cash inflows of Rs. 400,000 each in the first and second years, Rs. 600,000 each in 3rd , 4th & 5th years while Rs. 900,000 in 6th year. Other information which has been provided in the report is as follow:
Project 2:
The second project proposed by R&D is to open Consultancy Centers. These centers will aim to provide consultancy services to other factories of the industry and to its buyers regarding their real- life problems of doing business. This project requires an initial cost of Rs. 900,000 while it will generate after tax cash inflows of Rs. 300,000 each in first five years and Rs. 500,000 in 6th year. The required rate of return for the project is 28%. However, the feasibility report for this project is not very much comprehensive as it is providing information only about capital budgeting measure of IRR which is approximately 26.7%.
Project 3:
The third project proposed by R&D is to open Skill Development Cells. The aim of these development cells will be to launch different professional training courses in the market. The initial investment required for this project is Rs. 1,270,000. This project also requires net working capital of Rs. 180,000 at the start of the project which will be recovered at the end of 6th year. After tax cash inflows estimated to be generated from this project are as follows:
Requirements
Requirement No. 1: It can be observed that the feasibility report of project 1 is providing values against all capital budgeting measures except IRR. So, you are required to determine the approximate value of IRR to complete the report. (5 Marks)
Requirement No. 2: Although the information provided in feasibility report of project 2 is missing for most of the capital budgeting measures, however, you are required to determine only the values of NPV & Profitability Index (PI) for this project. (5 Marks)
Requirement No. 3: Determine the missing values of NPV and Payback period for project 3 to complete its feasibility report. (8 Marks)
Requirement No. 4: After finding out all the required values of each project, analyze which of the projects should be selected by STL and why? (2 Marks)
Profitability Payback Discounted Required Rate of Return 150/o IRR NPV s. 125,246 Times) 1.06 Index periodPaybaclk ears 3.92 ears 5.68Explanation / Answer
NOTE: EXCEL HAS BEEN USED ONLY FOR TABULATIONS AND SIMPLE CALCULATIONS. THE EXCEL TOOLS FOR FINDING NPV, IRR ETC HAVE NOT BEEN USED.
1) IRR is that discount rate for which the PV of cash flows equals the initial investment. Or alternatively, NPV = 0 This discount rate has to be found out by trial and error. Year Cash flow PVIF at 17% PV at 17% PVIF at 18% PV at 18% 0 -1950000 1.00000 -1950000 1.00000 -1950000 1 400000 0.85470 341880 0.84746 338983 2 400000 0.73051 292205 0.71818 287274 3 600000 0.62437 374622 0.60863 365179 4 600000 0.53365 320190 0.51579 309473 5 600000 0.45611 273667 0.43711 262266 6 900000 0.38984 350855 0.37043 333388 3420 -53437 IRR lies between 17% and 18%. The value of IRR can be calculated by simple interpolation as below: = 17+3420/(3420+53437)= 17.06% 2) NPV: Year Cash flows PVIF at 28% PV at 28% 0 -900000 1.00000 -900000 1 300000 0.78125 234375 2 300000 0.61035 183105 3 300000 0.47684 143051 4 300000 0.37253 111759 5 300000 0.29104 87311 6 500000 0.22737 113687 NPV = -26711 PI = Sum of PV of cash inflows/Initial investment = 873289/900000 = 0.97 3) NPV: Cumulative Year Cash flows Cash flows PVIF at 15% PV at 15% 0 -1450000 -1450000 1.00000 -1450000 1 250000 -1200000 0.86957 217391 2 310000 -890000 0.75614 234405 3 400000 -490000 0.65752 263006 4 460000 -30000 0.57175 263006 5 580000 550000 0.49718 288363 6 780000 1330000 0.43233 337216 NPV = 153387 Payback period = 4+30000/580000 = 4.0 years. 4.05172 4) Project 2, is to be rejected as its NPV is negative, because of which IRR<WACC and PI>1. For projects 1&3, the results are tabulated below: Initial investment (incl NWC) RRR % NPV [$] IRR % PI Pay back [years] Discounted payback [years] Project 1 1950000 15% 125246 17.06 1.06 3.92 5.68 Project 3 1450000 15% 153387 18.17 1.10 4.05 5.54 RECOMMENDATION: The question is silent on the available capital and any prescription as to maximum permssible payback. Hence, Project 3 is preferable as it gives higher NPV. Besides it has higher PI which indicates that it is more efficienty in use of funds.