Could I get some help on this case study? Anything would be helpful - what equations to use, Excel setup, etc.

Case 32 Mr. Speedy Mr. Speedy is a heating and air conditioning repair business that was established 23 years ago by George Moustakis. For the first 15 years Mr. Speedy grew steadily, but then George decided that the business was as large as he could successfully supervise. Today the business revolves around 20 vans that are on the streets, and another four for backup in the shop. The 20 vans are not all out at once as there is day, night, and weekend coverage using 32 technicians. Each technician is assigned to a van, and each van has only one or two technicians assigned to it. George has tried using a smaller pool of vans, which requires rotating them among the technicians. He found that this radically increased his costs. First, the vehicles were treated more like somebody else's problem. The drivers were harder on the vehicles, and they did not communicate as well with the mechanics. Second, and more importantly, restocking the truck at the end of a shift was sometimes slipshod. Now the technicians are very consistent about restocking the parts used during the day, when they still have paperwork on what was done. Otherwise, they may be short the next day. The technicians receive a completion bonus, which may equal their normal salary. Thus, interrupted jobs that require a return to the warehouse are the bane of the technicians. The average age of George's fleet of vans has crept up and is now 4 years. His vans generally last 7 years before they are abandoned. He is getting complaints from the technicians and the mechanics. The vans are spending more time in the shop. Most of the problems can be dealt with after the end of a shift, and only a few interrupt the work of the 148 Case 32 Mr. Speedy technicians. George has asked his shop manager and his bookkeeper to analyze the economics of van replacement using a 10% interest rate for the time value of money. Their responses are the two memos that follow this introduction. These vans are somewhat special. After they are purchased from a dealer, another vendor installs a van liner designed for storage of parts. Then the van is completely stocked. New, the vans cost about $14,000, the liners add another $4000, and the truck's mini-inventory costs another $5000. When a truck is retired, its inventory can be transferred, but its liner is worthless. The annual maintenance costs for the vans start at $500 per year and increase by $200 each year thereafter. As the vans age, there begins to be more of an operating cost due to missed calls. These costs begin at $250 and increase by $750 per year thereafter. Options 1. Assume a 40% marginal tax rate for combined state and federal income taxes, and use a 6% after-tax interest rate. Ignoring capital gains and investment tax credits, does your recommendation change? 2. Focus on the total number of vans and what changes in the replacement schedule would be necessary to change the number of backup vans. Which parameters are most critical in making this decision? Question 1 notes: Ignoring capital gains and investment tax credits, what van replacement schedule do you recommend? What factors could cause your recommendation to change? Question 2 notes: How would you project the required number of backup vans for a given fleet age average / distribution? What type of analysis could you perform? What data would you need? No need to develop a detailed quantitative model. 149 Cases in Engineering Economy 2nd by Peterson & Eschenbach To: George From: Igor, Shop Manager In analyzing our van replacement problem, I followed an example that appeared in last month's Fleet Manager. I recommend replacing the vans after 5 years (two years sooner than we do now). I also recommend that we continue to retire any van within a year of retirement (now 4 rather than 6 years) when a major repair becomes necessary. I asked Vincent what interest rate to use, and he told me 10%. My calculations follow. Year 0 1 2 3 4 5 6 7 150 Market Drop in Interest on Maintenance Value Value Salvage Cost Total 18,000 10,000 8,000 800 500 9,300 7,000 3,000 300 700 4,000 5,000 2,000 200 900 3,100 4,000 1,000 100 1100 2,200 3,500 500 50 1300 1,850 3,000 500 50 1500 2,050 2,500 500 50 1700 2,250 Case 32 Mr. Speedy To: George From: Vincent Cash, Bookkeeper In analyzing our van replacement problem, I used generally accepted accounting principles. I recommend continuing to replace vans every seven years. The only reason I do not recommend keeping the vans longer is increased repair (not routine maintenance). For each year longer we keep a van, it spends half a day more per month out of service. With an average age of 4 years, the 20 vans average 40 days per month of repair work. If this were level, we would not have a problem, but it is not. We have to juggle the schedule, when we need an unavailable fifth backup van. This would be worse if we delayed replacement. Igor should be able to give a better estimate. But, we could assume that every 10 days per month of repair work requires another backup van. Under the current tax law, the schedule for 5-year property allows us to depreciate using the following percentages (20, 32, 19.2, 11.52, 11.52, 5.76%). The vans cost us $18,000 initially. Year 1 2 3 4 5 6 7 Book Value Year's Start 18,000 14,400 8,640 5,184 3,110 1,037 0 Depreciation 3600 5760 3456 2074 2074 1037 0 Interest Operating on BV Cost Total 1800 250 5650 1440 1,000 8200 864 1,750 6070 518 2,500 5092 311 3,250 5635 104 4,000 5140 0 4,750 4750 151