As shown in Table 1, the boat manufacturer has provided an estimate of the average speed of each unit and the fuel consumption based on this average speed. Since the boats deliver packages over fairly short distances, the higher speed of the gasoline engine is only valued at $50 per day. The delivery service operates 19 hours per day, 365 days per year. During these 19 hours, the gasoline engines will be turned off while not in use, but the diesel units will idle and burn fuel at the rate of 2 gallons per hour if they are not in use. Both units are turned off during the 5 hours the delivery service is not operational. Both units can meet the schedules and requirements of DBW. Table 1: Boat specifications Purchase price Engine size (horsepower) Average speed (manufacturer's estimate) Knots (nautical miles per hour) Fuel consumption (gallons per hour) Fuel capacity (gallons) Gasoline $124,200 345 hp 24.6 29 325 Diesel $101,335 310 hp 21.2 21 325 The diesel unit requires more maintenance than the gasoline engine unit due to the diesel unit's cooling system. Annual maintenance costs are $7,500 for the diesel engine and only $6,000 for the gasoline engine. The cost of oil changes is $51 for the diesel unit and $29 for the gasoline unit. Oil changes occur every 120 hours of engine use for both types of engines. Idling hours do NOT count towards the 120 hours of engine use. Diesel is estimated to run $3.17 per gallon while gasoline runs $2.97 per gallon. The boats will typically travel 275 nautical miles in the course of the day. Each branch office is located adjacent to a fueling dock run by another business unit of DBW's parent company. Refueling costs more during the day (i.e., while the delivery service is in operation), and DBW prefers to refuel each boat every evening when the delivery service is not operating. The boats are docked at the fueling facility overnight, and each evening the tanks are refilled to capacity whether or not the boat was refueled during the day. Since a boat's fuel capacity is capped at 325 gallons, if a boat uses more than 325 gallons during the day, then the boat will need to be refueled during the day. In addition to the price of the fuel, refueling during the evening costs $20. If refueling must be done during the day, it costs $53. The gasoline units, if purchased, will be sold after 5 years of service for $32,000 each. The diesel units, if purchased, will be kept in service for 4 years before salvaged for $48,000 each. You can assume that each type of unit can be purchased at the same costs in the future. DBW's minimum attractive rate of return (MARR) is 20%. DBW suspects that the number of nautical miles per day may change in the future and is interested in knowing how many nautical miles per day must be traveled to change the preferred alternative. You should evaluate the purchase of diesel units and gasoline units and financially compare between the two units. To do this, you should translate the operating costs (e.g., oil changes, cost of fuel, cost of refueling) to annual costs. You can use decimals in your calculations (e.g., do not round the number of oil changes). A spreadsheet DBW financials.xlsx" is available to guide you in your calculations. You will need to enter additional costs to calculate at the end of the spreadsheet. You should write a professional memorandum (memo) advising DBW on which unit it should purchase. See the attached Word document "Writing a professional memo.docx" for expectations and guidelines on writing a professional memo. You should upload your memo and completed Excel spreadsheet via Canvas. If you are working with another student, only one person needs to upload the memo, but make sure that both individuals' names are on the memo. Your grade will be docked by 10 points for each day that you submit the memo after the due date. The memo should contain 4-5 paragraphs. The first paragraph should be an executive summary that summarizes your analysis and provides your final recommendation. The executive summary should also state the key metrics and numbers that support your recommendation. One paragraph should discuss the costs of the gasoline unit and the other paragraph should discuss the costs of the diesel unit. You should NOT go into details about the calculations in the memo, but you should outline the major costs. A table will be helpful to provide the costs. The final one or two paragraphs should briefly compare the two units and explain which unit you recommend. Be sure to explain on what basis you are making your recommendation. You should try to provide some intuition for why that unit is the best. You should also answer DBW's question of how many nautical miles per day would change your recommendation. You should assume that both types of units will always have identical nautical miles per day. Nautical miles per day Operations of company (hours per day) Salvage Years of service MARR CALCUALTIONS Hours that a boat operates per day Hours that a boat is idle per day Fuel used per day (gallons) Number of oil changes per year COST CALCULATIONS Annual value of higher speed Annual cost of oil changes Annual cost of refueling Annual cost of fuel 275 19 $32,000 5 20% 275 19 $48,000 4 20% Note: you will need to calculate other costs in addition to those listed. Please enter those into the spreadsheet