SMG FE 101 Lecture Notes - Lecture 3: Microsoft Office 2013, 0 (Year), Cash Flow

75 views2 pages

chocolatewater-buffalo865

2 Jun 2016

School

Department

Course

Professor

For unlimited access to Class Notes, a Class+ subscription is required.

Office 2013 – myitlab:grader – Instructions Excel Project

Updated: 04/14/2016 1 Current_Instruction.docx

Berk_DeMarzo. Problem 3-9 (Excel)

Project Description:

In this problem, you will: a) calculate the net present value of an investment opportunity; and b) devise a way to

turn the net present value of the investment opportunity into cash today.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points

Possible

Start Excel. Download and open the workbook named:

Berk_DeMarzo_Problem_3-9_Start.

0.000

In cell D16, calculate the net cash flow in year 0 by adding the investment for year 0, cell

D13, and the government payment in year 0, cell D14.

1.000

In cell E16, calculate the net cash flow in year 1 by copying and pasting cell D16 onto cell

E16.

1.000

To calculate the present value of a future value you need to use the present value formula:

PV = FV/(1 + r)^n. In cell D17, calculate the value today of the net cash flow in year 0 by

inputting the present value formula with the following substitutions: the net cash flow in year

0, cell D17, as the future value, FV; the interest rate, cell D8, with absolute cell reference to

the interest rate, $D$8, for copying and pasting purposes as the discount rate, r; and the

year, cell D12, as the number of discount periods, n.

1.000

In cell E17, calculate the value today of the net cash flow in year 1 by copying and pasting

cell D17 onto cell E17.

1.000

In cell D18, calculate the net present value by adding the value today of the net cash flow in

year 0, cell D17, and the value today of the net cash flow in year 1, cell E17.

1.000

In cell D22, calculate the amount to borrow today by dividing the amount to be received from

the government in one year, cell E14, by 1 plus the interest rate, cell D8, i.e., (1 + D8).

1.000

In cell D23, calculate the amount to be used today by making a cell reference to the

investment in year 0, cell D13, preceded by a negative sign (-) to denote a cash outflow.

1.000

In cell D24, calculate the amount that will be left after making the investment in year 0 by

adding the amount to borrow, cell D22, and the amount to be used in year 0, cell D23.

1.000

In cell D25, calculate the amount to deposit in the bank today to pay for costs in one year by

dividing the investment in year 1, cell E13, by 1 plus the interest rate, cell D8, i.e., (1 + D8).

(This will be a negative value since it is a cash outflow.)

1.000

find more resources at oneclass.com

Unlock document

This preview shows half of the first page of the document.
Unlock all 2 pages and 3 million more documents.

Already have an account? Log in

Document Summary

In this problem, you will: a) calculate the net present value of an investment opportunity; and b) devise a way to turn the net present value of the investment opportunity into cash today. For the purpose of grading the project you are required to perform the following tasks: In cell d16, calculate the net cash flow in year 0 by adding the investment for year 0, cell. D13, and the government payment in year 0, cell d14. In cell e16, calculate the net cash flow in year 1 by copying and pasting cell d16 onto cell. To calculate the present value of a future value you need to use the present value formula: In cell d17, calculate the value today of the net cash flow in year 0 by inputting the present value formula with the following substitutions: the net cash flow in year.

Related Questions

SHOW WORK on EXCEL and EXPLAIN ANSWERS on all problems.

You are thinking of buying a miniature golf course to operate. It is expected to generate cash flows of $45,000 per year in years one through three and $55,000 per year in years four through eight. If the appropriate discount rate is 12%, what is the most you would pay for this golf course?

Your team is evaluating two mutually exclusive projects. The initial cost of each investment is $50,000. The probability of the cash flows is shown below. If the project will have a 5 year life and the appropriate cost of capital is 9% calculate the following:

Probability	CF(A)	CF(B)
10%	(34,000)	(13,500)
25%	(8,500)	2,125
30%	17,000	19,000
25%	42,500	31,875
10%	68,000	46,750

Expected value

NPV

Standard deviation

IRR

MIRR

Use the information below for the next problem

Depreciation	34,000
EBIT	179,000
Investment in Operating Assets	69,000
Tax Rate	34%
Find the free cash flow

3. Calculate the free cash flow

Use the following information for the next problem

The Security Market Line
	Security X	Market
Beta	0.76	1
Expected Return	?	12%
If the risk free rate is	2.80%
Find the expected return on security X

4. What is the expected return for Security X?

Use the following information for the next three problems,


Year	Cash Flow
1	$12,500
2	$14,000
3	$10,000
4	$11,000
5	$16,000

5. What is the NPV of above project if the initial investment was $35,000?

6. Calculate the IRR assuming a cost of capital of 11%.

7. Calculate the MIRR of the project assuming a cost of capital of 11%. ___________________________________________________________________________

8. Suppose that you are approached with an offer to purchase an investment that will provide cash flows of $1,600 per year for 18 years. The cost of purchasing this investment is $9,200. You have an alternative investment opportunity, of equal risk, that will yield 9% per year. What is the NPV that makes you indifferent between the two options?

___________________________________________________________________

9. The Claustrophobic Solution, Inc., a residential window and door manufacturer, has the following historical record of earnings per share (EPS) from 2015 to 2007:

	2015	2014	2013	2012	2011	2010	2009	2008	2007
EPS	$1.28	$1.22	$1.18	$1.13	$1.10	$1.05	$1.00	$0.95	$0.90

The companyâs payout ratio has been 57% over the last nine years and the last quoted price of the firmâs share of stock was $15. Flotation costs for new equity will be 7%. The company has 34,000,000 of common shares of stock outstanding and a debt-equity ratio of 0.45.

If dividends are expected to grow at the same arithmetic average growth rate of the last nine years, what is the dividend payment per share in 2016?

_________________________________________________________________________-

Use the following data for the next 3 questions

The following are the company sales from 2000-2015

Year	Xylophone
2000	$230
2001	$573
2002	$994
2003	$1,683
2004	$3,192
2005	$6,140
2006	$8,892
2007	$13,586
2008	$18,376
2009	$29,476
2010	$33,598
2011	$44,208
2012	$58,473
2013	$96,368
2014	$149,306
2015	$209,397

Fit an exponential trend curve to the data- show the equation

Calculate the projected sales in 2016

What is the CAGR over the 2000-2015 period?

____________________________________________________________-

Use the following data for the next 3 problems

Roxieâs Surf Shop is expanding their product line, adding a high end surf board to their existing basic product.

Their fixed costs for the equipment needed for the new boards is $5700 per month.

The new board will cost $278 per board and they can be sold for $450.

How many new boards per month will they need to sell to breakeven quantity per month?

If the fixed costs are reduced to $4800 per month what is the new breakeven quantity?

If the fixed costs stay at $5300 and they want to have at least $1000 per month in profit how many boards should they sell?

Use the information below for the next 4 answers

Debt 5,000 bonds par $1,000 with a maturity 20 years; semi annual compounding. Coupon rate 8%. Price $1,310.

Preferred 50,000 shares of 3% par value $100 stock. Current price $63.00.

Common stock 72,000 shares currently selling for $87.00. The beta of the firm is 1.17, the risk free rate is 2.78%, Market return (Rm) =8.6%.

Cost of debt

Cost of preferred

Cost of equity

WACC

Use the following data for the remaining problems.

Capstone Quarry is analyzing whether a new contract proposal will be a good idea. The relevant data is shown below. The net working capital will be paid in the same time period as the cost of the equipment and will be recovered at the end of the project. Remember to calculate the after-tax gain or loss of salvage as part of your terminal cash flow.

Capstone Quarry Company Contract Analysis
Amount of Rock Salt per Year	23,000 Tons
Revenue per Ton	$ 145
Cost of Equipment	$ 2,750,000
Life(years)	5
MACRS Class	5
Fixed Cost per year	$ 475,000
Var Cost/Ton	$ 85
Actual Salvage	$ 105,000
Change in NWC	$ 85,000
Required Return	12%
Tax Rate	34%

Find the cash flows for each year

Net present value

Payback period

Discounted payback

IRR

MIRR

Hint:

Annual Cash Flows for Capstone Quarry

	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Initial Outlay
Unit Sales
Sales
Variable Costs
Fixed Costs
Depreciation
Taxable Cash Flows
Taxes
Add: Depreciation
Annual After-Tax Cash Flow
Terminal Cash Flow
Total Annual Cash Flows

periwinkleduck434

Capital Budgeting Mini Case

Instructions: The assignment is based on the mini case below. The instructions relating to the assignment are at the end of the case.

Lucy Hawkins and Andy Chen are facing an important decision. After having discussed different financial scenarios into the wee hours of the morning, the two computer engineers felt it was time to finalize their cash flow projections and move to the next stage â decide which of two possible projects they should undertake.

Both had a bachelor degree in engineering and had put in several years as maintenance engineers in a large chip manufacturing company. About six months ago, they were able to exercise their first stock options. That was when they decided to quit their safe, steady job and pursue their dreams of starting a venture of their own. In their spare time, almost as a hobby, they had been collaborating on some research into a new chip that could speed up certain specialized tasks by as much as 25%. At this point, the design of the chip was complete. While further experimentation might improve the performance of their design, any delay in entering the market now may prove to be costly, as one of the established players might introduce a similar product of their own. The duo knew that now was the time to act if at all.

They estimated that they would need to spend about $5,000,000 on plant, equipment and supplies. As for future cash flows, they felt that the right strategy at least for the first year would be to sell their product at dirt-cheap prices in order to induce customer acceptance. Then, once the product had established a name for itself, the price could be raised. By the end of the fifth year, their product in its current form was likely to be obsolete. However, the innovative approach that they had devised and patented could be sold to a larger chip manufacturer for a decent sum. Accordingly, the two budding entrepreneurs estimated the operating cash flows for this project (call it Project A) as follows:

Year	Project A Expected Cash flows ($)
0	(5,000,000)
1	200,000
2	875,000
3	2,130,000
4	3,110,000
5	3,110,000

An alternative to pursuing this project would be to sell the patent for their innovative chip design to one of the established chip makers. They estimated that they would receive around $800,000 for this. It would probably not be reasonable to expect much more as neither their product nor their innovative approach had a track record.

They could then invest in some plant and equipment that would test silicon wafers for zircon content before the wafers were used to make chips. Too much zircon would affect the long-term performance of the chips. The task of checking the level of zircon was currently being performed by chip makers themselves. However, many of them, especially the smaller ones, did not have the capacity to permit 100% checking. Most tested only a sample of the wafers they received.

Lucy and Andy were confident that they could persuade at least some of the chip makers to outsource this function to them. By exclusively specializing in this task, their little company would be able to slash costs by more than half, and thus allow the chip manufacturers to go in for 100% quality check for roughly the same cost as what they were incurring for a partial quality check today. The life of this project too is expected to be only about five years.

The initial investment for this project is estimated at $ 5,000,000. After taking into account the sale of their patent, the net investment would be $4,200,000. As for the future, Lucy and Andy were pretty sure that there would be sizable profits in the first year. But thereafter, the zircon content problem would slowly start to disappear with advancing technology in the wafer industry. Keeping this in mind, they estimate the future cash inflows for this project (call it Project B) as follows:

Year	Project B Expected Cash flows ($)
0	(4,200,000)
1	2,500,000
2	2,000,000
3	900,000
4	550,000
5	250,000

Lucy and Andy now need to make their decision. For purposes of analysis, they plan to use a required rate of return of 15% for both projects. Ideally, they would prefer that the project they choose have a payback period of less than 3.5 years and a discounted payback period of less than 4 years.

Below are the results of the analysis they have carried out so far:

Metrics	Project A	Project B
Payback period (in years)	3.58	1.85
Discounted payback period (in years)	4.64	2.87
Net Present Value (NPV)	$560,421	$516,723
Internal Rate of Return (IRR)	18.37%	22.47%
Profitability Index	1.11	1.12
Modified Internal Rate of Return (MIRR)	17.47%	17.70%

One of the concerns that Lucy and Andy have is regarding the reliability of their cash flow estimates. All the analysis in the table above is based on âexpectedâ cash flows. However, they are both aware that actual future cash flows may be higher or lower.

Assignment:

Suppose that Lucy and Andy have hired you as a consultant to help them make the decision. Please draft an official memo to them with your analysis and recommendations.

Your submission should cover the following questions:

Briefly, summarize the key facts of the case and identify the problem being faced by our two budding entrepreneurs. In other words, what is the decision that they need to make? (10 points)

An excellent paper will demonstrate the ability to construct a clear and insightful problem statement while identifying all underlying issues.

What are some approaches that can be used to solve this problem? What are some various criteria or metrics that can be used to help make this decision? (10 points)

An excellent paper will propose solutions that are sensitive to all the identified issues.

a) Rank the projects based on each of the following metrics: Payback period, Discounted payback period, NPV, IRR, Profitability Index, and MIRR. (10 points)

b) Andy believes that the best approach to make the decision is the NPV approach. However, Lucy is not so sure that ignoring the other metrics is a good idea. Which of the approaches or metrics would you propose? In other words, would you prefer one or more of these approaches over the others? Explain why. (20 points)

An excellent paper includes an evaluation of solutions containing thorough and insightful explanations, feasibility of solutions, and impacts of solutions.

a) Which of these projects would you recommend? Explain why. (10 points)

b) Briefly state the limitations of the approach you used in making this decision, and outline what further analysis you would recommend. (20 points)

An excellent paper provides concise yet thorough action-oriented recommendations using appropriate subject-matter justifications related to the problem while addressing limitations of the solution and outlining recommended future analysis.

tealimpala814

Instructions: The assignment is based on the mini case below. The instructions relating to the assignment are at the end of the case.

Year	Project A Expected Cash flows ($)
0	(5,000,000)
1	200,000
2	875,000
3	2,130,000
4	3,110,000
5	3,110,000

Year	Project B Expected Cash flows ($)
0	(4,200,000)
1	2,500,000
2	2,000,000
3	900,000
4	550,000
5	250,000

Below are the results of the analysis they have carried out so far:

Metrics	Project A	Project B
Payback period (in years)	3.58	1.85
Discounted payback period (in years)	4.64	2.87
Net Present Value (NPV)	$560,421	$516,723
Internal Rate of Return (IRR)	18.37%	22.47%
Profitability Index	1.11	1.12
Modified Internal Rate of Return (MIRR)	17.47%	17.70%

Assignment:

Suppose that Lucy and Andy have hired you as a consultant to help them make the decision. Please draft an official memo to them with your analysis and recommendations.

Your submission should cover the following questions:

1.Briefly, summarize the key facts of the case and identify the problem being faced by our two budding entrepreneurs. In other words, what is the decision that they need to make? (10 points)

An excellent paper will demonstrate the ability to construct a clear and insightful problem statement while identifying all underlying issues.

2.What are some approaches that can be used to solve this problem? What are some various criteria or metrics that can be used to help make this decision? (10 points)

An excellent paper will propose solutions that are sensitive to all the identified issues.

3.a) Rank the projects based on each of the following metrics: Payback period, Discounted payback period, NPV, IRR, Profitability Index, and MIRR. (10 points)

An excellent paper includes an evaluation of solutions containing thorough and insightful explanations, feasibility of solutions, and impacts of solutions.

4.a) Which of these projects would you recommend? Explain why. (10 points)

b) Briefly state the limitations of the approach you used in making this decision, and outline what further analysis you would recommend. (20 points)

SMG FE 101 Lecture Notes - Lecture 3: Microsoft Office 2013, 0 (Year), Cash Flow

Document Summary

Get access

Related Documents

BUS 237 Study Guide - Final Guide: Sparkline, Microsoft Office 2010, North American Numbering Plan

ISDS 1100 Chapter : ISDS Instructions Excelproject1

SOC 1500 Lecture Notes - Lecture 3: Unique Key, Microsoft Excel, Microsoft Access

Related Questions