Question
TVM with Excel Assignment #3 Life is messy and deciding how to allocate capital resources is complicated. So, unlike the highly simplified problems used in
TVM with Excel Assignment #3
Life is messy and deciding how to allocate capital resources is complicated. So, unlike the highly simplified
problems used in class (and in the online examples, homework, etc.), this is a more robust capital budgeting
decision problem.
BACKGROUND INFO:
Acme Manufacturing, Inc. was originally a family owned operation that has been in business for several
generations. It has grown steadily and is now listed on the stock exchange with family members still owning a
substantial portion of the shares. Over the years, the company has acquired a reputation for exceptional quality
and has won awards from major customers.
The firm is 55% equity financed; shares currently trade at $37.00 and do not pay a dividend. Debt capital is
provided by a single issue of bonds (20 year, $1,000 par value, $82.50 annual coupon) currently trading at
$1,175. The firm's beta is 1.25. Their traditional hurdle rate has been 12%, though the rate has not been
reviewed in many years. Over the years, shareholders have come to expect a 10% return. Their corporate tax
rate is 25%. Treasury securities are yielding 5.25%. The market rate of return on equities is 9.25%.
The Machine Tool Division is considering the purchase of a piece of highly-automated, robotic production
equipment. It would replace older machines and would offer improvements in quality, and some additional
capacity for expansion. Because of the magnitude of the proposed expenditure, a careful estimate of the
projects costs and benefits is needed.
They are currently using several old-style machines that together had cost $700,000. Depreciation of $220,000
has already been charged against this total cost; depreciation charges are $80,000 annually. Management
believes these machines will need to be replaced after six more years. They have a current market value of
$250,000.
The old machines require 12 workers per shift earning $13.50/hr plus 3 maintenance workers paid $14.50/hr.
The plant operates day and afternoon shifts five days each week; maintenance workers are assigned to the
afternoon shift only. Maintenance expenses have been running at $5,000 annually; the cost of electricity has
been $26,600 per year. The production process is not only labor intensive, but also physically demanding.
Workplace injuries are not uncommon and lately medical claims have increased.
The new machine will have a total cost that includes shipping, installation and testing of $1.5 million. The plant
will also need $350,000 in modifications to accommodate the new machine. These costs will be capitalized and
depreciated over the six-year estimated life of the machine. The new machine would require only two skilled
operators (one per shift) who would earn $20/hr. Maintenance will be outsourced and cost $90,000 per year.
The annual cost of electricity is estimated to be $50,000.
Certain aspects of the decision are difficult to quantify. Management's relationship with the union hasn't always
been a smooth one and union leadership may not agree to the layoff of the redundant workers. Reassigning
them to positions in other divisions might be easier but there are currently only a handful of suitable openings,
The specs on the new machine indicate that even higher levels of product quality and lower scrap rates are
possible. In light of ever-increasing competition, this might prove to be of enormous competitive advantage. The
new machine has a maximum capacity 27% higher than the old semi-automated machines which are currently
operating at 90% capacity.
Assignment Parts:
a. Calculate the firm's Weighted Average Cost of Capital.
b. Identify and analyze the relevant cash flows for the two alternatives - buying the new machine vs.
continuing to use the old ones.
c. List and describe briefly any areas of uncertainty or concern for this project - beyond the obvious ones
described in the narrative. What effect might they have? Bullet points are just fine.
d. Based on your results in parts b & c, explain why you would or would not proceed with the new
machine.
***PLEASE SHOW WORK USING EXCEL CALCULATIONS THANKS***
Guidelines:
Show all work and briefly label and explain each step. I must be able to follow your work - points off if I have
to struggle with it.
Do not change the assumptions in the problem or invent information not provided; however, be sure to list
any additional assumptions you feel you need to make.
Complete the solution using Excel formulas and functions to make the necessary calculations for parts
a and b - do not just type in numbers. Just as with TVM, Excel has functions for all the project analysis
tools. For parts c and d, please insert text boxes and type in your responses.
Upload your Excel (.xls or .xlsx) solution file online through the assignment link- no hard copies. No
other software/file format is acceptable.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started