GRAND CANYON FIN504 MODULE 3,4,5,6,7 HOMEWORK AND FINAL PAPER

  

MODULE 3Details:Using Excel, and the Gitman chapter 5 Excel resource, if needed, complete the following problems from chapter 5 in Principles of Managerial Finance:P5-2P5-3P5-13P5-20P5-30P5-36P5-43Please show all work for each problem.Uma Corp.Present Value of Expected Future SavingsPeriod: 2013 through 2023Discount rate for years 2013 – 2018 0.07Discount rate for years 2019 – 2023 0.11Annual PresentYear Period Savings PV Lump Sum PV Annuity PV Lump Sum Value2013 1 110000 =C10/(1+$E$5)^B10 =D102014 2 120000 =C11/(1+$E$5)^B11 =D112015 3 130000 =C12/(1+$E$5)^B12 =D122016 4 150000 =C13/(1+$E$5)^B13 =D132017 5 160000 =C14/(1+$E$5)^B14 =D142018 6 150000 =C15/(1+$E$5)^B15 =D152019 7 90000 =C16*((1-(1/(1+$E$6)^B14))/$E$6) =E16/(1+$E$5)^B15 =F162020 8 900002021 9 900002022 10 900002023 11 90000=SUM(C10:C20) =SUM(G10:G20)MODULE 4Details:Using Excel, and the Gitman chapters 6 and 7 Excel resource, if needed, complete the following problems from chapters 6 and 7 in Principles of Managerial Finance:P6-1P6-10P6-13P6-20P7-1P7-6P7-14Please show all work for each problem.Chapter 6 Interest Rates and Bond ValuationGiven Data:CSM CorporationCoupon-interest rate 0.06Par Value 1000Maturity 15 yearsCurrent bond price 874.42Assume that interest on the CSM bond is paid semiannually.Compound period m 2Modified n = =B7*B11Modified payment = =(B5*B6)/B11Annual r 0.074modified r = =B14/B11Trial and Error method: Choose various required rates to determine the current bond value.Cell B14 should be blank.Cell B15 should have the formula: B14/B11The spreadsheet will recalculate each time. Continue the process until the value of the bondequals the current price of the bond (in this problem it is a premium of $874.42.Remember that for a bond to be priced at a premium, the coupon rate must be greater than the YTM.Remember that for a bond to be priced at a discount, the coupon rate must be less than the YTM.Year Periods Payment PV Payments2011 01 =$B$13 =(C31/((1+$B$15)^B31)) =$B$13 PVA =G31*((1-(1/((1+$B$15)^B60)))/$B$15)2012 2 =$B$13 =(C32/((1+$B$15)^B32))3 =$B$13 =(C33/((1+$B$15)^B33))2013 4 =$B$13 =(C34/((1+$B$15)^B34)) =B6 PV =(G34/((1+$B$15)^B61))5 =$B$13 =(C35/((1+$B$15)^B35))2014 6 =$B$13 =(C36/((1+$B$15)^B36)) Bond Value =SUM(I31:I35)7 =$B$13 =(C37/((1+$B$15)^B37))2015 8 =$B$13 =(C38/((1+$B$15)^B38)) Current Bond Value =B89 =$B$13 =(C39/((1+$B$15)^B39))2016 10 =$B$13 =(C40/((1+$B$15)^B40))11 =$B$13 =(C41/((1+$B$15)^B41))2017 12 =$B$13 =(C42/((1+$B$15)^B42)) Prove the following:13 =$B$13 =(C43/((1+$B$15)^B43)) When k = 0.08 value = 827.082018 14 =$B$13 =(C44/((1+$B$15)^B44)) ????? value = =B815 =$B$13 =(C45/((1+$B$15)^B45)) 0.06 value = 10002019 16 =$B$13 =(C46/((1+$B$15)^B46))17 =$B$13 =(C47/((1+$B$15)^B47))2020 18 =$B$13 =(C48/((1+$B$15)^B48))19 =$B$13 =(C49/((1+$B$15)^B49))2021 20 =$B$13 =(C50/((1+$B$15)^B50))21 =$B$13 =(C51/((1+$B$15)^B51))2022 22 =$B$13 =(C52/((1+$B$15)^B52))23 =$B$13 =(C53/((1+$B$15)^B53))2023 24 =$B$13 =(C54/((1+$B$15)^B54))25 =$B$13 =(C55/((1+$B$15)^B55))2024 26 =$B$13 =(C56/((1+$B$15)^B56))27 =$B$13 =(C57/((1+$B$15)^B57))2025 28 =$B$13 =(C58/((1+$B$15)^B58))29 =$B$13 =(C59/((1+$B$15)^B59))2026 30 =$B$13 =(C60/((1+$B$15)^B60))2026 30 =B6 =(C61/((1+$B$15)^B61))Bond Value =SUM(D31:D61)Current Bond Price =B8Chapter 7 Stock ValuationGiven Data:Most Recently Paid Dividend $3.00 DoGrowth Rate in Earnings 7% gRequired rate of return 10% rmodel: Po = (Do (1 + g )) / (r – g)Current Price of stock Po = $107.00One year later:Most Recently Paid Dividend $3.21 DoGrowth Rate in Earnings 7% gRisk Premium 6.74% RPat-bill rate 5.25% RfNew required return 11.99% rnewNew intrinsic value of stock $68.83 PoMODULE 5Details:Using Excel, and the Gitman chapters 8 and 9 Excel resource, if needed, complete the following problems from chapters 8 and 9 in Principles of Managerial Finance:P8-1P8-4P8-14P8-23P9-1P9-2P9-17Please show all work for each problem.Forecasted Returns, Expected Values, and Standard Deviations forAssets A, B, and C and Portfolios AB, AC, and BCAssets PortfoliosYear A B C AB AC BC2013 0.1 0.1 0.12 =SUMPRODUCT($C$24:$D$24,C7:D7) =($C$24*C7)+($E$24*E7) =SUMPRODUCT($D$24:$E$24,D7:E7)2014 0.13 0.11 0.14 =SUMPRODUCT($C$24:$D$24,C8:D8) =($C$24*C8)+($E$24*E8) =SUMPRODUCT($D$24:$E$24,D8:E8)2015 0.15 0.08 0.1 =SUMPRODUCT($C$24:$D$24,C9:D9) =($C$24*C9)+($E$24*E9) =SUMPRODUCT($D$24:$E$24,D9:E9)2016 0.14 0.12 0.11 =SUMPRODUCT($C$24:$D$24,C10:D10) =($C$24*C10)+($E$24*E10) =SUMPRODUCT($D$24:$E$24,D10:E10)2017 0.16 0.1 0.09 =SUMPRODUCT($C$24:$D$24,C11:D11) =($C$24*C11)+($E$24*E11) =SUMPRODUCT($D$24:$E$24,D11:E11)2018 0.14 0.15 0.09 =SUMPRODUCT($C$24:$D$24,C12:D12) =($C$24*C12)+($E$24*E12) =SUMPRODUCT($D$24:$E$24,D12:E12)2019 0.12 0.15 0.1 =SUMPRODUCT($C$24:$D$24,C13:D13) =($C$24*C13)+($E$24*E13) =SUMPRODUCT($D$24:$E$24,D13:E13)Statistics:Expected value =AVERAGE(C7:C13) =AVERAGE(D7:D13) =AVERAGE(E7:E13) =AVERAGE(G7:G13) =AVERAGE(H7:H13) =AVERAGE(I7:I13)Standard deviation =STDEV(C7:C13) =STDEV(D7:D13) =STDEV(E7:E13) =STDEV(G7:G13) =STDEV(H7:H13) =STDEV(I7:I13)Note: In each two stock portfolio, the weights of each security is equal0.5 0.5 0.5Chapter 9 The Cost of CapitalNova CorporationDebtNet Proceedsmaturity 10 Required bond price 980coupon rate 6.50% Flotation percent 2.00%par $1,000 Flotation cost $20coupon payment $65 Net Proceeds $960Trial and error YTM 7.0714%End of Year(s) Cash Flow PV0 $960 $960.001 – 10 $(65) (455.03)10 $(1,000) (504.97)$0.00 (The YTM of 7.0714% equates the NPV to zero)Before-tax Cost of Debt 7.0714%Tax rate 40.00%After-tax Cost of Debt 4.24% (a)Preferred StockPar value $100.00 Expected sale price $102.00Annual percentage rate 6% Flotation cost $4.00Annual dividend $6.00 Net Proceeds $98.00Cost of Preferred Stock 6.12% (b)Common StockGordon ModelExpected dividend $3.25Expected growth rate 5%Current price $35.00Flotation cost $2.00Adjusted Price $33.00 Cost of Common Stock 14.85% (c)Weighted Average Cost of CapitalWeight in debt 0.35Weight in preferred stock 0.12Weight in common stock 0.53Sum of weights 1.00 WACC 10.09% (d)MODULE 6Details:Using Excel, and the Gitman chapters 10, 11, and 12 Excel resource, if needed, complete the following problems from chapters 10, 11, and 12 inPrinciples of Managerial Finance:P10-1P10-5P10-21P11-3P11-12P12-2Integrative Case 5: Lasting Impressions CompanyPlease show all work for each problem.The Drillago CompanyCalculation of the NPV, IRR, and the Payback PeriodFacts of case:maturity (n) 10 yearscost-of-capital (k) 0.13Initial outlay (pv) 15000000Excel function =IRR(B12:B22)Estimated Trial and error 0.147630974Cash NPV Technique IRR TechniqueYear Outflows/Inflows PV PV Payback Technique0 =-B7 =B12/(1+$B$6)^A12 =B12/(1+$E$9)^A121 600000 =B13/(1+$B$6)^A13 =B13/(1+$E$9)^A13 =B12+B132 1000000 =B14/(1+$B$6)^A14 =B14/(1+$E$9)^A14 =G13+B143 1000000 =B15/(1+$B$6)^A15 =B15/(1+$E$9)^A15 =G14+B154 2000000 =B16/(1+$B$6)^A16 =B16/(1+$E$9)^A16 =G15+B165 3000000 =B17/(1+$B$6)^A17 =B17/(1+$E$9)^A17 =G16+B176 3500000 =B18/(1+$B$6)^A18 =B18/(1+$E$9)^A18 =G17+B187 4000000 =B19/(1+$B$6)^A19 =B19/(1+$E$9)^A19 =G18+B19 =G19/B198 6000000 =B20/(1+$B$6)^A20 =B20/(1+$E$9)^A20 =G19+B209 8000000 =B21/(1+$B$6)^A21 =B21/(1+$E$9)^A21 =G20+B2110 12000000 =B22/(1+$B$6)^A22 =B22/(1+$E$9)^A22 =G21+B22=SUM(C12:C22) =SUM(E12:E22) =A18+H19 yearsRecap:NPV =C23 Accept the project as the NPV > 0.IRR =E8 Approximately as it equates the NPV to Zero.Accept the project as the IRR (14.76%) > Cost of Capital (13%)Payback =H23 years approximatelyThe Damon CorporationCalculation of the Initial InvestmentInstalled cost of proposed machineCost of proposed machine $145,000plus: Installation costs 15,000Total installed cost – proposed $160,000(depreciable value)After-tax proceeds from sale of present machineProceeds from sale of present machine $70,000less: Tax on sale of present machine 14,080Total after-tax proceeds – present $55,920Change in net working Capital 18,000Initial investment $122,080Tax on sale of old machine Change in Working Capitalcost of old machine $120,000 Increase in receivables $15,000MACRS increase in inventory 19,000year 1 20% 24,000 increase in payables 16,000year 2 32% 38,400 Net working capital $18,000year 3 19% 22,800Book Value $34,800Sale price of old machine $70,000Gain on sale $35,200Tax rate 40%Tax Expense $14,080Depreciation Expense for Proposed and PresentMachines for the Damon CorporationYear Cost Applicable MACRS depreciation DepreciationWith proposed machine1 $160,000 20% $32,0002 160,000 32% 51,2003 160,000 19% 30,4004 160,000 12% 19,2005 160,000 12% 19,2006 160,000 5% 8,000Total 100% $160,000With present machine1 $120,000 12% $14,4002 120,000 12% 14,4003 120,000 5% 6,0004 05 06 0Total $34,800Calculation of Operating Cash Inflows for Damon CorporationProposed and Present MachinesYear 1 Year 2 Year 3 Year 4 Year 5 Year 6With proposed machineEarnings before depr. and int. and taxes $105,000 $110,000 $120,000 $120,000 $120,000 $-Depreciation #REF! #REF! #REF! #REF! #REF! #REF!Earnings before interest and taxes #REF! #REF! #REF! #REF! #REF! #REF!Taxes 40% #REF! #REF! #REF! #REF! #REF! #REF!Net operating profit after taxes #REF! #REF! #REF! #REF! #REF! #REF!Depreciation #REF! #REF! #REF! #REF! #REF! #REF!Operating cash inflows #REF! #REF! #REF! #REF! #REF! #REF!With present machineEarnings before depr. and int. and taxes $95,000 $95,000 $95,000 $95,000 $95,000 $-Depreciation #REF! #REF! #REF! #REF! #REF! #REF!Earnings before interest and taxes #REF! #REF! #REF! #REF! #REF! #REF!Taxes 40% #REF! #REF! #REF! #REF! #REF! #REF!Net operating profit after taxes #REF! #REF! #REF! #REF! #REF! #REF!Depreciation #REF! #REF! #REF! #REF! #REF! #REF!Operating cash inflows #REF! #REF! #REF! #REF! #REF! #REF!The Damon CorporationCalculation of the Terminal Cash FlowAfter-tax proceeds from sale of proposed machineProceeds from sale of proposed machine $24,000Book value as of end of year 5 8,000Net gain $16,000Tax on gain 40% 6,400Total after-tax proceeds – proposed $9,600After-tax proceeds from sale of present machineProceeds from sale of present machine $8,000Book value as of end of year 5 0Net gain $8,000Tax on gain 40% 3,200Total after-tax proceeds – present $4,800Change in net working capital #REF!Terminal Cash Flow #REF!Mutually Exclusive ProjectsProject Alpha Project BetaAnnual AnnualCash 10% Cash 10%Year Outflow/Inflow PVIF NPV PVIFA ANPV Year Outflow/Inflow PVIF NPV PVIFA ANPV0 -5,500,000 1.0000 $(5,500,000) 0 -6,500,000 1.0000 $(6,500,000)1 300,000 0.9091 272,727 1 400,000 0.9091 363,6362 500,000 0.8264 413,223 2 600,000 0.8264 495,8683 500,000 0.7513 375,657 3 800,000 0.7513 601,0524 550,000 0.6830 375,657 4 1,100,000 0.6830 751,3155 700,000 0.6209 434,645 5 1,400,000 0.6209 869,2906 800,000 0.5645 451,579 6 2,000,000 0.5645 1,128,9487 950,000 0.5132 487,500 7 2,500,000 0.5132 1,282,8958 1,000,000 0.4665 466,507 8 2,000,000 0.4665 933,0159 1,250,000 0.4241 530,122 9 1,000,000 0.4241 424,098 5.759010 1,500,000 0.3855 578,315 $350,116 $60,79411 2,000,000 0.3505 700,98812 2,500,000 0.3186 796,577 6.8137$383,499 $56,284Reviewing the NPV’s calculated for the two mutually exclusive projects, we see that project Alpha would be preferred over project Betaas Alpha has a NPV of $383,499 relative to the NPV of Beta which is $350,116.However, when we compare these mutually exclusive projects on the basis of their respective ANPVs, project Beta would be preferred overproject Alpha because it provides the higher annualized net present value ($60,794 versus $56,284).MODULE 7Details:Using Excel, and the Gitman chapters 13, 14, 15, and 16 Excel resource, if needed, complete the following problems from chapters 13, 14, 15, and 16 in Principles of Managerial Finance:P13-5P13-22P14-3P14-15P15-4P15-5P15-10P16-18P16-20Please show all work for each problem.Chapter 16 Current Liabilities ManagementFixed Rate LoanGiven Data:Days 365Loan $200,000.00Prime Rate 7.00%Maturity 60 daysPrime Excess 2.00%a. The total dollar interest cost on the First American LoanLoan Prime+ Maturity Total Dollar Interest$200,000.00 9.00% 0.164383562 $2,958.90b. The 60-day rate on the loanTotal Dollar Interest Loan 60-day Rate$2,958.90 $200,000.00 1.4795%c. Effective annual rate of interest on fixed 60-day loan60-day Rate Periods in Year Effective Annual Rate1.4795% 6.083333333 9.3453%Floating Rate LoanGiven Data:Days 365Loan $200,000.00Prime Rate 7.00% 7.50%$$Maturity 60 30Prime Excess 1.50%d. The Initial RatePrime Rate Prime Excess Initial Rate-1st 30 day rate7.00% 1.50% 8.50%e. Interest Rate for first and last 30-day periodsIntial Rate + Maturity First 30 Day Rate8.50% 0.082191781 0.6986%initial rate + Maturity Last 30 day rate9.00% 0.082191781 0.7397%f. Total Dollar Interest CostLoan 1st 30 Days Last 30-Days Total Interest Cost$200,000.00 0.6986% 0.7397% $2,876.71g. 60-Day rate of InterestTotal Interest Cost Loan 60-Day Rate$2,876.71 $200,000.00 1.4384%h. Effective Annual Interest Rate on 60-Day Loan60-Day Rate Periods in Year Effective Annual Rate1.4384% 6.083333333 9.0762%Chapter 13 Leverage and Capital StructureCalculation of Share ValueEstimates Associated withAlternative capital StructuresCapital Structure Expected Estimated EstimatedDebt Ratio EPS Required Return Share Value0 1.75 0.114 =B10/C1010 1.9 0.118 =B11/C1120 2.25 0.125 =B12/C1230 2.55 0.1325 =B13/C1340 3.18 0.18 =B14/C1450 3.06 0.19 =B15/C1560 3.1 0.25 =B16/C16Rock-O CorporationStockholders’ Equity SectionBefore the Reverse Stock SplitCommon stock 900,000 shares $1.00 par $900,000Paid-in-Capital 7,000,000Retained Earnings 3,500,000Total Stockholders’ Equity $11,400,000Reverse Stock SplitStock Split 2 3Common stock 600,000 shares $1.50 par $900,000Paid-in-Capital 7,000,000Retained Earnings 3,500,000Total Stockholders’ Equity $11,400,000Analysis of Initiating a Cash Discountfor Eboy CorporationIncrease in units due to discount 50Selling price @net 30 $4,200Variable Cost Per Unit $2,600Additional Profit Contribution from Sales: $80,000Cost of Marginal Investment in AccCounts ReceivableVariable cost per unit $2,600Raw Material annual usage 1450Accounts Receivable $443,000Sales $3,544,000Days 365Collection Period 45.625AR Turnover 8.0Average investment presently (w/o discounts) $471,250Variable cost per unit $2,600.00Raw Material annual usage 1500Expected AR Turnover due to discount 12.0Average investment presently (with cash discounts) $325,000Reduction in accounts receivable investment $146,250Opportunity cost of funds 12.5%Cost Savings from reduced investment in AR $18,281Cash Discount term 2.00%Percentage of customers to take discount 70%Raw Material annual usage (new) 1500Selling price per unit $4,200Cost of Cash Discount $88,200Net Profit from initiation of proposed cash discount $10,081MODULE 8Details:Complete your 2,500-word (excluding tables, figures, and addenda) financial analysis of your chosen company selected in Module 2.Following the nine-step assessment process introduced below and detailed in Assessing a Companys Future Financial Health:Analysis of fundamentals: goals, strategy, market, competitive technology, and regulatory and operating characteristics.Analysis of fundamentals: revenue outlook.Investments to support the business unit(s) strategy(ies).Future profitability and competitive performance.Future external financing needs.Access to target sources of external finance.Viability of the 3-5-year plan.Stress test under scenarios of adversity.Current financing plan.As you conduct the analysis, you will compile research on your chosen company, including analyst reports and market information. Disclose all assumptions made in the case study (e.g., revenue growth projections, expense controls) and provide supporting reasons and evidence behind those assumptions.Finally, in order to assess the long-term financial health of the chosen company, synthesize the research data and outcomes of the nine-step assessment process.Prepare this assignment according to the APA guidelines found in the APA Style Guide, located in the Student Success Center. An abstract is not required.This assignment uses a grading rubric. Instructors will be using the rubric to grade the assignment; therefore, students should review the rubric prior to beginning the assignment to become familiar with the assignment criteria and expectations for successful completion of the assignment.