Pleas see the file and do like that - there is basically no reference. To follow it’s excel and some questions that have. To be answered
Part C Q1 1 a.Complete the spreadsheet to estimate the project's annual after-tax cash flows. b.What is the investment's net present value at a discount rate of 10 percent? c.What is the investment's internal rate of return? Facts and Assumptions Equipment initial cost$350,000 Depreciable life yrs.7 Expected life yrs.10 Salvage value$0 Straight line depreciation EBIT in year 1$28,000 Tax rate38% Growth rate in EBIT3% Discount rate10% a.Year012345678910 Initial cost Annual depreciation EBITHint - EBIT= Previous Year's EBIT*(1+Growth Rate) Taxes EBIT after tax After-tax cash flowHint - After Tax Cash Flow adds back depreciation After-tax cash flow of year 0 should be entered as negative b. c.NPVNPV= NPV(Discout Rate, Year 1:Year 10) - Initial Cost IRR Part C Q2 2 A company is considering two mutually exclusive methods of producing a new product. The relevant data concerning the alternatives appear below. At the end of the useful life of whatever equipment is chosen the product will be discontinued. The company's tax rate is 25 percent and the discount rate is 10 percent.Alternative 1Alternative 2 a.Calculate the net present value of each alternative.a.NPV b.Calculate the benefit-cost ratio for each alternative.b.BCRHint - BCR = NPV(from Year 1 to the end)/Initial Cost Alternative 1Alternative 2Other assumptions Initial investment$55,000$120,000Tax rate25% Annual receipts$50,000$60,000Discount rate10% Annual disbursements$20,000$12,000 Annual depreciation$16,000$20,000 Expected life (years)46 Salvage value$0$0 0123456 Alternative 1 Initial investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After-tax cash flow Alternative 2 Initial investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After-tax cash flow Part C Q3 Cost Benefit Analysis Setting Up Recruiting Department - Cost/Benefit Analysis ASSUMPTIONS Company description New Brunswick, NJ - Based Insurance Company with 1,000 Average salaries$50,000 per year increasing approximately 3% per year Number of open positions per year100 positions based on 10% annual turnover Current statePaying $10,000 per open requisition to outside agencies amounting to approximately $1,000,000 per year. RecommendationSet up internal recruitment department to save on recuiting costs. PERIOD012345 YEAR201820192020202120222023 TOTAL ANNUAL COMPANY SALARIES5,000,0005,150,0005,304,5005,463,6355,627,544 RECRUITMENT CENTER COSTS Recruiting Office Equipmemt and Furniture-200,000 Applicant tracking system development-200,000 Hiring costs to hire three recruiters and assistant-200,000 Information Security and Testing-250,000 Total direct costs-850,00000000 Total salvage value000000 BENEFITS Reduced recruiting costs ($1,000,000 minus direct expenses)635,000619,450602,733584,840571,173 Reduced legal costs for employment agency contracts50,00055,00060,00065,00070,000 Total direct benefits685,000674,450662,733649,840641,173 EXPENSES Recruiting assistant salary with 3% annual increase-35,000-36,050-37,132-38,250-39,400 Three recruiter salaries with 3% annual increases-150,000-154,500-159,135-163,910-168,827 IT support and other allocated expenses-100,000-110,000-121,000-133,000-140,600 Depreciation on furniture and equipment (no salvage)-80,000-80,000-80,000-80,000-80,000 Total direct expenses-365,000-380,550-397,267-415,160-428,827 Additional inc before tax320,000293,900265,466234,680212,346 Tax at 21%-67,200-61,719-55,748-49,283-44,593 Additional inc after tax252,800232,181209,718185,397167,753 Add back depreciation80,00080,00080,00080,00080,000 After-tax cash flow332,800312,181289,718265,397247,753 FREE CASH FLOW-850,000332,800312,181289,718265,397247,753 Discount Rate15% Indirect benefits - recruiters helping to build employer brand and company culture Indirect costs - recruiters may not know the labor market as well as employment agencies aWhat is the investment's net present value at a discount rate of 15 percent? bWhat is the investment's internal rate of return? c.What is the investment's Benefit Cost Ratio Finance is the study of managing money HRM and FinanceAssignment 4 Readings: · Canvas Downloads · Assignment 4 Excel Problems · Advanced Time Value of Money Calculator and Problem Sets Spreadsheet Assignment: Upload to the Assignments Folder in Canvas your answers to Sections A-C. Answers to question should typically be in one to two complete sentences but please provide more detail if context required – however in some instances (for example, financial problems), a simple answer of a number may be all that is appropriate. Please note that spelling, grammar, punctuation, capitalization and style are all part of an effective presentation and report and will be considered in evaluating all submitted work product. Often it is not just what you know but how you present it that matters. Assignment 4 is due by 11:59PM on March 25 Questions: A. Time Value of Money; Capital Budgeting Complete the problem sets below. Please submit some form of work product on these items – submitting answers alone will not get you credit for the work. This may include scanned copies of work papers, Excel spreadsheets, etc. You may use the Advanced Time Value Excel Spreadsheet to assist with these problems although many may be completed with a calculator, formulas or other methods. B.HRM Analysis and Decisions Consider the following circumstances where a company must make a decision on whether and/or how it should invest in its employees Please submit some form of work product on these items – submitting answers alone will not get you credit for the work. B1. Currently employed insurance adjuster seeks employer’s approval to obtain an insurance sales license from Insured Assurance, Inc. by completing an intensive sales licensing program over the next three months on a full-time basis. Assumptions: Direct costs Tuition, fees, materials and two weekend conferences totaling $24,500 Indirect costs Lost earnings while participating in program totaling $40,000 Interest rate 2% Expected increased productivity (from sales and improved service) upon completion of program Period (Year) 1 - $27,000 Period (Year) 2 - $30,000 Period (Year) 3 - $32,000 a. What is the Net Present Value and Benefit Cost Ratio for the company sponsoring the professional in obtaining this license respectively? b. Do the Net Present Value and Benefit Cost Ratio support the decision to put the employee into this program? (answer in 1-2 sentences) c. What is the minimum amount of increased productivity in Year 3 that can be achieved to make the company indifferent from a financial perspective of investing in its employee? (Hint: consider its Breakeven Levels at different values for Year 3 until NPV equals 0] B2. Currently employed computer programmer seeks to obtain a Microsoft Engineer license from DeVry University’s School of Continuing Education by completing five courses over the next four months (Summer and Fall semesters) on a full-time basis. Assumptions: Direct costs Tuition, fees and books totaling $7,300 Indirect costs Lost earnings while participating in program totaling $30,000 Interest rate 2% Expected increased productivity (and earnings potential) upon completion of program Period (Year) 1 - $6,000 Period (Year) 2 - $8,000 Period (Year) 3 - $10,000 Period (Year) 4 - $12,000 a. What is the Net Present Value and the Benefit Cost Ratio for the company of investing in this program for the employee respectively? b. What are the advantages and limitations of each of these metrics? c. What is the minimum amount of increased productivity in Year 4 that can be achieved to make the company indifferent from a financial perspective of investing in its employee (Hint: consider its Breakeven Levels at different values for Year 4 until NPV equals 0] B 3. Firm is considering putting one of its top individual contributors through its Leadership Development Program over the next three months. The program has occasional travel requirements. Upon completion, the employee should be able to lead a division. Assumptions: Direct costs Training fees, materials and travel expenses totaling $50,000 Indirect costs Lost productivity while participating in program totaling $40,000 Interest rate 4% Expected increased productivity upon completion of program Period (Year) 1 - $40,000 Period (Year) 2 - $48,000 Period (Year) 3 - $56,000 Period (Year) 4 - $60,000 a. What is the Net Present Value and the Benefit Cost Ratio to the firm of sending the employee to the program? b. Considering your answers above, under what circumstances would a company be more inclined to invest in an employee’s training such as this? [Hint: consider a company’s current financial position, available resources and whether it makes sense for a company to base its decisions primarily on the financial consequences of its actions.] C. Excel Complete the questions included in the Excel file Assignment 4 Excel Problems (Q1-3). All items highlighted in YELLOW should be completed. Note that for your Excel submission, you need to use formulas where appropriate such as to calculate Gross Income, Margins, etc. instead of doing the math manually and submitting only numbers in the spreadsheets. -3- Time Value Calculators Advanced Time Value of Money Future Value of a Present Amount Present Value Number of Periods Interest Rate per Period Future Value$0.00 Future Value of an Annuity Payment per Period Number of Periods Interest Rate per Period Future Value$0.00 Present Value of a Future Amount Future Amount Number of Periods Interest Rate per Period Present Value$0.00 Present Value of an Annuity Payment per Period Number of Periods Interest Rate per Period Present Value$0.00 Future Value of a Present Amount (solving for time) Present Value Number of PeriodsERROR:#DIV/0! Interest Rate per Period Future Value Future Value of a Present Amount (solving for rate) Present Value Number of Periods Interest Rate per PeriodERROR:#NUM! Future Value Present Value of a Future Amount (solving for time) Future Amount Number of PeriodsERROR:#DIV/0! Interest Rate per Period Present Value Present Value of a Future Amount (solving for rate) Future Amount Number of Periods Interest Rate per PeriodERROR:#NUM! Present Value Future Value of an Annuity (solving for time) Payment per Period Number of PeriodsERROR:#DIV/0! Interest Rate per Period Future Value Future Value of an Annuity (solving for rate) Payment per Period Number of Periods Interest Rate per PeriodERROR:#NUM! Future Value Present Value of an Annuity (solving for payment) Payment per PeriodERROR:#NUM! Number of Periods Interest Rate per Period Present Value Present Value of an Annuity (solving for time) Payment per Period Number of PeriodsERROR:#DIV/0! Interest Rate per Period Present Value Sample Problems PROBLEM 10-13 NPV CALCULATION DATA Discount rate10% YearCash Flows 0-60,000 120,000 220,000 310,000 410,000 530,000 630,000 NPV PROBLEM 10-6 NPV, PI, AND IRR CALCULATIONS DATA Required rate of return12% YearProject AProject B 0(50,000)(70,000) 112,00013,000 212,00013,000 312,00013,000 412,00013,000 512,00013,000 612,00013,000 NPV = PI = IRR = PROBLEM 10-31 SIZE-DISPARITY PROBLEM DATA Discount rate10% AB YearCash FlowsCash Flows 0-50,000-50,000 115,6250 215,6250 315,6250 415,6250 515,625100,000 A) Payback Period B) NPV C) IRR