Genesis Energy Cash Position Analysis

mod3_assgnment_2_help_sheet.xlsx

Home >Business & Finance homework help >Genesis Energy Cash Position Analysis

Sheet1

					PLEASE USE THE TEMPLATE PROVIDED FOR YOU!
					Please complete with (35% in second month after the sale and 30% in third month after the sale) Do not forget (Other Cash Receipts)
					which is the last line item in this section of budget
					Below I have given you a partial snapshot of the remaider of this spreadsheet you are to complete with some of the correct answers to get you started
					just like above. Keep in mind this is one spreadsheet and I have included the line items needed, but you should not attempt to copy and paste this, it will not work!

Sheet2

Sheet3

M2,A2

MODULE 2, Assignment 2
1. Calculate the future value of $100,000 ten years from now based on the following annual interest rates:							Years	IR	FV	PV
a. 2%							10	2.00%	($121,899.44)	100,000.00
b. 5%
c. 8%
d. 10%							Yr	IR	FV	PV
SOLUTION							1	8%	100,000.00	($92,592.59)	100,000.00	($92,592.59)
	$100,00 for 10 years	Answer					2	8%	150,000.00	($128,600.82)	100,000.00	($85,733.88)
							3	8%	200,000.00	($158,766.45)	100,000.00	($79,383.22)
	a. 2%	$121,899.44					4	8%	200,000.00	($147,005.97)	100,000.00	($73,502.99)
	b. 5%	$162,889.46					5	8%	150,000.00	($102,087.48)	100,000.00	($68,058.32)
	c. 8%	$215,892.50					6	8%	100,000.00	($63,016.96)		($399,271.00)
	d. 10%	$259,374.25					7	8%	100,000.00	($58,349.04)
							8	8%	100,000.00	($54,026.89)
2. Calculate the present value of a stream of cash flows based on a discount rate of 8%. Annual cash flow is as follows:							9	8%	100,000.00	($50,024.90)
a. Year 1 = $100,000							10	8%	100,000.00	($46,319.35)
b. Year 2 = $150,000										($900,790.45)
c. Year 3 = $200,000
d. Year 4 = $200,000
e. Year 5 = $150,000
f. Years 6-10 = $100,000
SOLUTION
a. Year 1 = $100,000			$92,592.59
b. Year 2 = $150,000			$128,600.82
c. Year 3 = $200,000			$158,766.45
d. Year 4 = $200,000			$147,005.97
e. Year 5 = $150,000			$102,087.48
f. Years 6-10 = $100,000			$271,737.14
		Sum =	$900,790.45
3. Calculate the present value of the cash flow stream in problem 2 with the following interest rates:
a. Year 1 = 8%
b. Year 2 = 6%
c. Year 3 = 10%
d. Year 4 = 4%
e. Year 5 = 6%
f. Years 6-10 = 4
SOLUTION
a. Year 1 = 8%	1	100,000.00	8%	$92,592.59
b. Year 2 = 6%	2	150,000.00	6%	$133,499.47
c. Year 3 = 10%	3	200,000.00	10%	$150,262.96
d. Year 4 = 4%	4	200,000.00	4%	$170,960.84
e. Year 5 = 6%	5	150,000.00	6%	$112,088.73
f. Years 6-10 = 4	6	100,000.00	4%	$365,907.34	$79,031.45	$365,907.34
	7	100,000.00	4%		$75,991.78
	8	100,000.00	4%		$73,069.02
	9	100,000.00	4%		$70,258.67
	10	100,000.00	4%		$67,556.42

M3,A2

Module 3, Assignment 2 Solutions
Genesis Cash Budget
	Monthly Budget													Quarterly Budget
	Dec	Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	March	June	Sept	Dec
Cash Inflow
Sales (Reference only)	300,000	200,000	350,000	400,000	500,000	550,000	700,000	700,000	650,000	900,000	850,000	750,000	500,000	150,000	190,000	3,000,000	2,400,000
Cash Collections on Sales
10% in month of sale	30,000	20,000	35,000	40,000	50,000	55,000	70,000	70,000	65,000	90,000	85,000	75,000	50,000	15,000	19,000	300,000	240,000
25% in first month after sale	- 0	75,000	50,000	87,500	100,000	125,000	137,500	175,000	175,000	162,500	225,000	212,500	187,500	125,000	37,500	47,500	750,000
35% in second month after sale	- 0	- 0	105,000	70,000	122,500	140,000	175,000	192,500	245,000	245,000	227,500	315,000	297,500	262,500	175,000	52,500	66,500
30% in third month after sale	- 0	- 0	- 0	90,000	60,000	105,000	120,000	150,000	165,000	210,000	210,000	195,000	270,000	255,000	225,000	150,000	45,000
Other Cash Receipts	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000
Total Cash Inflow	45,000	110,000	205,000	302,500	347,500	440,000	517,500	602,500	665,000	722,500	762,500	812,500	820,000	672,500	471,500	565,000	1,116,500
Cash Outflows
Material Purchases (reference only)	150,000	100,000	175,000	200,000	250,000	275,000	350,000	350,000	325,000	450,000	425,000	375,000	250,000	75,000	95,000	1,500,000	1,200,000
Payment for Material Purchase
100% in month after purchase	- 0	150,000	100,000	175,000	200,000	250,000	275,000	350,000	350,000	325,000	450,000	425,000	375,000	250,000	75,000	95,000	1,500,000
Other Cash Payments:
Other production cost 30%
of Material cost paid month
after Purchase		45,000	30,000	52,500	60,000	75,000	82,500	105,000	105,000	97,500	135,000	127,500	112,500	75,000	22,500	28,500	450,000
Selling and Marketing Expense	15,000	10,000	17,500	20,000	25,000	27,500	35,000	35,000	32,500	45,000	42,500	37,500	25,000	7,500	9,500	150,000	120,000
General and Administrative expenses	60,000	40,000	70,000	80,000	100,000	110,000	140,000	140,000	130,000	180,000	170,000	150,000	100,000	30,000	38,000	600,000	480,000
Interest Payment	75,000												75,000				75,000
Tax Payment	.	15,000	- 0	- 0	15,000	- 0	- 0	15,000	- 0	- 0	15,000	- 0	- 0	15,000	15,000	15,000	15,000
Dividend Payment	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
Total Cash Outflows	150,000	260,000	217,500	327,500	400,000	462,500	532,500	645,000	617,500	647,500	812,500	740,000	687,500	377,500	160,000	888,500	2,640,000
Net Cash Gain/(Loss)	(105,000)	(150,000)	(12,500)	(25,000)	(52,500)	(22,500)	(15,000)	(42,500)	47,500	75,000	(50,000)	72,500	132,500	295,000	311,500	(323,500)	(1,523,500)
Cash Flow Summary							15,000
Cash Balance start of the month	15,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
Net Cash Gain/loss	(105,000)	(150,000)	(12,500)	(25,000)	(52,500)	(22,500)	(15,000)	(42,500)	47,500	75,000	(50,000)	72,500	132,500	295,000	311,500	(323,500)	(1,523,500)
Cash Balance at end of month	(90,000)	(125,000)	12,500	- 0	(27,500)	2,500	10,000	(17,500)	72,500	100,000	(25,000)	97,500	157,500	320,000	336,500	(298,500)	(1,498,500)
Minimum Cash Balance desired	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
Surplus cash (deficit)	(115,000)	(150,000)	(12,500)	(25,000)	(52,500)	(22,500)	(15,000)	(42,500)	47,500	75,000	(50,000)	72,500	132,500	295,000	311,500	(323,500)	(1,523,500)
External Financing Summary
External Financing Balance
at start of month	- 0	115,000	265,000	277,500	302,500	355,000	377,500	392,500	435,000	387,500	312,500	362,500	290,000	157,500	- 0	- 0	323,500
New Financing Required
negative amount from cash
surplus (deficit)	(115,000)	(150,000)	(12,500)	(25,000)	(52,500)	(22,500)	(15,000)	(42,500)	47,500	75,000	(50,000)	72,500	132,500	295,000	311,500	(323,500)	(1,523,500)
External Financing Requirement	(115,000)	(265,000)	(277,500)	(302,500)	(355,000)	(377,500)	(392,500)	(435,000)	(387,500)	(312,500)	(362,500)	(290,000)	(157,500)	- 0	- 0	(323,500)	(1,847,000)
External Financing Balance	115,000	265,000	277,500	302,500	355,000	377,500	392,500	435,000	387,500	312,500	362,500	290,000	157,500	- 0	- 0	323,500	1,847,000

M4,A2

Module 4, Assignment 2 Solutions
Question 1: Debt: Jones Industries borrows $600,000 for 10 years with an annual payment of $100,000. What is the expected interest rate (cost of debt)?
CALCULATOR SOLUTION:			Excel Solution:
	$600000 = PV		Use the "rate" function and input the following:
	10 = N		RATE(nper,pmt,pv,fv,type,guess)
	PMT = $100000		11%
	CMPT for Interest Rate
	0.1056
Question 2: Internal common stock: Jones Industries has a beta of 1.39. The risk-free rate as measured by the rate on short-term US Treasury bill is 3 percent, and the expected return on the overall market is 12 percent. Determine the expected rate of return on Jones’s stock (cost of equity). Here are the details:
	Jones Total Assets	$2,000,000
	Long- & short-term debt	$600,000
	Common internal stock equity	$400,000
	New common stock equity	$1,000,000
	Total liabilities & equity	$2,000,000
Solution:
	Use the following formula: ks = kRF + (kM - kRF)b
		Where,
		ks = Expected return on the stock
		kRF = the risk free rate
		Km = Market return on similar stock
		b = beta
	So,
		3% +(12 - 3)1.39 =
		15.51

M5, A2

Module 5, Assignment 2 Solutions
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.
	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%
	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.00%
	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
			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
	SOLUTION
OPTION	NPV of the Cash Flows	Initial Investment	Cash Flow										PV of the cash Flows	NPV	IRR	Payback
			Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9	Y10
a	Project A: 25-emp facility	-2000	($200.00)	-300	-400	200	400	1000	1000	1000	1000	1000	$1,633.14	($366.86)	10.05%	year 8
b	Project B: 40-emp facility	-2500	-200	-200	100	400	400	1500	1500	1500	1500	1500	$3,179.87	$679.87	15.96%	year 7
c	Project C: 75-emp facility	-3000	-300	-400	-100	600	700	2000	2000	2000	2000	2000	$4,075.03	$1,075.03	16.75%	year 7
d	Equipment 1 - fully automatic	-1500	-100	100	200	400	200	800	800	800	800	800	$2,077.72	$577.72	17.95%	6 years
e	Equipment 1 - semi-automatic	-1000	-50	-100	200	200	300	600	600	600	600	600	$1,498.18	$498.18	19.00%	6 years
f	Equipment 1 - manual	-750	150	150	150	150	150	750	750	750	750	750	$2,021.19	$1,271.19	33.35%	5 years
g	Equipment 2 - Standard	-800	-175	200	250	250	300	700	700	700	700	700	$1,888.87	$1,088.87	28.02%	5 years
h	Equipment 2 - top of line	-1500	-100	275	325	325	325	1500	1500	1500	1500	1500	$3,714.02	$2,214.02	28.87%	6 years
i	Equipment 3 - 3-man machine	-700	-200	-150	250	300	350						$261.53	($438.47)	-4.15%	No payback
j	Equipment 3 - 2-man machine	-600	-175	-100	175	175	175						$95.10	($504.90)	-13.28%	No payback
k	Equipment 3 - 5-man machine	-750	-300	-200	300	400	400						$258.56	($491.44)	-3.55%	No payback
CONCLUSION:	All projects are feasible except I, j, and k

Genesis Cash Budget

DecJanFebMarchAprilMayJuneJulyAugSeptOctNovDecMarchJuneSeptDec

Cash Inflow

Sales (Reference only)300,000 200,000 350,000 400,000 500,000 550,000 700,000 700,000 650,000 900,000 850,000 750,000 500,000 150,000190,0003,000,0002,400,000

Cash Collections on Sales

10% in month of sale30,000 20,000 35,000 40,000 50,000 55,000 70,000 70,000 65,000 90,000 85,000 75,000 50,000 15,00019,000 300,000 240,000

25% in first month after sale- 75,000 50,000 87,500 100,000 125,000 137,500 175,000 175,000 162,500 225,000 212,500 187,500 125,00037,500 47,500 750,000

Monthly BudgetQuarterly Budget

M2,A2

MODULE 2, Assignment 2
1. Calculate the future value of $100,000 ten years from now based on the following annual interest rates:							Years	IR	FV	PV
a. 2%							10	2.00%	($121,899.44)	100,000.00
b. 5%
c. 8%
d. 10%							Yr	IR	FV	PV
SOLUTION							1	8%	100,000.00	($92,592.59)	100,000.00	($92,592.59)
	$100,00 for 10 years	Answer					2	8%	150,000.00	($128,600.82)	100,000.00	($85,733.88)
							3	8%	200,000.00	($158,766.45)	100,000.00	($79,383.22)
	a. 2%	$121,899.44					4	8%	200,000.00	($147,005.97)	100,000.00	($73,502.99)
	b. 5%	$162,889.46					5	8%	150,000.00	($102,087.48)	100,000.00	($68,058.32)
	c. 8%	$215,892.50					6	8%	100,000.00	($63,016.96)		($399,271.00)
	d. 10%	$259,374.25					7	8%	100,000.00	($58,349.04)
							8	8%	100,000.00	($54,026.89)
2. Calculate the present value of a stream of cash flows based on a discount rate of 8%. Annual cash flow is as follows:							9	8%	100,000.00	($50,024.90)
a. Year 1 = $100,000							10	8%	100,000.00	($46,319.35)
b. Year 2 = $150,000										($900,790.45)
c. Year 3 = $200,000
d. Year 4 = $200,000
e. Year 5 = $150,000
f. Years 6-10 = $100,000
SOLUTION
a. Year 1 = $100,000			$92,592.59
b. Year 2 = $150,000			$128,600.82
c. Year 3 = $200,000			$158,766.45
d. Year 4 = $200,000			$147,005.97
e. Year 5 = $150,000			$102,087.48
f. Years 6-10 = $100,000			$271,737.14
		Sum =	$900,790.45
3. Calculate the present value of the cash flow stream in problem 2 with the following interest rates:
a. Year 1 = 8%
b. Year 2 = 6%
c. Year 3 = 10%
d. Year 4 = 4%
e. Year 5 = 6%
f. Years 6-10 = 4
SOLUTION
a. Year 1 = 8%	1	100,000.00	8%	$92,592.59
b. Year 2 = 6%	2	150,000.00	6%	$133,499.47
c. Year 3 = 10%	3	200,000.00	10%	$150,262.96
d. Year 4 = 4%	4	200,000.00	4%	$170,960.84
e. Year 5 = 6%	5	150,000.00	6%	$112,088.73
f. Years 6-10 = 4	6	100,000.00	4%	$365,907.34	$79,031.45	$365,907.34
	7	100,000.00	4%		$75,991.78
	8	100,000.00	4%		$73,069.02
	9	100,000.00	4%		$70,258.67
	10	100,000.00	4%		$67,556.42

M3,A2

Module 3, Assignment 2 Solutions
Genesis Cash Budget
	Monthly Budget													Quarterly Budget
	Dec	Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	March	June	Sept	Dec
Cash Inflow
Sales (Reference only)	300,000	200,000	350,000	400,000	500,000	550,000	700,000	700,000	650,000	900,000	850,000	750,000	500,000	150,000	190,000	3,000,000	2,400,000
Cash Collections on Sales
10% in month of sale	30,000	20,000	35,000	40,000	50,000	55,000	70,000	70,000	65,000	90,000	85,000	75,000	50,000	15,000	19,000	300,000	240,000
25% in first month after sale	- 0	75,000	50,000	87,500	100,000	125,000	137,500	175,000	175,000	162,500	225,000	212,500	187,500	125,000	37,500	47,500	750,000
35% in second month after sale	- 0	- 0	105,000	70,000	122,500	140,000	175,000	192,500	245,000	245,000	227,500	315,000	297,500	262,500	175,000	52,500	66,500
30% in third month after sale	- 0	- 0	- 0	90,000	60,000	105,000	120,000	150,000	165,000	210,000	210,000	195,000	270,000	255,000	225,000	150,000	45,000
Other Cash Receipts	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000
Total Cash Inflow	45,000	110,000	205,000	302,500	347,500	440,000	517,500	602,500	665,000	722,500	762,500	812,500	820,000	672,500	471,500	565,000	1,116,500
Cash Outflows
Material Purchases (reference only)	150,000	100,000	175,000	200,000	250,000	275,000	350,000	350,000	325,000	450,000	425,000	375,000	250,000	75,000	95,000	1,500,000	1,200,000
Payment for Material Purchase
100% in month after purchase	- 0	150,000	100,000	175,000	200,000	250,000	275,000	350,000	350,000	325,000	450,000	425,000	375,000	250,000	75,000	95,000	1,500,000
Other Cash Payments:
Other production cost 30%
of Material cost paid month
after Purchase		45,000	30,000	52,500	60,000	75,000	82,500	105,000	105,000	97,500	135,000	127,500	112,500	75,000	22,500	28,500	450,000
Selling and Marketing Expense
General and Administrative expenses
Interest Payment
Tax Payment
Dividend Payment
Total Cash Outflows
Net Cash Gain/(Loss)
Cash Flow Summary							- 0
Cash Balance start of the month	15,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
Net Cash Gain/loss	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	0	- 0	- 0	- 0
Cash Balance at end of month	15,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
Minimum Cash Balance desired	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
Surplus cash (deficit)	(10,000)	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	0	- 0	- 0	- 0
External Financing Summary
External Financing Balance
at start of month	- 0	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	- 0	- 0	- 0
New Financing Required
negative amount from cash
surplus (deficit)	(10,000)	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	0	- 0	- 0	- 0
External Financing Requirement	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	(10,000)	- 0	- 0	- 0	- 0
External Financing Balance	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	- 0	- 0	- 0	- 0

M4,A2

Module 4, Assignment 2 Solutions
Question 1: Debt: Jones Industries borrows $600,000 for 10 years with an annual payment of $100,000. What is the expected interest rate (cost of debt)?
CALCULATOR SOLUTION:			Excel Solution:
	$600000 = PV		Use the "rate" function and input the following:
	10 = N		RATE(nper,pmt,pv,fv,type,guess)
	PMT = $100000		11%
	CMPT for Interest Rate
	0.1056
Question 2: Internal common stock: Jones Industries has a beta of 1.39. The risk-free rate as measured by the rate on short-term US Treasury bill is 3 percent, and the expected return on the overall market is 12 percent. Determine the expected rate of return on Jones’s stock (cost of equity). Here are the details:
	Jones Total Assets	$2,000,000
	Long- & short-term debt	$600,000
	Common internal stock equity	$400,000
	New common stock equity	$1,000,000
	Total liabilities & equity	$2,000,000
Solution:
	Use the following formula: ks = kRF + (kM - kRF)b
		Where,
		ks = Expected return on the stock
		kRF = the risk free rate
		Km = Market return on similar stock
		b = beta
	So,
		3% +(12 - 3)1.39 =
		15.51

M5, A2

Module 5, Assignment 2 Solutions
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.
	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%
	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.00%
	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
			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
	SOLUTION
OPTION	NPV of the Cash Flows	Initial Investment	Cash Flow										PV of the cash Flows	NPV	IRR	Payback
			Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9	Y10
a	Project A: 25-emp facility	-2000	($200.00)	-300	-400	200	400	1000	1000	1000	1000	1000	$1,633.14	($366.86)	10.05%	year 8
b	Project B: 40-emp facility	-2500	-200	-200	100	400	400	1500	1500	1500	1500	1500	$3,179.87	$679.87	15.96%	year 7
c	Project C: 75-emp facility	-3000	-300	-400	-100	600	700	2000	2000	2000	2000	2000	$4,075.03	$1,075.03	16.75%	year 7
d	Equipment 1 - fully automatic	-1500	-100	100	200	400	200	800	800	800	800	800	$2,077.72	$577.72	17.95%	6 years
e	Equipment 1 - semi-automatic	-1000	-50	-100	200	200	300	600	600	600	600	600	$1,498.18	$498.18	19.00%	6 years
f	Equipment 1 - manual	-750	150	150	150	150	150	750	750	750	750	750	$2,021.19	$1,271.19	33.35%	5 years
g	Equipment 2 - Standard	-800	-175	200	250	250	300	700	700	700	700	700	$1,888.87	$1,088.87	28.02%	5 years
h	Equipment 2 - top of line	-1500	-100	275	325	325	325	1500	1500	1500	1500	1500	$3,714.02	$2,214.02	28.87%	6 years
i	Equipment 3 - 3-man machine	-700	-200	-150	250	300	350						$261.53	($438.47)	-4.15%	No payback
j	Equipment 3 - 2-man machine	-600	-175	-100	175	175	175						$95.10	($504.90)	-13.28%	No payback
k	Equipment 3 - 5-man machine	-750	-300	-200	300	400	400						$258.56	($491.44)	-3.55%	No payback
CONCLUSION:	All projects are feasible except I, j, and k

Cash Outflows

Material Purchases (reference only)150,000 100,000 175,000 200,000 250,000 275,000 350,000 350,000 325,000 450,000 425,000 375,000 250,000 75,00095,000 1,500,000 1,200,000

Payment for Material Purchase

100% in month after purchase- 150,000 100,000 175,000 200,000 250,000 275,000 350,000 350,000 325,000 450,000 425,000 375,000 250,00075,000 95,000 1,500,000

Other Cash Payments:

Other production cost 30%

of Material cost paid month

after Purchase45,000 30,000 52,500 60,000 75,000 82,500 105,000 105,000 97,500 135,000 127,500 112,500 75,00022,500 28,500 450,000

Selling and Marketing Expense

General and Administrative expenses

Interest Payment

Tax Payment

Dividend Payment

Total Cash Outflows

Net Cash Gain/(Loss)

M2,A2

MODULE 2, Assignment 2
1. Calculate the future value of $100,000 ten years from now based on the following annual interest rates:							Years	IR	FV	PV
a. 2%							10	2.00%	($121,899.44)	100,000.00
b. 5%
c. 8%
d. 10%							Yr	IR	FV	PV
SOLUTION							1	8%	100,000.00	($92,592.59)	100,000.00	($92,592.59)
	$100,00 for 10 years	Answer					2	8%	150,000.00	($128,600.82)	100,000.00	($85,733.88)
							3	8%	200,000.00	($158,766.45)	100,000.00	($79,383.22)
	a. 2%	$121,899.44					4	8%	200,000.00	($147,005.97)	100,000.00	($73,502.99)
	b. 5%	$162,889.46					5	8%	150,000.00	($102,087.48)	100,000.00	($68,058.32)
	c. 8%	$215,892.50					6	8%	100,000.00	($63,016.96)		($399,271.00)
	d. 10%	$259,374.25					7	8%	100,000.00	($58,349.04)
							8	8%	100,000.00	($54,026.89)
2. Calculate the present value of a stream of cash flows based on a discount rate of 8%. Annual cash flow is as follows:							9	8%	100,000.00	($50,024.90)
a. Year 1 = $100,000							10	8%	100,000.00	($46,319.35)
b. Year 2 = $150,000										($900,790.45)
c. Year 3 = $200,000
d. Year 4 = $200,000
e. Year 5 = $150,000
f. Years 6-10 = $100,000
SOLUTION
a. Year 1 = $100,000			$92,592.59
b. Year 2 = $150,000			$128,600.82
c. Year 3 = $200,000			$158,766.45
d. Year 4 = $200,000			$147,005.97
e. Year 5 = $150,000			$102,087.48
f. Years 6-10 = $100,000			$271,737.14
		Sum =	$900,790.45
3. Calculate the present value of the cash flow stream in problem 2 with the following interest rates:
a. Year 1 = 8%
b. Year 2 = 6%
c. Year 3 = 10%
d. Year 4 = 4%
e. Year 5 = 6%
f. Years 6-10 = 4
SOLUTION
a. Year 1 = 8%	1	100,000.00	8%	$92,592.59
b. Year 2 = 6%	2	150,000.00	6%	$133,499.47
c. Year 3 = 10%	3	200,000.00	10%	$150,262.96
d. Year 4 = 4%	4	200,000.00	4%	$170,960.84
e. Year 5 = 6%	5	150,000.00	6%	$112,088.73
f. Years 6-10 = 4	6	100,000.00	4%	$365,907.34	$79,031.45	$365,907.34
	7	100,000.00	4%		$75,991.78
	8	100,000.00	4%		$73,069.02
	9	100,000.00	4%		$70,258.67
	10	100,000.00	4%		$67,556.42

M3,A2

Module 3, Assignment 2 Solutions
Genesis Cash Budget
	Monthly Budget													Quarterly Budget
	Dec	Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	March	June	Sept	Dec
Cash Inflow
Sales (Reference only)	300,000	200,000	350,000	400,000	500,000	550,000	700,000	700,000	650,000	900,000	850,000	750,000	500,000	150,000	190,000	3,000,000	2,400,000
Cash Collections on Sales
10% in month of sale	30,000	20,000	35,000	40,000	50,000	55,000	70,000	70,000	65,000	90,000	85,000	75,000	50,000	15,000	19,000	300,000	240,000
25% in first month after sale	- 0	75,000	50,000	87,500	100,000	125,000	137,500	175,000	175,000	162,500	225,000	212,500	187,500	125,000	37,500	47,500	750,000
35% in second month after sale	- 0	- 0	105,000	70,000	122,500	140,000	175,000	192,500	245,000	245,000	227,500	315,000	297,500	262,500	175,000	52,500	66,500
30% in third month after sale	- 0	- 0	- 0	90,000	60,000	105,000	120,000	150,000	165,000	210,000	210,000	195,000	270,000	255,000	225,000	150,000	45,000
Other Cash Receipts	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000
Total Cash Inflow	45,000	110,000	205,000	302,500	347,500	440,000	517,500	602,500	665,000	722,500	762,500	812,500	820,000	672,500	471,500	565,000	1,116,500
Cash Outflows
Material Purchases (reference only)	150,000	100,000	175,000	200,000	250,000	275,000	350,000	350,000	325,000	450,000	425,000	375,000	250,000	75,000	95,000	1,500,000	1,200,000
Payment for Material Purchase
100% in month after purchase	- 0	150,000	100,000	175,000	200,000	250,000	275,000	350,000	350,000	325,000	450,000	425,000	375,000	250,000	75,000	95,000	1,500,000
Other Cash Payments:
Other production cost 30%
of Material cost paid month
after Purchase		45,000	30,000	52,500	60,000	75,000	82,500	105,000	105,000	97,500	135,000	127,500	112,500	75,000	22,500	28,500	450,000
Selling and Marketing Expense	15,000	10,000	17,500	20,000	25,000	27,500	35,000	35,000	32,500	45,000	42,500	37,500	25,000	7,500	9,500	150,000	120,000
General and Administrative expenses	60,000	40,000	70,000	80,000	100,000	110,000	140,000	140,000	130,000	180,000	170,000	150,000	100,000	30,000	38,000	600,000	480,000
Interest Payment	75,000												75,000				75,000
Tax Payment	.	15,000	- 0	- 0	15,000	- 0	- 0	15,000	- 0	- 0	15,000	- 0	- 0	15,000	15,000	15,000	15,000
Dividend Payment	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
Total Cash Outflows	150,000	260,000	217,500	327,500	400,000	462,500	532,500	645,000	617,500	647,500	812,500	740,000	687,500	377,500	160,000	888,500	2,640,000
Net Cash Gain/(Loss)	(105,000)	(150,000)	(12,500)	(25,000)	(52,500)	(22,500)	(15,000)	(42,500)	47,500	75,000	(50,000)	72,500	132,500	295,000	311,500	(323,500)	(1,523,500)
Cash Flow Summary							15,000
Cash Balance start of the month	15,000	- 0	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
Net Cash Gain/loss	(105,000)	(150,000)	(12,500)	(25,000)	(52,500)	(22,500)	(15,000)	(42,500)	47,500	75,000	(50,000)	72,500	132,500	295,000	311,500	(323,500)	(1,523,500)
Cash Balance at end of month
Minimum Cash Balance desired
Surplus cash (deficit)
External Financing Summary
External Financing Balance
at start of month	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	0	- 0	- 0	- 0
New Financing Required
negative amount from cash
surplus (deficit)	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	0	- 0	- 0	- 0
External Financing Requirement	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
External Financing Balance	0	0	0	0	0	0	0	0	0	0	0	0	0	- 0	- 0	- 0	- 0

M4,A2

Module 4, Assignment 2 Solutions
Question 1: Debt: Jones Industries borrows $600,000 for 10 years with an annual payment of $100,000. What is the expected interest rate (cost of debt)?
CALCULATOR SOLUTION:			Excel Solution:
	$600000 = PV		Use the "rate" function and input the following:
	10 = N		RATE(nper,pmt,pv,fv,type,guess)
	PMT = $100000		11%
	CMPT for Interest Rate
	0.1056
Question 2: Internal common stock: Jones Industries has a beta of 1.39. The risk-free rate as measured by the rate on short-term US Treasury bill is 3 percent, and the expected return on the overall market is 12 percent. Determine the expected rate of return on Jones’s stock (cost of equity). Here are the details:
	Jones Total Assets	$2,000,000
	Long- & short-term debt	$600,000
	Common internal stock equity	$400,000
	New common stock equity	$1,000,000
	Total liabilities & equity	$2,000,000
Solution:
	Use the following formula: ks = kRF + (kM - kRF)b
		Where,
		ks = Expected return on the stock
		kRF = the risk free rate
		Km = Market return on similar stock
		b = beta
	So,
		3% +(12 - 3)1.39 =
		15.51

M5, A2

Module 5, Assignment 2 Solutions
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.
	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%
	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.00%
	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
			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
	SOLUTION
OPTION	NPV of the Cash Flows	Initial Investment	Cash Flow										PV of the cash Flows	NPV	IRR	Payback
			Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9	Y10
a	Project A: 25-emp facility	-2000	($200.00)	-300	-400	200	400	1000	1000	1000	1000	1000	$1,633.14	($366.86)	10.05%	year 8
b	Project B: 40-emp facility	-2500	-200	-200	100	400	400	1500	1500	1500	1500	1500	$3,179.87	$679.87	15.96%	year 7
c	Project C: 75-emp facility	-3000	-300	-400	-100	600	700	2000	2000	2000	2000	2000	$4,075.03	$1,075.03	16.75%	year 7
d	Equipment 1 - fully automatic	-1500	-100	100	200	400	200	800	800	800	800	800	$2,077.72	$577.72	17.95%	6 years
e	Equipment 1 - semi-automatic	-1000	-50	-100	200	200	300	600	600	600	600	600	$1,498.18	$498.18	19.00%	6 years
f	Equipment 1 - manual	-750	150	150	150	150	150	750	750	750	750	750	$2,021.19	$1,271.19	33.35%	5 years
g	Equipment 2 - Standard	-800	-175	200	250	250	300	700	700	700	700	700	$1,888.87	$1,088.87	28.02%	5 years
h	Equipment 2 - top of line	-1500	-100	275	325	325	325	1500	1500	1500	1500	1500	$3,714.02	$2,214.02	28.87%	6 years
i	Equipment 3 - 3-man machine	-700	-200	-150	250	300	350						$261.53	($438.47)	-4.15%	No payback
j	Equipment 3 - 2-man machine	-600	-175	-100	175	175	175						$95.10	($504.90)	-13.28%	No payback
k	Equipment 3 - 5-man machine	-750	-300	-200	300	400	400						$258.56	($491.44)	-3.55%	No payback
CONCLUSION:	All projects are feasible except I, j, and k

Cash Flow Summary15,000

Cash Balance start of the month15,000 - 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,00025,000 25,000 25,000

Net Cash Gain/loss(105,000) (150,000) (12,500) (25,000) (52,500) (22,500) (15,000) (42,500) 47,500 75,000 (50,000) 72,500 132,500 295,000311,500 (323,500) (1,523,500)

Cash Balance at end of month

Minimum Cash Balance desired

Surplus cash (deficit)

External Financing Summary

External Financing Balance

at start of month- - - - - - - - - - - - - 0- - -

New Financing Required

negative amount from cash

surplus (deficit)- - - - - - - - - - - - - 0- - -

External Financing Requirement- - - - - - - - - - - - - - - - -

External Financing Balance0000000000000- - - -