The streets and roads maintained by the government have not had an adequate maintenance program. The continuing failure to adequately and promptly repair tailures in the asphalt paving of local, collector, and arterial roadways has caused the government to incur excessive repaving/reconstruction costs for its roadways due to the early failure of their asphalt surfacing and subsequent deterioration of the roadway's base. The Public Works Department's Civil Engineers have recommended the establishment of a dedicated team for the rapid and efficient accomplishment of the repair of potholes and other paving Presently, the annual budget for maintaining and refurbishing failing roadways is $750,000. This amount has historically (over the most recent 10 years) increased by an average 8% annually. The Public Works Engineers estimate that the proposed maintenance program will reduce that budgetary item's cost annually by the following proportions ProgramCost Year Reduction ures 2 10% 10% 10% The accomplishment of this recommendation is expected to require the services of a full-time, dedicated crew of four (4) field personnel: one (1) Crew Foreman and three (3) Maintenance Workers. The efforts of this group will be the constant execution of a job-order report from the Public Works Department's current Road Inventory computer software's Repair Report List. They will accomplish their scheduled work using an automated asphalt patching system for the repair of potholes in major roadways that will be purchased for this purpose. Purchase price estimated a $175,000. The purchase price includes training of three (3) individuals in the operation and maintenance of the equipment. The cost of the crew's year 1 annual salaries and benefits are expected to conform to the following schedule 4 6% The Public Works Engineers also estimate program operating costs for materials, equipment m aintenance, repairs, and fuel as follows Program RoadwayEquipment Year Materials Maintenance Fuel 500 100,000 110,000 115,000 120,000 125,000 125,000 120,000 120,000 110,000 100,000 10,000 10,000 10,000 10,000 10,000 12,000 12,000 12,000 14,000 14,000 Position 2 Individual Cost Total Cost $51,000 115,500 S166,500 500 525 525 525 525 550 550 550 Supervisor 1 (1 S51,000 S38,500 4 Maintenance Worker II (3 Total: Note that salary & benefit costs can be expected to increase approximately 2.5% per year

Question

Solve the following problem using excel: The program is estimated to require a 10-year commitment to fully achieve the desired result. Analyze the following information using both the Net Present Value (NPV) method and the Internal Rate of Return (IRR) method.
(I have already worked and solved this problem, however as it is complex and contains a large number of values to be calculated, I want to be sure that I applied all appropriate concepts correctly.)
I have attached the directions and problem in a screenshot.

The streets and roads maintained by the government have not had
an adequate maintenance program. The continuing failure to
adequately and promptly repair tailures in the asphalt paving of
local, collector, and arterial roadways has caused the government
to incur excessive repaving/reconstruction costs for its roadways
due to the early failure of their asphalt surfacing and subsequent
deterioration of the roadway's base. The Public Works
Department's Civil Engineers have recommended the
establishment of a dedicated team for the rapid and efficient
accomplishment of the repair of potholes and other paving
Presently, the annual budget for maintaining and refurbishing
failing roadways is $750,000. This amount has historically
(over the most recent 10 years) increased by an average 8%
annually. The Public Works Engineers estimate that the
proposed maintenance program will reduce that budgetary
item's cost annually by the following proportions
ProgramCost
Year Reduction
ures
2
10%
10%
10%
The accomplishment of this recommendation is expected to
require the services of a full-time, dedicated crew of four (4) field
personnel: one (1) Crew Foreman and three (3) Maintenance
Workers. The efforts of this group will be the constant execution
of a job-order report from the Public Works Department's current
Road Inventory computer software's Repair Report List. They
will accomplish their scheduled work using an automated asphalt
patching system for the repair of potholes in major roadways that
will be purchased for this purpose. Purchase price estimated a
$175,000. The purchase price includes training of three (3)
individuals in the operation and maintenance of the equipment.
The cost of the crew's year 1 annual salaries and benefits are
expected to conform to the following schedule
4
6%
The Public Works Engineers also estimate program operating costs
for materials, equipment m
aintenance, repairs, and fuel as follows
Program RoadwayEquipment
Year Materials Maintenance Fuel
500
100,000
110,000
115,000
120,000
125,000
125,000
120,000
120,000
110,000
100,000
10,000
10,000
10,000
10,000
10,000
12,000
12,000
12,000
14,000
14,000
Position
2
Individual Cost Total Cost
$51,000
115,500
S166,500
500
525
525
525
525
550
550
550
Supervisor 1 (1
S51,000
S38,500
4
Maintenance Worker II (3
Total:
Note that salary & benefit costs can be expected to increase
approximately 2.5% per year
Expand
Transcribed Image Text

The streets and roads maintained by the government have not had an adequate maintenance program. The continuing failure to adequately and promptly repair tailures in the asphalt paving of local, collector, and arterial roadways has caused the government to incur excessive repaving/reconstruction costs for its roadways due to the early failure of their asphalt surfacing and subsequent deterioration of the roadway's base. The Public Works Department's Civil Engineers have recommended the establishment of a dedicated team for the rapid and efficient accomplishment of the repair of potholes and other paving Presently, the annual budget for maintaining and refurbishing failing roadways is $750,000. This amount has historically (over the most recent 10 years) increased by an average 8% annually. The Public Works Engineers estimate that the proposed maintenance program will reduce that budgetary item's cost annually by the following proportions ProgramCost Year Reduction ures 2 10% 10% 10% The accomplishment of this recommendation is expected to require the services of a full-time, dedicated crew of four (4) field personnel: one (1) Crew Foreman and three (3) Maintenance Workers. The efforts of this group will be the constant execution of a job-order report from the Public Works Department's current Road Inventory computer software's Repair Report List. They will accomplish their scheduled work using an automated asphalt patching system for the repair of potholes in major roadways that will be purchased for this purpose. Purchase price estimated a $175,000. The purchase price includes training of three (3) individuals in the operation and maintenance of the equipment. The cost of the crew's year 1 annual salaries and benefits are expected to conform to the following schedule 4 6% The Public Works Engineers also estimate program operating costs for materials, equipment m aintenance, repairs, and fuel as follows Program RoadwayEquipment Year Materials Maintenance Fuel 500 100,000 110,000 115,000 120,000 125,000 125,000 120,000 120,000 110,000 100,000 10,000 10,000 10,000 10,000 10,000 12,000 12,000 12,000 14,000 14,000 Position 2 Individual Cost Total Cost $51,000 115,500 S166,500 500 525 525 525 525 550 550 550 Supervisor 1 (1 S51,000 S38,500 4 Maintenance Worker II (3 Total: Note that salary & benefit costs can be expected to increase approximately 2.5% per year

Expert Answer

Want to see the step-by-step answer?

Check out a sample Q&A here.

Want to see this answer and more?

Experts are waiting 24/7 to provide step-by-step solutions in as fast as 30 minutes!*

*Response times may vary by subject and question complexity. Median response time is 34 minutes for paid subscribers and may be longer for promotional offers.
Tagged in
Business
Finance

Related Finance Q&A

Find answers to questions asked by students like you.

Q: The following companies have different financial statistics.    Texas Burgers Tom's Hot Dogs Gr...

A: Texas Burgers:It has got projects that can lead to healthy growth of 22% in sales and earningsLimite...

Q: You are evaluating two different silicon wafer milling machines. The Techron I costs $300,000, has a...

A: Calculation of EAC of Techron I and Techron II:The EAC is calculated using a excel spreadsheet. The ...

Q: Boulder Furniture has bonds outstanding that mature in 13 years, have a 6 percent coupon, and pay in...

A: Calculation of Yield to Maturity:Excel Spreadsheet:

Q: Global Products plans to issue long-term bonds to raise funds to finance its growth. The company has...

A: a)Calculation of Coupon Rate on the New Bond Issue:

Q: Boeing Corporation or Northrop Grumman, for Goldman Sachs. You are anaggressive investment portfolio...

A: Click to see the answer

Q: Barbie is going to borrow $5,000 to help write a book.  The loan is for one year and the money can e...

A: Option 1: 1 year loan borrowed at prime rate = 11%Effective interest cost = 11%Option 2: 1 year loan...

Q: Project: The streets and roads maintained by the government have not had an adequate maintenance pro...

A: The initial investment for the project is $175000 as patching system has to be purchased so that wor...

Q: Could you show me to solve this problem step by step. Please

A: The equation we have to solve is as shown on the white board. We will take the following approach.As...

Q: A bond for the Chelle Corporation has the following characteristics: Maturity 12 years Coupo...

A: Parts a, b and c can be answered as per our policies. For solution to part d, kindly respost this qu...

Q: Assume​ Evco, Inc., has a current stock price of $39 and will pay a $1.80 dividend in one​ year; its...

A: Calculation of Expected Price:

Q: Block, S., Hirt, G., & Danielsen, B. (2017). Foundations of Financial Management. New York, NY: ...

A: Calculating of the expected retained profit for the next year. We have,Profit margin = 12 % of sales...

Q: I asked this question yesterday: Suppose that, in each period, the cost of a security either goes up...

A: At the outset, we regret the approach that was provided to you in the earlier solution. We also stat...

Q: David's Watersports Firm is considering a public offering of common stock. Its investment banker has...

A: Calculating the spread on this issue in percentage terms. We havePercentage spread = (Retail price –...

Q: How long will it take for money to double if invested at 6% compounded quarterly?

A: Compounded Interest:           It is the method of calculating interest at the initial amount of pri...

Q: What are finances?

A: Meaning of finance:  It is a broad term that explains the system and study of investments, money, an...

Q: Someone in the 15 percent tax bracket can earn 8 percent annually on his investments in a tax-exempt...

A: Calculation of After-Tax Value of Investment:The account is a tax exempt account. Hence the 15% tax ...

Q: a corportation that wants to pay off its bond early typically has to pay one or more extra coupons a...

A: a corportation that wants to pay off its bond early typically has to pay one or more extra coupons a...

Q: Better Mousetraps has developed a new trap. It can go into production for an initial investment in e...

A: At the outset, I want to bring to your notice that in the leading line of the question, we have been...

Q: What are spot rates and forward rates? Suppose you open the newspaper today and observe the followin...

A: Answer 1:The correct answer is “discount” in the forward market.Justification: This is because of th...

Q: Use the following information: . Debt: $79,000,000 book value outstanding. The debt is trading at 94...

A: Calculation of Total Market Value:

Q: Your oldest daughter is about to start kindergarten in a private school. Tuition is $10,000 per​ yea...

A: Calculation of Present Value:There are 2 issues with the annuity. The one with the current tuition f...

Q: Block, S., Hirt, G., & Danielsen, B. (2017). Foundations of Financial Management. New York, NY: ...

A: Calculating the anticipated return after financing costs with the most aggressive asset financing mi...

Q: You bought a home for $150,000 and put down 10% and got a mortagage at an interest rate of 4.35% per...

A: We will make use of the PMT function in Microsoft Excel to determine the monthly payment on the mort...

Q: Required: Use an Excel spreadsheet to calculate the net present value NPV from a "Parent company per...

A: Calculating the value of total cost of plant and depreciation expenses using straight-line method in...

Q: A 25-year annuity was purchased with $225,000 that had accumulated in a RRSP.  The annuity provides ...

A: This question is based solved using Excel functions:PMT: Tells us monthly mortgage payment on a loan...

Q: For example, assume Ethan wants to earn a return of 7.00% and is offered the opportunity to purchase...

A: Calculating the intrinsic value of the bond. We have,Intrinsic value = A / (1+C)1 + A / (1+C)2 + A /...

Q: Storico Co. just paid a dividend of $2.65 per share. The company will increase itsdividend by 20 per...

A: Calculation of Price of Stock Today:Excel Spreadsheet:

Q: The ROA of your firm is 5%. The firm also has a debt-asset ratio of 70%. If your firm reinvests 100%...

A: Calculating the value of internal growth rate. We have,Internal Growth Rate = ROA X RR / (1 – ROA X ...

Q: In many of the resources I've found in regards to the Black-Scholes Formula, it uses the natural log...

A: You are right in your observation that all the literature and academic materials available on Black ...

Q: ABC Enterprises' noncallable bonds currently sell for $1,048.50. They have a 5-year maturity, an ann...

A: The yield to maturity of a bond can be calculated using the RATE function in excel. Inputs are:Perio...

Q: Assume that you were recently hired as assistant to Jerry Lehman, financial VP of Coleman Technologi...

A: Note:Since we are entitled to answer up to 3 sub-parts, we shall answer the first 3 as you have not ...

Q: Romagnoli Company is considering a 3-year investment project that involves the purchase of a new man...

A: Since we have to handle multiple variables here, we will use Microsoft Excel to get the final answer...

Q: Countess Corp. is expected to pay an annual dividend of $3.97 on its common stock in one year. The c...

A: Calculating the cost of equity using the formula of dividend discounting model. We have,Current pric...

Q: A project costs $1 million and has a base-case NPV of exactly zero (NPV sign. Enter your answers in ...

A: a.Calculation of Adjusted Present Value:

Q: A firm has the following investment  Cash inflows Year                A                       B     ...

A: g. This question asks for Project A's Internal Rate of Return and the decision if the firm's cost of...

Q: Six potential services a bank provides?

A: A bank is a financial institution that accepts deposits from the public and makes loans from these d...

Q: First and Ten Corporation’s stock returns have a covariance with the market portfolio of .0506. The ...

A: Calculation of Beta:

Q: You borrowed $700 at 5% compounded quarterly. Yourpayments are $150 at the end of each year. How man...

A: We will adopt the following approach:It's a typical time value of money case, where we have to find ...

Q: Consider a project lasting one year only. The initial outlay is $1,000 and the expected inflow is $1...

A: Before we get into solving this problem, let's recall two formulae and concepts:For a project lastin...

Q: Relative inflation rates affect interest rates, exchange rates, the overall economic health of a cou...

A: Answer 1:The statement is a “valid” statement.Justification: The interest rate may comprise two segm...

Q: Tommy Corp. can borrow from its bank at 17 percent to take a cash discount.  The terms of the cash d...

A: The terms of the cash discount are 3/19, net 45. Discount %age, D = 3%Discount period, DP = 19 daysF...

Q: A Company project of 10 years, Paying 2,000,000 a year with a 4% growth each year, at the end of pro...

A: Preparation of table that showing formula step in excel for calculating present value of cash flow. ...

Q: Case Study #3: Chapter 6 Business Analysis -  A business can be valued by capitalizing its earnings ...

A: You have asked a question with five sub parts. I will address the first three sub parts. Please post...

Q: Multinational corporations are exposed to higher risks that primarily come from two significant sour...

A: Expropriation is the act in which taking  over of privately owned property by the government.While, ...

Q: The manufacturer of a product that a variable cost of $2.50 per unit and total fixed cost of $125,00...

A: The manufacturer of a product that a variable cost of $2.50 per unit and total fixed cost of $125,00...

Q: If the annual geometric mean for the equity risk premium is 8.4 percent, what percentage of the equi...

A: Calculation of Percentage of Equity Risk Premium:

Q: The company stock increases dividend by 5% each year and the expecteddividend in 5 years is $5. Anal...

A: Suppose the dividend paid recently=$xDividend paid in year 1=x*(1+growth rate)^1Dividend paid in yea...

Q: Block, S., Hirt, G., & Danielsen, B. (2017). Foundations of Financial Management. New York, NY: ...

A: Unit sales per month can be compared to production per month and the difference will give us the cha...

Q: Here are some historical data on the risk characteristics of Ford and Harley Davidson. Harley Davids...

A: Since you have asked a question with five sub parts, I will address the first three sub parts. Pleas...

Q: Save Help Subr Exit Check my work Marsha Jones has bought a used Mercedes horse transporter for her ...

A: Calculation of NPV of the Investment:The renting is done for every other week which means there are ...