Question

Please solve in excel

Reep Construction recently won a contract for the excavation and site preparation of a new rest area on the Pennsylvania Turnpike. In preparing his bid for the job, Bob Reep, founder and president of Reep Construction, estimated that it would take four months to perform the work and that 10, 12, 14, and 8 trucks would be needed in months 1 through 4, respectively. The firm currently has 20 trucks of the type needed to perform the work on the new project. These trucks were obtained last year when Bob signed a long-term lease with PennState Leasing. Although most of these trucks are currently being used on existing jobs, Bob estimates that one truck will be available for use on the new project in month 1, two trucks will be available in month 2, three trucks will be available in month 3, and one truck will be available in month 4. Thus, to complete the project, Bob will have to lease additional trucks. The long-term leasing contract with PennState charges a monthly cost of $600 per truck. Reep Construction pays its truck drivers $20 an hour, and daily fuel costs are approximately $100 per truck. All maintenance costs are paid by PennState Leasing. For planning purposes, Bob estimates that each truck used on the new project will be operating eight hours a day, five days a week for approximately four weeks each month. Bob does not believe that current business conditions justify committing the firm to ad- ditional long-term leases. In discussing the short-term leasing possibilities with PennState Leasing, Bob learned that he can obtain short-term leases of one to four months. Short-term leases differ from long-term leases in that the short-term leasing plans include the cost of both a truck and a driver. Maintenance costs for short-term leases also are paid by PennState Leas- ing. The following costs for each of the four months cover the lease of a truck and driver: Length of Lease Cost per Month 1 $ 4000 2 $ 3700 3 $ 3225 4 $ 3040 Bob Reep would like to acquire a lease that minimizes the cost of meeting the monthly trucking requirements for his new project, but he also takes great pride in the fact that his company has never laid off employees. Bob is committed to maintaining his no-layoff policy; that is, he will use his own drivers even if costs are higher. Managerial Report Perform an analysis of Reep Construction's leasing problem and prepare a report for Bob Reep that summarizes your findings. Be sure to include information on and analysis of the following items:

1. The optimal leasing plan

2. The costs associated with the optimal leasing plan

3. The cost for Reep Construction to maintain its current policy of no layoffs

Answer 1

Answer:-