Genesis Energy Capital Plan Report

profilemorpre
m5a2_template.xlsx
Home>Business & Finance homework help>Genesis Energy Capital Plan Report

M5A2_Template

Module 5, Assignment 2
Genesis WACC
Item Amount ($000) % Interest Weighted
Total Rate Rate
Accounts Payable 300,000 7.50% 8.00% 0.60%
Short-term Note Payable 100,000 2.50% 8.00% 0.20% NOTE: It was assumed that the interest rates were those given in modules 3 and 4. No information was given in this Module. All Items in red were calculated using these values.
Total Current Liabilities 400,000
Long-term Note Payable 400,000 10.00% 9.00% 0.90%
Mortgage Payable 1,200,000 30.00% 10.00% 3.0%
Total Liabilites 1,600,000
Common Stock Equity 1,500,000 37.50% 15.51% 5.82%
Operating Equity 500,000 12.50% 15.51% 1.94%
Total Liabilities and Equity 4,000,000 100.00%
WACC = 12.46%
Genesis Captial Projects
Initial Investment Cash Flow Cash Flow Cash Flow Cash flow Cash Flow Cashflow
Y1 Y2 Y3 Y4 Y5 Y6-10
Project A: 25-emp facility 2000 -200 -300 -400 200 400 1000
Project B: 40-emp facility 2500 -200 -200 100 400 400 1500
Project C: 75-emp facility 3000 -300 -400 -100 600 700 2000
Equipment 1 - fully automatic 1500 -100 100 200 400 200 800
Equipment 1 - semi-automatic 1000 -50 -100 200 200 300 600
Equipment 1 - manual 750 150 150 150 150 150 750
Equipment 2 - Standard 800 -175 200 250 250 300 700
Equipment 2 - top of line 1500 -100 275 325 325 325 1500
Equipment 3 - 3-man machine 700 -200 -150 250 300 350
Equipment 3 - 2-man machine 600 -175 -100 175 175 175
Equipment 3 - 5-man machine 750 -300 -200 300 400 400
In-house inspection 1800 100 500 500 300 300 800
Contract inspection 200 200 200 100 100 Calc in cell O44
=NPV($F$18,D44:M44)+C44
SOLUTION TEMPLATE
Initial Investment Cash Flow Cash Flow Cash Flow Cash flow Cash Flow Cashflow Cashflow Cashflow Cashflow Cashflow =IRR(C44:M44)
NPV of the Cash Flows Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
NPV IRR Payback
Project A: 25-emp facility ($2,000) ($200) ($300) ($400) $200 $400 $1,000 $1,000 $1,000 $1,000 $1,000 ($366.86) 10.05%) 7 years 4 months
Project B: 40-emp facility ($2,500) ($200) ($200) $100 $400 $400 $1,500 $1,500 $1,500 $1,500 $1,500
Project C: 75-emp facility ($300) ($300) ($400) ($100) $600 $700 $2,000 $2,000 $2,000 $2,000 $2,000
Equipment 1 - fully automatic ($1,500) ($100) $100 $200 $400 $200 $800 $800 $800 $800 $800
Equipment 1 - semi-automatic ($1,000) ($50) ($100) $200 $200 $300 $600 $600 $600 $600 $600
Equipment 1 - manual ($750) $150 $150 $150 $150 $150 $750 $750 $750 $750 $750
Equipment 2 - Standard ($800) ($175) $200 $250 $250 $300 $700 $700 $700 $700 $700
Equipment 2 - top of line ($1,500) ($100) $275 $325 $325 $325 $1,500 $1,500 $1,500 $1,500 $1,500
Equipment 3 - 3-man machine ($700) ($200) ($150) $250 $300 $350
Equipment 3 - 2-man machine ($600) ($175) ($100) $175 $175 $175
Equipment 3 - 5-man machine ($750) ($300) ($200) $300 $400 $400
In-house inspection ($1,800) $100 $500 $500 $300 $300 $800 $800 $800 $800 $800
Contract inspection $200 $200 $200 $100 $100
Copy and paste the choice from each selected grouping and sum the cash flows for each year. Then calculate the NPV, the IRR, and the Payback for the total project.
NPV IRR Payback

M5A2_Solution

Module 5, Assignment 2
Genesis WACC
Item Amount ($000) % Interest Weighted
Total Rate Rate
Accounts Payable 300,000 7.50% 8.00% 0.60%
Short-term Note Payable 100,000 2.50% 8.00% 0.20% NOTE: It was assumed that the interest rates were those given in modules 3 and 4. No information was given in this Module. All Items in red were calculated using these values.
Total Current Liabilities 400,000
Long-term Note Payable 400,000 10.00% 9.00% 0.90%
Mortgage Payable 1,200,000 30.00% 10.00% 3.0%
Total Liabilites 1,600,000
Common Stock Equity 1,500,000 37.50% 15.51% 5.82%
Operating Equity 500,000 12.50% 15.51% 1.94%
Total Liabilities and Equity 4,000,000 100.00%
WACC = 12.455000%
Genesis Captial Projects
Initial Investment Cash Flow Cash Flow Cash Flow Cash flow Cash Flow Cashflow
Y1 Y2 Y3 Y4 Y5 Y6-10
Project A: 25-emp facility 2000 -200 -300 -400 200 400 1000
Project B: 40-emp facility 2500 -200 -200 100 400 400 1500
Project C: 75-emp facility 3000 -300 -400 -100 600 700 2000
Equipment 1 - fully automatic 1500 -100 100 200 400 200 800
Equipment 1 - semi-automatic 1000 -50 -100 200 200 300 600
Equipment 1 - manual 750 150 150 150 150 150 750
Equipment 2 - Standard 800 -175 200 250 250 300 700
Equipment 2 - top of line 1500 -100 275 325 325 325 1500
Equipment 3 - 3-man machine 700 -200 -150 250 300 350
Equipment 3 - 2-man machine 600 -175 -100 175 175 175
Equipment 3 - 5-man machine 750 -300 -200 300 400 400
In-house inspection 1800 100 500 500 300 300 800
Contract inspection 200 200 200 100 100 Calc in cell O44
=NPV($F$18,D44:M44)+C44
SOLUTION TEMPLATE
Initial Investment Cash Flow Cash Flow Cash Flow Cash flow Cash Flow Cashflow Cashflow Cashflow Cashflow Cashflow =IRR(C44:M44)
NPV of the Cash Flows Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
NPV IRR Payback
Project A: 25-emp facility -2000 ($200.00) -300 -400 200 400 1000 1000 1000 1000 1000 ($366.86) 10.05%) 7 years 4 months
Project B: 40-emp facility -2500 -200 -200 100 400 400 1500 1500 1500 1500 1500 $679.87 15.96%) 7 years 8 months
Project C: 75-emp facility -300 -300 -400 -100 600 700 2000 2000 2000 2000 2000 $3,775.03 48.44%) 4 years 9 months
Equipment 1 - fully automatic -1500 -100 100 200 400 200 800 800 800 800 800 $577.72 17.95%) 5 years 9 months
Equipment 1 - semi-automatic -1000 -50 -100 200 200 300 600 600 600 600 600 $498.18 19.00%) 5 years 1 monrh
Equipment 1 - manual -750 150 150 150 150 150 750 750 750 750 750 $1,271.19 33.35%) 5 years
Equipment 2 - Standard -800 -175 200 250 250 300 700 700 700 700 700 $1,088.87 28.02%) 5 years
Equipment 2 - top of line -1500 -100 275 325 325 325 1500 1500 1500 1500 1500 $2,214.02 28.87%) 5 years 3 months
Equipment 3 - 3-man machine -700 -200 -150 250 300 350 ($438.47) (4.15%) No payback
Equipment 3 - 2-man machine -600 -175 -100 175 175 175 ($504.90) (13.28%) No payback
Equipment 3 - 5-man machine -750 -300 -200 300 400 400 ($491.44) (3.55%) No payback
In-house inspection -1800 100 500 500 300 300 800 800 800 800 800 $975.90 (1.81%) 5 years 2 months
Contract inspection 200 200 200 100 100 $594.77 N/A N/A
Project C: 75-emp facility -300 -300 -400 -100 600 700 2000 2000 2000 2000 2000
Equipment 1 - manual -750 150 150 150 150 150 750 750 750 750 750
Equipment 2 - top of line -1500 -100 275 325 325 325 1500 1500 1500 1500 1500
Equipment 3 - 3-man machine -700 -200 -150 250 300 350
In-house inspection -1800 100 500 500 300 300 800 800 800 800 800 NPV IRR Payback
Total -5050 -350 375 1125 1675 1825 5050 5050 5050 5050 5050 $7,797.67 29.21%) 5 years 1 month