Homework 1 (Empty)

xlsx

School

Temple University *

*We aren’t endorsed by this school

Course

BA3512

Subject

Finance

Date

Apr 3, 2024

Type

xlsx

Pages

30

Uploaded by ChiefDiscovery9104

Report
Retirement Problem Interest 7% Annual Deposit 1601.17 Annual Withdrawl $10,000 Age Beginning of Year End of Year 40 1 0 41 2 1 42 3 2 43 4 3 44 5 4 45 6 5 46 7 6 47 8 7 48 9 8 49 10 9 50 11 10 51 12 11 52 13 12 53 14 13 54 15 14 55 16 15 56 17 16 57 18 17 58 19 18 59 20 19 60 21 20 61 22 21 62 23 22 63 24 23 64 25 24 65 26 25 66 27 26 67 28 27 68 29 28 69 30 29 Instead of using Solver, use PV and PMT to solve for annual de annual withdraw 10000 Interest rate 7% Number of Yrs. 10 The key is that the future value of first 20 years' deposits (ann
First solve for present value of all the withdrawals using PV fun PV 70235.82 Then use PMT to solve for the annual deposit Number of Yrs. 20 PMT 1601.17 =
Account Balance (Beginning) Deposit (Beginning) Interest Earned for the year Withdraw (End) 0.00 1,601.17 112.08 0.00 1,713.26 1,601.17 232.01 0.00 3,546.44 1,601.17 360.33 0.00 5,507.95 1,601.17 497.64 0.00 7,606.76 1,601.17 644.56 0.00 9,852.49 1,601.17 801.76 0.00 12,255.43 1,601.17 969.96 0.00 14,826.56 1,601.17 1,149.94 0.00 17,577.68 1,601.17 1,342.52 0.00 20,521.37 1,601.17 1,548.58 0.00 23,671.13 1,601.17 1,769.06 0.00 27,041.36 1,601.17 2,004.98 0.00 30,647.51 1,601.17 2,257.41 0.00 34,506.10 1,601.17 2,527.51 0.00 38,634.78 1,601.17 2,816.52 0.00 43,052.47 1,601.17 3,125.76 0.00 47,779.40 1,601.17 3,456.64 0.00 52,837.22 1,601.17 3,810.69 0.00 58,249.08 1,601.17 4,189.52 0.00 64,039.77 1,601.17 4,594.87 0.00 70,235.82 0.00 4,916.51 10,000.00 65,152.32 0.00 4,560.66 10,000.00 59,712.99 0.00 4,179.91 10,000.00 53,892.89 0.00 3,772.50 10,000.00 47,665.40 0.00 3,336.58 10,000.00 41,001.97 0.00 2,870.14 10,000.00 33,872.11 0.00 2,371.05 10,000.00 26,243.16 0.00 1,837.02 10,000.00 18,080.18 0.00 1,265.61 10,000.00 9,345.79 0.00 654.21 10,000.00 eposit: nuity due) at the end of year 20 is the same as the present value of the 10 years' withdraw (ordinary annuity)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
nction
Account Total (End) 1,713.26 3,546.44 5,507.95 7,606.76 9,852.49 12,255.43 14,826.56 17,577.68 20,521.37 23,671.13 27,041.36 30,647.51 34,506.10 38,634.78 43,052.47 47,779.40 52,837.22 58,249.08 64,039.77 70,235.82 65,152.32 59,712.99 Parameters: 53,892.89 47,665.40 Set the last cell in the row of "Accou 41,001.97 33,872.11 26,243.16 18,080.18 9,345.79 0.00 ) at the end of year 20 Quesiton 1 . You are currently 40 years old and intend to retire at easier, you intend to start a retirement account. At the beginning some money to your retirement account till your retire. You expe year. After retirement at age 60, you want to withdraw $10,000 f the end of each year for 10 years. How much money should you each year? Requirement: Use all the three methods (1) Solver, (2) Goal Seek solve for annual deposit. Use snipping tool to screenshot the dial Seek with the appropriate parameters.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
unt Total (End)" to the Value of "0" by changing the "Annual deposits". t age 60. To make your retirement g of each of years, you will deposit ect the account will earn 7% per from your retirement account at plan to deposit in your retirement k, and (3) PV and PMT function to log windows of Solver and Goal
94500
Retirement Problem Interest 7% Annual Deposit 1713.26 Annual Withdrawl $10,000 Age Beginning of Year End of Year Account Balance (Beginning) 40 1 0 0.00 41 2 1 1,713.26 42 3 2 3,546.44 43 4 3 5,507.95 44 5 4 7,606.76 45 6 5 9,852.49 46 7 6 12,255.43 47 8 7 14,826.56 48 9 8 17,577.68 49 10 9 20,521.37 50 11 10 23,671.13 51 12 11 27,041.36 52 13 12 30,647.51 53 14 13 34,506.10 54 15 14 38,634.78 55 16 15 43,052.47 56 17 16 47,779.40 57 18 17 52,837.22 58 19 18 58,249.08 59 20 19 64,039.77 60 21 20 70,235.82 61 22 21 65,152.32 62 23 22 59,712.99 63 24 23 53,892.89 64 25 24 47,665.40 65 26 25 41,001.97 66 27 26 33,872.11 67 28 27 26,243.16 68 29 28 18,080.18 69 30 29 9,345.79 Instead of using Solver, use PV and PMT to solve for annual deposit: annual withdraw 10000 Interest rate 7% Number of Yrs. 10
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
The key is that the future value of first 20 years' deposits (annuity due) at the end of year 20 First solve for present value of all the withdrawals using PV function PV 70235.82 Then use PMT to solve for the annual deposit Number of Yrs. 20 PMT 1713.26
Deposit or Withdraw (Beginning) Interest Earned for the year Deposite or Withdraw (End) 0.00 0.00 1,713.26 0.00 119.93 1,713.26 0.00 248.25 1,713.26 0.00 385.56 1,713.26 0.00 532.47 1,713.26 0.00 689.67 1,713.26 0.00 857.88 1,713.26 0.00 1,037.86 1,713.26 0.00 1,230.44 1,713.26 0.00 1,436.50 1,713.26 0.00 1,656.98 1,713.26 0.00 1,892.90 1,713.26 0.00 2,145.33 1,713.26 0.00 2,415.43 1,713.26 0.00 2,704.43 1,713.26 0.00 3,013.67 1,713.26 0.00 3,344.56 1,713.26 0.00 3,698.61 1,713.26 0.00 4,077.44 1,713.26 0.00 4,482.78 1,713.26 0.00 4,916.51 -10,000.00 0.00 4,560.66 -10,000.00 0.00 4,179.91 -10,000.00 0.00 3,772.50 -10,000.00 0.00 3,336.58 -10,000.00 0.00 2,870.14 -10,000.00 0.00 2,371.05 -10,000.00 0.00 1,837.02 -10,000.00 0.00 1,265.61 -10,000.00 0.00 654.21 -10,000.00
0 is the same as the present value of the 10 years' withdraw (ordinary annuity) at the end of year 20
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Account Total (End) 1,713.26 3,546.44 5,507.95 7,606.76 9,852.49 12,255.43 14,826.56 17,577.68 20,521.37 23,671.13 27,041.36 30,647.51 34,506.10 38,634.78 43,052.47 47,779.40 52,837.22 58,249.08 64,039.77 70,235.82 65,152.32 59,712.99 53,892.89 47,665.40 41,001.97 33,872.11 26,243.16 18,080.18 9,345.79 0.00 Parameters Set the last cell in the row o Question 2: You are currently 40 years old an start a retirement account. At the END of ea your retire. You expect the account will earn from your retirement account at the END of e Requirement: Use all the three methods (1 deposit. Use snipping tool to screenshot the
of "Account Total (End)" to the Value of "0" by changing the "Annual deposits". nd intend to retire at age 60. To make your retirement easier, you intend to ach of years, you will deposit some money to your retirement account till n 7% per year. After retirement at age 60, you want to withdraw $10,000 each year for 10 years. How much money should you plan to deposit in your retirement each year? 1) Solver, (2) Goal Seek, and (3) PV and PMT function to solve for annual dialog windows of Solver and Goal Seek with the appropriate parameters.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
94500
QX CORP. - CAPITAL PROJECT ANALYSIS Cost of machine $ 54,500,000 Tax Rate 25% Required Rate of Return 14% Material Cost per Brake Pad $ 30.00 Labor Cost per Brake Pad $ 20.00 Year 0 1 # of Brake Pads Sold 100,000 Sale Price per Brake Pad $ 200.00 Sales $ 20,000,000 Material Cost per Brake Pad $ 30.00 Labor Cost per Brake Pad $ 20.00 COGS - Material and Labor 5,000,000 COGS as a % of Revenue 25.0% Operating Expenses 500,000 Operating Expenses as a % of COGS 10.0% Depreciation Expense 10,900,000 ACCEPT OR REJECT THE PROJECT AND WHY? (Your answer here) Yes, you would accept this project because the IRR of 19.63% is higher than the Required Rate of Return of 14%.
Depreciation MACRS % 20.00% EBIT 3,600,000 Taxes 900,000 Net Income 2,700,000 Add: Depreciation 10,900,000 Deduct: Change in net working capital (You can ignore this raw) Add: After-tax scrap/residual value 54,500,000.0 Cash Flow -54,500,000 $ 13,600,000 Net Present Value $9,395,912.61 Internal Rate of Return 19.36% $9,395,913 Required rate 10% of return 12% 14% 15% 17% 19% $9,395,913 10% 12% 14% 15% 17% 19%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
FINAL 5.2.2019
2 3 4 5 6 110,000 120,000 125,000 110,000 100,000 $ 200.00 $ 200.00 $ 200.00 $ 200.00 $ 200.00 $ 22,000,000 $ 24,000,000 $ 25,000,000 $ 22,000,000 $ 20,000,000 $ 30.00 $ 30.00 $ 30.00 $ 30.00 $ 30.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 5,500,000 6,000,000 6,250,000 5,500,000 5,000,000 25.0% 25.0% 25.0% 25.0% 25.0% 550,000 600,000 625,000 550,000 500,000 10.0% 10.0% 10.0% 10.0% 10.0% 17,440,000 10,464,000 6,278,400 6,278,400 3,139,200 QX Corp. is a manufacturer of automotive brake pads for original equipment manufacturers (OEM for the Model 3. TESLA is currently not a customer and their brake pad is unique and requires a sp require the purchase of the specialized machine that will be needed to manufacture the brake pad analyst in the Financial Planning & Analysis Department. The production department supplied yo --Cost of the machine: $54,500,000 --QX will sell the following number of brake pads per year. Year 1 - 100,000 , Year 2 - 110,000, Year 3 - 120,000, Year 4 - 125,000, Year 5 - 110,000, Year 6 - -- Sales price per pad is $200.00 -- The material cost per brake pad to manufactue is expected to be $30 per pad for all eight years -- The labor cost per brake pad is expected to be $20 per pad for all eight years -- Operating expenses (not including depreciation) will be 10.0% of COGS -- The equipment will be depreciated based on the following MCARS tax rates which are: Year 1 - 20.0%, Year 2 - 32.0%, Year 3 - 19.2%, Year 4 - 11.52%, Year 5 - 11.52% and Year 6 - 5.76 -- The equipment will be sold for $1,000,000 at the end of year 8. -- Tax Rate is 25% -- Management requires a rate of return on this project of 14% REQUIRED You are to prepare an IRR and NPV analysis and recommend whether to ACCEPT or REJECT the p following sensitivities for NPV calculation. Tax rate - 20%, 22%, 24%, 25%, 27%, 29% Required rate of return - 10%, 12%, 14%, 15%, 17%, 19%
32.00% 19.20% 11.52% 11.52% 5.76% (1,490,000) 6,936,000 11,846,600 9,671,600 11,360,800 (372,500) 1,734,000 2,961,650 2,417,900 2,840,200 (1,117,500) 5,202,000 8,884,950 7,253,700 8,520,600 17,440,000 10,464,000 6,278,400 6,278,400 3,139,200 $ 16,322,500 $ 15,666,000 $ 15,163,350 $ 13,532,100 $ 11,659,800 CREATE DATA TABLE BELOW Tax rate 20% 22% 24% 25% 27% 20% 22% 24% 25% 27%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
7 8 100,000 90,000 $ 200.00 $ 200.00 $ 20,000,000 $ 18,000,000 $ 30.00 $ 30.00 $ 20.00 $ 20.00 5,000,000 4,500,000 25.0% 25.0% 500,000 450,000 10.0% 10.0% Ms). TESLA has approached them to manufacture brake pads pecialized machine. The contract will be for 8 years and will d as well as hiring additional employees. You are a financial ou with the following: 100,000, Year 7 - 100,000, Year 8 - 90,000 6% project and WHY. Also prepare a DATA TABLE assuming the
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
14,500,000 13,050,000 3,625,000 3,262,500 10,875,000 9,787,500 - - 750,000 $ 10,875,000 $ 9,037,500 29% 29%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
94500
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CHEMX, INC. - CAPITAL PROJECT ANALYSIS $ in millions Cost of machine $ 2,600,000 Residual value end of year 10 3.0% Expected COGS Savings 10.0% Annual incremental working capital $ 2,000 Tax depreciation 5 year MACRS based on cost of the machine Year 1 20% Year 2 32% Year 3 19% Year 4 12% Year 5 12% Year 6 5% Tax Rate 25% Required Rate of Return 10% PERIOD 0 1 2 Cost of goods sold - Harrisburg, PA plant $ 6,850,000 $ 7,050,000 Cost savings (=revenue) $ 685,000 $ 705,000 Less: Depreciation 520,000 832,000 EBIT 165,000 -127,000 Less: Taxes 41,250 -31,750 Net Income 123,750 -95,250 Add: Depreciation 520,000 832,000 Less: Change in net working capital 2,000 2,000 2,000 Add: After-tax scrap/residual value 2,600,000 Cash Flow -2,602,000 641,750 734,750 Cumulative Cash Flow $ 641,750 $ 1,376,500 Payback Period Payback-----> CF in year 4 not Percentage not Percentage nee plus first X year
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
NPV $ 845,143 IRR 18.7% PAYBACK PERIOD 3.883 Profitability Index 1.32
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3 4 5 6 7 8 $ 7,250,000 $ 7,450,000 $ 7,650,000 $ 7,850,000 $ 8,050,000 $ 8,250,000 $ 725,000 $ 745,000 $ 765,000 $ 785,000 $ 805,000 $ 825,000 494,000 312,000 312,000 130,000 231,000 433,000 453,000 655,000 805,000 825,000 57,750 108,250 113,250 163,750 201,250 206,250 173,250 324,750 339,750 491,250 603,750 618,750 494,000 312,000 312,000 130,000 2,000 2,000 2,000 2,000 2,000 (58,500) 665,250 634,750 649,750 619,250 601,750 560,250 $ 2,041,750 $ 2,676,500 $ 3,326,250 $ 3,945,500 $ 4,547,250 $ 5,107,500 -2,602,000 t needed $ 74,500 t needed 11.7% eded 88.3% rs ChemX, Inc. is a manufacturer of chemical products with locations in the Un States. Its manufacturing plant in Harrisburg, PA is considering the purchase new mixing machine. The cost of goods sold will be $6,850,000 in year 1, an increase by $200,000 each year . The cost of the machine is $2,600,000 and it is expected to result in cost sav 10.0% of that location's cost of goods sold over the life of the mixing machin which is 8 years. Over the 8 years an annual investment in working capital will be required of (needs to be input at the beginning of year). At the end of its life it can be so 3% of the original cost. Five year MACRS depreciation will be used for tax purposes, which has the depreciation rate of 20%, 32%, 19%, 12%, 12% and the first five years. The company's marginal tax rate is 25%. Prepare an analysis to determine if this project will generate an attractive le economic benefits. Specifically determine the net present value and interna of return, payback period, discounted payback period, profitability index of project.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
nited e of a nd will vings of ne f $2,000 old for d 5% for evel of al rate the ACCEPT OR REJECT THE PROJECT AND WHY? (Your answer here) Yes, you accept this project because this IRR of 19.1% is higher the Required Rate of Return of 10% and NPV is greater than 0.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
94500
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help