Corporate Finance.Week 1 Assignment

FIN5307week1HW.xlsx

Home >Business & Finance homework help >Accounting homework help >Corporate Finance.Week 1 Assignment

Instructions

Principles of Corporate Finance	Main Menu
Tenth Edition
Instructions
Navigating the Workbook
Entering your information
Entering data
Printing
Help
Navigating the Workbook	Top
Each chapter of the spreadsheets to accompany Principles of Corporate Finance
contains links to help you navigate the workbook. These hyperlinks help you
move around the workbook quickly. The Main Menu contains links to each
problem from the chapter that contains the Excel icon. From the Main Menu,
click on the question you wish to complete. You can always return to the main
menu by clicking on the link located in the upper right corner of each worksheet.
You can move quickly around an Excel workbook by selecting the worksheet tab
at the bottom of the screen. Each worksheet in an Excel workbook will have its own
tab. In the spreadsheets to accompany Principles of Corporate Finance, you will
see a separate tab for each problem, along with the Main Menu, Instructions and
Help Topics worksheets.
Another way to move quickly around an Excel workbook is by using the following
keyboard shortcuts:
CTRL+PAGE DOWN: Moves you to the next sheet in the workbook.
CTRL+PAGE UP: Moves you to the previous sheet in the workbook.
Entering your information	Top
For each question, you will see the following lists and boxes:
Student Name:
Course Name:
Student ID:
Course Number:
Enter your information in these cells before submitting your work.
Entering data	Top
To enter numbers or text for these questions, click the cell you want, type the data and
press ENTER or TAB. Press ENTER to move down the column or TAB to move across the row.
For cells or columns where you want to enter text, select “Format,” and then “Cells” from
Excel’s main menu at the top of your screen. Select the “Number” tab and then “Text”
from the category list.
Printing	Top
To print your work, select "File," and then "Print Preview" from Excel’s main menu at the top
of your screen. The print area for each question has been set, but be sure to review
the look of your print job. If you need to make any changes, select “Setup” when
you are previewing the document.
Help	Top
There are two sources of help throughout these spreadsheet templates. First, you will find comments
in specific cells (highlighted in red) providing tips to what formula or function is needed to complete
the problem. Second, you will find links to Microsoft Office's online help page when an Excel Function
is needed to complete the problem.

Question 3

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 3
Question 3
In February 2009 Treasury 6s of 2026 offered a semiannually compounded yield of 3.5965%. Recognizing that coupons are paid semiannually, calculate the bond’s price.
Enter the values in blue colored cells
Chapter 3
Question 3
Student Name:
Course Name:
Student ID:
Course Number:
Use Excel's PRICE function to find the value of the bond under the following assumptions:
Settlement Date
Maturity Date
Coupon Rate
YTM
Price	For help with Excel's PRICE function

http://office.microsoft.com/en-us/excel/HP052092191033.aspx?pid=CH062528251033 http://office.microsoft.com/en-us/excel/HP052092191033.aspx?pid=CH062528251033

Question 4

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 3
Question 4
Here are the prices of three bonds with 10-year maturities:
Bond Coupon (%)	Price (%)
2	81.62
4	98.39
8	133.42
If coupons are paid annually, which bond offered the highest yield to maturity? Which had the lowest? Which bonds had the longest and shortest durations?
Enter the values in blue colored cells
Chapter 3
Question 4
Student Name:
Course Name:
Student ID:
Course Number:
Use Excel's YIELD function to find the YTM of the bond under each of the above assumptions:
Coupon Rate
Price (%)
Settlement Date
Maturity Date
YTM
For help with Excel's YIELD function

http://office.microsoft.com/en-us/excel/HP052093451033.aspx?pid=CH062528251033

Question 15

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 3
Question 15
A 10-year German government bond (bund) has a face value of €100 and a coupon rate of 5% paid annually. Assume that the interest rate (in euros) is equal to 6% per year. What is the bond's PV?
Enter the values in blue colored cells
Chapter 3
Question 15
Student Name:
Course Name:
Student ID:
Course Number:
Maturity in Years
Settlement date
Maturity Date
Face Value
Coupon
Market rate
Annual Payment
Bond's PV	For help with Excel's PRICE function

http://office.microsoft.com/en-us/excel/HP052092191033.aspx?pid=CH062528251033 http://office.microsoft.com/en-us/excel/HP052092191033.aspx?pid=CH062528251033

Question 10

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 5
Question 10
Calculate the IRR (or IRRs) for the following project:
C0	C1	C2	C3
-3000	3500	4000	-4000
For what range of discount rates does the project have positive NPV?
Enter the values in blue colored cells.
Chapter 5
Question 10
Student Name:
Course Name:
Student ID:
Course Number:
C0	C1	C2	C3
-3000	3500	4000	-4000
IRR	Help with Excel's IRR function
Enter a formula for the present value of each cash flow given the interest rate.
Discount Rates
Cash Flows
Year 0	-3,000.00
Year 1	3,500.00
Year 2	4,000.00
Year 3	0.00
NPV =
For what range of discount rates does the project have positive NPV?

http://office.microsoft.com/en-us/excel/HP052091461033.aspx?pid=CH062528251033

Question 13

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 5
Question 13
The Titanic Shipbuilding Company has a noncancelable contract to build a small cargo vessel. Construction involves a cash outlay of $250,000 at the end of each of the next two years. At the end of the third year the company will receive payment of $650,000. The company can speed up construction by working an extra shift. In this case there will be a cash outlay of $550,000 at the end of the first year followed by a cash payment of $650,000 at the end of the second year. Use the IRR rule to show the (approximate) range of opportunity costs of capital at which the company should work the extra shift.
Enter the values in blue colored cells.
Chapter 5
Question 13
Student Name:
Course Name:
Student ID:
Course Number:
Complete the following table to show the incremental cash flows then calculate the IRR
C1	C2	C3
Current arrangement	(250,000)	(250,000)	650,000
Extra shift
Incremental flows
IRR	Help with Excel's IRR function
IRR

http://office.microsoft.com/en-us/excel/HP052091461033.aspx?pid=CH062528251033

Question 11

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 6
Question 11
CSC is evaluating a new project to produce encapsulators. The initial investment in plant
and equipment is $500,000. Sales of encapsulators in year 1 are forecasted at $200,000 and
costs at $100,000. Both are expected to increase by 10% a year in line with inflation. Profits
are taxed at 35%. Working capital in each year consists of inventories of raw materials and
is forecasted at 20% of sales in the following year.
The project will last five years and the equipment at the end of this period will have no
further value. For tax purposes the equipment can be depreciated straight-line over these
five years. If the nominal discount rate is 15%, show that the net present value of the project
is the same whether calculated using real cash flows or nominal flows.
Enter the values in blue colored cells
Chapter 6
Question 11
Student Name:
Course Name:
Student ID:
Course Number:
Figures in $
Initial Investment
Sales in Year 1
Costs in Year 1
Inflation
Working Capital (% of sales-following yr)
Life of the Project
Taxes
Nominal discount rate
Real Discount rate
Salvage Value of Plant & Equipment
Years
0	1	2	3	4	5
Capital Investment
Sales
Costs
Operating Income
Depreciation
Net Profit before Taxes
Taxes
Net Profit after Taxes
Depreciation added back
Cash flows
Working Capital
Net Cash Flows
Discounted value of Nominal Cash Flows
Cash Flows adjusted for Inflation
Discounted value of Real Cash Flows
Present Value (Nominal Cash Flows)
Present Value (Real Cash Flows)
Cost of Plant & Equipment
NPV (Nominal Cash Flows)
NPV (Real Cash Flows)
Difference between Nominal and Real Cash flows

Question 15 (2)

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 6
Question 15
After spending $3 million on research, Better Mousetraps has developed a new trap. The
project requires an initial investment in plant and equipment of $6 million. This investment
will be depreciated straight-line over five years to a value of zero, but, when the project
comes to an end in five years, the equipment can in fact be sold for $500,000. The firm
believes that working capital at each date must be maintained at 10% of next year’s forecasted
sales. Production costs are estimated at $1.50 per trap and the traps will be sold for
$4 each. (There are no marketing expenses.) Sales forecasts are given in the following table.
The firm pays tax at 35% and the required return on the project is 12%. What is the NPV?
Enter the values in blue colored cells
Chapter 6
Question 15
Student Name:
Course Name:
Student ID:
Course Number:
Year	0	1	2	3	4	5
Sales (millions of traps)	0	0.50	0.60	1.00	1.00	0.60
Initial Investment
Life of Project in Years
Salvage value of Plant and Equipment
Working Capital as % of forcast sales
Production costs
Sale Price
Taxes
Cost of Capital
Years
0	1	2	3	4	5
(Figures in 000's)
Capital Investment
Accumulated Depreciation
Year-End Book Value
Working capital
Total Book Value
Unit Sales
Revenues
Costs
Depreciation
Pretax Profit (includes salvage in yr 5)
Taxes at 35%
Profit after tax
Revenues
Costs
Tax on operations
Cash Flow from Operations
Change in working capital
Capital Investment
Net Cash Flows
Discount Factor @ 12%
Present Value
NPV

Question 21

Principles of Corporate Finance	Main Menu
Tenth Edition
Chapter 6
Question 21
United Pigpen is considering a proposal to manufacture high-protein hog feed. The project would make
use of an existing warehouse, which is currently rented out to a neighboring firm. The next year's rental charge
on the warehouse is $100,000, and thereafter the rent is expected to grow in line with inflation at 4 percent a
year. In addition to using the warehouse, the proposal envisages an investment in plant and equipment of
$1.2 million. This could be depreciated for tax purposes straight-line over 10 years. However, Pigpen expects
to terminate the project at the end of eight years and to resell the plant and equipment in year 8 for $400,000.
Finally, the project requires an initial investment in working capital of $350,000. Thereafter, working capital is
forecasted to be 10 percent of sales in each of years 1 through 7.
Year 1 sales of hog feed are expected to be $4.2 million, and thereafter sales are forecast to grow by
5 percent a year, slightly faster than the inflation rate. Manufacturing costs are expected to be 90 percent of
sales, and projects are subject to tax at 35 percent. The cost of capital is 12 percent
What is the NPV of Pigpen’s project?
Enter the values in blue colored cells
Chapter 6
Question 21
Student Name:	Heybis Perez
Course Name:	Corporate Finace
Student ID:
Course Number:	ACG 5080 & FIN 5065
Select the green colored cells below for tips and suggestions to complete this problem.
Assumptions
Initial investment ($000)	Enter all figures in thousands of dollars (e.g., $100,000 is entered as 100).
Resale Value year 8
Working Capital Investment
Projected working capital
(% of Sales)
Yearly rental income
Rental Income growth rate
First year sales
Sales growth rate
Manufacturing costs
(% of Sales)
Tax rate
Cost of capital
Year
0	1	2	3	4	5	6	7	8
Sales
Manufacturing Costs
Depreciation
Earnings before tax
Taxes
Net Income
Working Capital
Increase in W.C.
Lost After Tax Rental Income
Initial Investment
Sale of Plant
Tax on Sale of Plant
Net Cash Flows
Present value
Net present value =	Help with Excel's SUM function

http://office.microsoft.com/en-us/excel/HP052092901033.aspx?pid=CH062528291033