Get documents about "Exam 3 Sp10 Solved
Shared by: changcheng2
-
Stats
- views:
- 2
- posted:
- 1/14/2012
- language:
- pages:
- 13
Document Sample
IMPORTANT: SAVE THIS SPREADSHEET TO THE DESKTOP OF THE
COMPUTER YOU ARE USING WITH YOUR NAME IN THE FILENAME.
RESAVE IT OFTEN WHILE YOU ARE WORKING ON IT.
NOTHING SHOULD BE USED OR ACCESSED BY YOU DURING THIS
TEST EXCEPT THE COMPUTER YOU ARE USING AND THIS FILE, AND
BLACKBOARD WHEN YOU SUBMIT YOUR COMPLETED EXAM.
YOU MAY NOT ACCESS THE INTERNET OTHER THAN TO SUBMIT
YOUR COMPLETED EXAM IN BLACKBOARD.
VIDEO SURVEILLANCE IS ACTIVE.
There are 6 tabbed pages in this exam spreadsheet including this page.
There are 5 problem pages with points possible noted on the tabs.
Follow the instructions on each tabbed page.
Partial credit will be given where possible.
Total points for the problems sum to 80.
Submit your finished file in the digital dropbox in BlackBoard.
NOTE WELL: YOUR TEST NEEDS TO BE SUBMITTED, NOT SAVED.
IF YOU JUST SAVE IT, I DON'T RECEIVE IT AND YOU GET A ZERO.
IN BLACKBOARD, YOU SEND IT BY CLICKING ON SEND, NOT ON ADD. DO NOT USE
BUTTON. USE THE SEND BUTTON. BE SURE TO CHECK WITH ME BEFORE YOU LEAV
TO MAKE SURE I RECEIVED YOUR TEST.
N ADD. DO NOT USE THE ADD
E BEFORE YOU LEAVE
The Supreme Shoe Company is considering the purchase of a new, fully-automated machine to replace a manually operated one. The machine
being replaced, now 1 year old, originally had an expected life of 4 years, is being depreciated as a 5-year MACRS asset from its purchase price of
$32,500. It can be sold now for $7,500. If the old machine is used for 3 more years instead of being replaced now, it is expected to have a $1,500
before-tax residual value at that time.
The annual costs of maintenance and defects on the old machine are $4,000 and $2,000 respectively. The replacement machine being considered
has a purchase price of $35,000 and an expected salvage value of $20,000 at the end of its 3-year life. There will also be shipping and installation
expenses of $3,000. Because the new machine would be more efficient, the constant investment in raw materials would decrease by a total of MACRS Percentages
$5,000 from the current level with the old machine. The company expects that annual maintenance costs on the new machine will be $7,500 Year 3-year 5-year 7-year 10-year
while defects will cost $1,500. The new machine will also result in additional productive capacity so sales will increase by $9,000 per year due to
1 33.33% 20.00% 14.29% 10.00%
increased output. The new machine will be depreciated as a 5-year MACRS-class asset.
2 44.45% 32.00% 24.49% 18.00%
Before considering this project, the company undertook an engineering analysis of current facilities to determine if other changes would be 3 14.81% 19.20% 17.49% 14.40%
necessitated by the purchase of the machine. The study cost the company $12,500 and determined that existing facilities could support this new 4 7.41% 11.52% 12.49% 11.52%
machine with no other changes. In order to purchase the new machine, the company would have to take on new debt of $30,000 at 10% interest, 5 11.52% 8.93% 9.22%
resulting in increased interest expense of 12,000 per year. The required rate of return for this project is 9% and the company's marginal tax rate is 6 5.76% 8.92% 7.37%
34%. 7 8.93% 6.55%
8 4.46% 6.55%
Use the space below to compute the initial outlay, the annual cash flows, and the terminal cash flows for this project. The only inputs that need 9 6.56%
to be linked to your computations are the ones shown. All other numbers can be entered straight into your formulas but all calculations need 10 6.55%
to done in the formulas. The MACRS depreciation percentages are in the table to the right. You do not need to do any lookup functions, and the 11 3.28%
model only needs to work for this time period and for this depreciation type. Show all computations. Label all entries appropriately. THE
NUMBER RESULTS ARE WHAT IS IMPORTANT, NOT THE FORMATTING. YOU CAN SET THIS UP ANY WAY THAT YOU WANT AS LONG AS THE 4
TOTAL CASH FLOWS GET COMPUTED CORRECTLY AND THEY ADJUST CORRECTLY TO THE INPUTS SHOWN.
INPUTS
Old Machine: New Machine: General:
Original Price 32,500 Original Price 35,000 Tax Rate 34%
Current Value 7,500 Installation Expenses 3,000 Req'd Return 9%
Expected value in 3 years 1,500 Expected value in 3 years 20,000
Annual Cash Expenses 6,000 Annual Cash Expenses 9,000
Increased Sales 9,000
CASH FLOWS
CASH FLOWS AT t=0 CASH FLOWS FOR YEARS 1 to 3
Year 1 Year 2 Year 3
Purchase Price (35,000.00) New Depreciation 7,000.00 11,200.00 6,720.00
Installation (3,000.00) Depreciation Old 10,400.00 6,240.00 3,744.00
Chg in Net Working Capital 5,000.00 Difference (3,400.00) 4,960.00 2,976.00 $12,063.44
BTSV Old Asset 7,500.00
BV Old Asset 26,000.00 Revenue 9,000.00 9,000.00 9,000.00
Gain or Loss on Sale (18,500.00) Operating Expenses (3,000.00) (3,000.00) (3,000.00)
Tax Effect on Sale of Old Asset 6290 Depreciation 3,400.00 (4,960.00) (2,976.00)
Net Initial Outlay (19,210.00) Chg in Taxable Inc 9,400.00 1,040.00 3,024.00
Tax (3,196.00) (353.60) (1,028.16)
Chg in Net Income 6,204.00 686.40 1,995.84
Reverse Depn (3,400.00) 4,960.00 2,976.00
Net Operating CF 2,804.00 5,646.40 4,971.84
Reverse NWC (5,000.00)
Chg in ATSV 13,727.76
Net Cash Flow 13,699.60
Old New Diff
BTSV 1,500.00 20,000.00
Book Value 5,616.00 10,080.00
Gain(Loss) on Sale (4,116.00) 9,920.00
Tax Effect 1,399.44 (3,372.80)
ATSV 2,899.44 16,627.20 13,727.76
Problem 2:
The MACRS depreciation rates are given in the table MACRS Percentag
to the right. Using the inputs provided below, create Year 3-year
whatever formulas are needed to compute the book value 1 33.33%
of the asset at the end of the year shown in the input. 2 44.45%
Your method must work for any allowed values of the inputs. 3 14.81%
4 7.41%
Depreciation Class 7-Year 5
Purchase Price $50,000 6
Years owned (Max of 10) 2 7
8
Book Value of the asset $30,610 9
10
11
1 7145
2 12245
3
4
5
6
7
8
9
10
Total 19390
MACRS Percentages
5-year 7-year 10-year
20.00% 14.29% 10.00% 3-Year 2
32.00% 24.49% 18.00% 5-Year 3
19.20% 17.49% 14.40% 7-Year 4
11.52% 12.49% 11.52% 10-Year 5
11.52% 8.93% 9.22%
5.76% 8.92% 7.37% 4
8.93% 6.55%
4.46% 6.55%
6.56%
6.55%
3.28%
Problem 3:
Using the inputs in the table below, create whatever formulas are needed to compute the DISCOUNTED PAYBACK
set of inputs within the 10-year time frame. Put the result in the yellow cell below the table. The result should sh
cell where X is the discounted payback period in years. If the inputs are set to produce a project that does not ach
payback within the 10 years, your result should return the word NEVER.
Cash
Time Flow
0 $ (5,000) $ (5,000)
1 $ 1,000 $892.86 $ (3,804)
2 $ 1,500 $1,195.79 $ (1,669)
3 $ 3,000 $2,135.34 $ 555
4 $ 3,500 $2,224.31 $ 2,258
5 $ 3,000 $1,702.28 $ 3,615
7 $ 3,000 $1,357.05 $ 3,615
8 $ - $0.00 $ 3,615
9 $ - $0.00 $ 3,615
10 $ - $0.00 $ 3,615
Discount Rate 12%
Payback Period 3 Years
compute the DISCOUNTED PAYBACK PERIOD years for any
elow the table. The result should show "X Years" in the
produce a project that does not achieve discounted
3
4
5
7
8
9
10
Problem 4:
Cash Disount
Time Flow Rate
0 $ (20,000) 9.00%
1 $ 2,500
2 $ 4,000
3 $ 5,000
4 $ 6,000
5 $ 3,800
6 $ 3,500
A. Using the inputs above, create one formula that computes the Net Present Value of the project. [5 Points]
Answer: $ (1,671.56)
B. Using the inputs above, create one formula that computes the Modified Internal Rate of Return for the project. [5 Poin
Answer: 7.43%
C. Using the inputs and outputs above, create one formula that gives the equivalent annual annuity for the project. [5 Poi
Answer: ($372.62)
D. Using the inputs and outputs above and the setup space below, create a data table
that automatically calculates the NPV for the project (in Column D) for the discount rates
given in Column C in the table. Then use the data table to create an NPV profile graph.
Place your graph below the data table and format it appropriately with
titles, axis lables, and other appropriate formating elements as appropriate.
Data Table
$ (1,672) Column C contains the rates that need be substituted
0% $ 4,800 for the discount rate.
2% $ 3,100
4% $ 1,565 Column D should contain the resulting NPV's for the
6% $ 176 various disount rates.
8% $ (1,085)
10% $ (2,232)
12% $ (3,278)
14% $ (4,234) NPV Profile
18% $ (5,913) $6,000
20% $ (6,653)
22% $ (7,334) $4,000
24% $ (7,963)
$2,000
$2,000
26% $ (8,545)
28% $ (9,084) $-
30% $ (9,585) 0% 5% 10% 15%
$(2,000)
NPV
$(4,000)
$(6,000)
$(8,000)
$(10,000)
$(12,000)
Discount Rate
t. [5 Points]
n for the project. [5 Points]
ty for the project. [5 Points]
V Profile
20% 25% 30% 35%
Discount Rate
In the yellow cell below, create a formula that creates
Problem 5: a sentence that looks like the example below it. The
numbers for NPV and the discount rate must change
Year CF to be correct for any cash flows that are entered in
0 $ (6,500.00) cells C5:C10 and the last word in the sentence must
1 $ 1,000.00 change to be either ACCEPTED or REJECTED
2 $ 1,500.00 depending on whether the NPV is positive or negative.
3 $ 1,200.00 You can create any intermediate formulas that you
4 $ 3,000.00 need to compute the results that are used in the
5 $ 3,500.00 sentence.
Discount Rate: 9.65%
The project has an NPV of $853.29 using a discount rate of 9.65% and it should be ACCEPTED.
The project has an NPV of $6,000.00 using a discount rate of 10.00% and it should be ACCEPTED.
NPV $ 853.29
ACCEPR OR REJECT ACCEPTED
create a formula that creates
e the example below it. The
e discount rate must change
h flows that are entered in
word in the sentence must
EPTED or REJECTED
he NPV is positive or negative.
mediate formulas that you
sults that are used in the
be ACCEPTED.
uld be ACCEPTED.
