I have attached the instructions and an example of the project. My subject is BMW company and I have to create an ISSUE and Fix it. So, The ISSUE I picked isISSUE: Lack of demand in the last ten
Home Depot: New Land Clearing Department
Executive Summary
A need for a standardized land clearing services in the Texas Hill Country has been identified and Home Depot wants to be the first to fill that need. In order to open a new land clearing department, a few things needed to be decided: which location will open the first department, what steps need to be taken in order to make it happen, and what machinery will the department have on hand for land clearing services. A linear programming model was used to make the location decision. Date on ten different cities in the Texas Hill country were collected, analyzed, and applied to three candidate cities for the first Home Depot land clearing department location. The number of raw acres found there, the number of acres currently being developed in the area, and finally the amount money spent on land clearing each year were analyzed and used to predict the amount of money spent on land clearing in our three potential cities. The candidate of Dripping Springs was selected as the location to open the original land clearing department because it proved to have the market with the most potential for land clearing services. A PERT/CPM model was set up with nine different activities in order to get the department opened for business. The overall project is expected to take 98 days, but the budget set allows for up to 108 days to get everything done. This budget gives Home Depot 70% confidence that the project will not lose money. A linear programming model was set up in order to decide the type and quantity of machinery to purchase for the department in order to maximize the number of acres Home Depot can clear in a day. Based on a purchasing budget of $350,000, daily operation budget of $3,000, and a few maximum and minimum quantity requirements for each type of machinery an optimal solution of one wood chipper, ten chain saws, and six skid steers was found. This specific combination of machinery allows Home Depot to clear up to 23.5 acres on land per day. These models set up a clear plan of action for Home Depot to take in order to successfully open new land clearing department in their stores.
Table of Contents
Introduction ……………………………………………………………………………………….4
Multiple Regression Analysis……………………………………………………………………..6
PERT/CPM Analysis……………………………………………………………………………...8
Linear Programming Analysis …………………………………………………………………....9
Implementation ………………………………………………………………………………….11
Conclusion ………………………………………………………………………………………12
Appendix A ……………………………………………………………………………………...13
Appendix B……………………………………………………………………………………... 15
Appendix C ……………………………………………………………………………………...17
Introduction
The Home Depot is currently the world’s largest home improvement retailer that offers more than one million products for the DIY customer, professional contractors, and the industry’s largest installation business for the Do-It-For-Me customer. They offer a wide variety of installation, remodeling, and repair services including doors and windows, exterior home, flooring, kitchen and bath, roofing, and heating/cooling. At select locations you can even rent trucks, large equipment, and just about everything else you would need in a home improvement project. Out in the Texas Hill Country there is an endless need for the clearing of land so it can be developed into residential areas, shopping centers, or even just used for ranching. Home Depot would like to extend their services to include a land clearing department equipped with machinery and a team to go on site and clear land for personal and commercial use. This would allow for Home Depot to enter the market of large scale projects as well as still allow their services to be utilized by the individual developer. The new land clearing department would go out on site to clear land of cedar trees, brush, cactus, and dead trees. Right now, there is not just one company known for offering these services. There are plenty privately owned companied that will clear land, but often times they are hard to find, don’t operate on a guaranteed schedule, or don’t have a standard for pricing set. If a large corporation like Home Depot enters the market for land clearing it will offer a reliable service that is standardized across the board, and convenient for commercial and independent projects. In the actual retail store locations there will be a section specifically for the land clearing department. Here customers will be able to schedule a land clearing team, as well as talk to a representative about all aspects of the department. This section will also serve as a main office for the land clearing department where workers will report for work and ideally store all equipment and documentation for the job. In order for Home Depot to open this department, they will first start in a single store and eventually expand into every location that could benefit from this new service.
Multiple Regression Analysis
The first order of business in starting a new department at Home Depot is to determine which store location will have the first department opened. Data on ten different cities across the Texas Hill Country was analyzed using a multiple regression model, then applied to data collected on three other cities in order to decided where the first land clearing department will be opened. The first variable used was the amount of raw land in acers that currently exists in and around each city. Raw land is defined as any land found in its natural state, without any man made alterations to it. These numbers show the amount of untouched land in each area that could potentially need clearing at some point in the future. The second variable looked at is the amount of land in acres that is currently being developed in and around each city. These numbers show to what degree each city is actively developing raw land. This can translate into the likelihood of more raw land in need of clearing in the future. The ten cities analyzed had on record the amount of money spent on land clearing per year, and the regression analysis was used to predict the amount of money spent on land clearing per year in the three cities under consideration for the implementation on the first Home Depot land clearing department. These numbers give an idea to the market value in each city for land clearing.
It was found that the amount of land currently being developed in each city has an incredibly storing direct correlation to the amount of money spent on land clearing each year. The amount of raw land available in each city had a moderate inverse correlation to how much money was spent on land clearing each year. Overall, the amount land currently being developed was the strongest predictor of the amount of money being spent each year on land clearing. This means that when predicting the volume of money put into land clearing each year in our three candidates, the amount of land currently being developed in each of these cities has the biggest influence on those numbers.
The multiple regression model set up has been found to be extremely reliable when predicting the amount of money spent on land clearing per year. So when finally making the decision of which location to open the first land clearing department, this model is a great resource. Based on this multiple regression model, the location with the most potential for profit is the Dripping Springs location based on the amount of raw land in the area, land currently being developed, and the predicted amount spent on land clearing per year. It shows to have the greatest amount of untouched land available over New Braunfuls and Bulverde, as well as have the most land currently undergoing development. It has been predicted that Dripping Springs also has the most money being spent on land clearing per year, which means it has the greatest potential for future profit in the business of land clearing for Home Depot to take advantage of.
PERT/CPM Analysis
Once a store location was decided on for the land clearing department, a project map was created of all the main activities in need of completion before the department could officially be up and running. In order to determine an expected completion time and set a budget for the project, a PERT/CPM model was put together made up of nine activities: hiring management, acquiring storage space for equipment, setting up the department in store, hiring general employees, purchasing equipment, training all employees, establishing a dumpsite, getting all employees the proper licensing, and getting the company licensed. The critical path of this project was found to be hiring managers, hiring employees, training all employees, and getting employees licensed. All of these activities directly affect the expected completion time of the entire project because they individually have zero slack in timeline to completion. After optimistic, probable, and pessimistic times for completion were established for each of the critical activities, it was determined that the expected completion time for the project was 98.83 weeks.
Labor costs for the start-up of the new land clearing department run at $3,000 a week. That means at the expected rate the start-up project is running, labor costs will be about $300,000 to get the department opened for business. Originally a budget of $250,000 in labor costs was set which put the required completion time at no more than 88.33 days. In order to meet that completion deadline, roughly ten day would have needed to be shaved off the critical activities. After running the numbers the probability of that actually happening sat at a mere 19.49%. Home Depot asks for 70% confidence in a project not losing money. That meant the project budget needs to be set at about $325,000. With this budget, the project can be completed in about 108 days, which sits well within the overall pessimistic completion time of the project.
Linear Programming Analysis
One of the activities that needed to be completed before opening for business is the purchasing of equipment. In this business the purchasing of equipment is a long term investment because they can be used for a long time, and the quality of equipment can increase efficiency in land clearing. A linear programming model was set up in order to determine the combination of clearing equipment that should be purchased by Home Depot to maximize the number of acres the clearing team can clear per day. Four different pieces of machinery were considered in the selection of equipment to purchase, purchasing price, cost of daily operation, and acres per day clearing capabilities were all taken under consideration. First was a chainsaw has a price tag of $40,000, and daily operation cost of $170, and can clear 2.5 acres per day. Second is a chainsaw that costs $460, a daily operation cost of $150, and can clear .3 acres per day. Next was a skid steer that has a price of $50,000, a daily operation cost of $180, and can clear 3 acres per day. Last there was a 4x4 tractor that costs $29,000 to buy, $175 to operate per day, and can clear 1.4 acres per day.
Five different constraints were set on the problem in order come up with the most functional combination of the 4 pieces of machinery. A budget of $350,000 was set to purchase equipment, and a daily budget of $3,000 in operation costs. No more than 10 chainsaws can be purchased, and there has to be at least one wood chipper. Lastly, there has to either be at least one skid steer or one 4x4 tractor. In terms of functionality, both the skid steer and 4x4 tractor perform the same tasks, a skid steer is just nicer and can clear more acres per day.
After formulating a linear programming model with the previous information, the optimal solution was calculated to be one wood chipper, ten chainsaws, six skid steers, and zero 4x4 tractors. This combination of machinery allowed the land clearing department to clear a maximum of 23.524 acres per day if all equipment is used to its full potential. This solution leaves $248.56 in the daily operation cost budget, and provides a surplus of 5 skid steers. If the constraint on number of chainsaws allowed to be purchased was raised to 11 instead of 10, and the purchasing budget allowed, one more could be bought and would increase the acreage clearing efficiency to 23.796 acres per day. Any more chainsaws added to the equation would put the numbers outside of the objective coefficient range and require a recalculation. The set-up of this linear equation worked out to where there are very few changed are available to increase the maximum acres possible to clear in a day.
Implementation
After running a full analysis on these three statistical models, information has been pulled that will help the start-up of this new land clearing department Home Depot is wanting to open. Based on the multiple regression model, the best candidate for the first location of the new department is Dripping Springs, Texas. The PERT/CPM model was set up in order to draw out a plan of action to open this new department. The expected completion time of the project is 98.38 days. A labor budget of $250,000 has been set to get through the start-up project, which allows up to 108 days for completion. The purchasing of machinery was broken down in the linear programming model. In order to maximize the number of acres the department can clear per day, Home Depot needs to purchase one wood chipper, ten chain saws, and six skid steers. That allows the department to clear up to 23.524 acres of land per day. Under the current constraints set, there is little opportunity to increase the acreage clearing ability of the department.
Since this is a plan to begin a brand new service through Home Depot, there is not any prior standards set on how it should all run. The plans established based on this analysis should be used to set a foundation in the development of this new department. Home Depot executives put in charge of the development of the land clearing department should take into consideration all information established, and change where they see fit as the project is in motion.
Conclusion
The opportunity for Home Depot to enter into the market of land clearing was identified and a system has been put together in order to get the most out of said opportunity. To test the market, Home Depot will start by opening just one land clearing department in the Dripping Springs location. That city has been identified as the most promising market for the land clearing services. Then the start-up plan has been laid out with nine different activities to be completed before the business can officially start operating. The labor cost budget has been set at $250,000 which allows 108 days for the entire project to be completed. The equipment purchasing activity in particular was individually analyzed to decide what machinery Home Depot needs to invest in. The goal of these investments is to maximize the number of acres the clearing department can clear within a day. The final solution is to purchase one wood chipper, ten chainsaws, and six skid steers. This combination of machinery will allow Home Depot to clear about 23.5 acres per day while staying within the spending budgets and having all the necessary equipment to clear land.
Appendix A
Multiple regression model
Cities | Raw Land (Acers) [x1} | Land Currently Being Developed (Acres) [x2] | Recorded $ Spent on Land Clearing Per Year [Ŷ] |
Kerrville | 10,000 | 3,250 | 1,137,500 |
Llano | 30,000 | 2,500 | 500,000 |
Burnet | 25,250 | 1,000 | 250,000 |
Boerne | 9,000 | 4,000 | 1,600,000 |
Bandera | 12,500 | 5,300 | 1,457,500 |
Leakey | 32,750 | 525 | 157,500 |
San Marcos | 5,000 | 3,200 | 1,600,000 |
Bastrop | 29,950 | 2,000 | 850,000 |
Lockhart | 21,300 | 4,700 | 1,997,500 |
Georgetown | 1,700 | 1,000 | 425,000 |
Predicted $ Spent on Land Clearing Per Year [Ŷ] | |||
Dripping Springs | 25,250 | 6,200 | 2,099,704.03 |
New Braunfuls | 6,200 | 3,800 | 1,454,734.68 |
Bulverde | 17,550 | 5,425 | 1,905,002.56 |
Ŷ = 223535.501 – 8.764x1 + 338.298x2
Appendix A cont.
Appendix B
PROJECT SCHEDULING WITH PERT/CPM
********************************
*** PROJECT ACTIVITY LIST ***
IMMEDIATE OPTIMISTIC MOST PROBABLE PESSIMISTIC
ACTIVITY PREDECESSORS TIME TIME TIME
-------------------------------------------------------------------------
A - 25 31 40
B - 4 7 10
C - 20 28 35
D A 25 31 40
E B 10 14 18
F D,E 18 21 28
G E 10 14 20
H F 10 14 19
I C,G 15 21 25
-----------------------------------------------------------------------------
EXPECTED TIMES AND VARIANCES FOR ACTIVITIES
ACTIVITY EXPECTED TIME VARIANCE
-------------------------------------------
A 31.50 6.25
B 7.00 1.00
C 27.83 6.25
D 31.50 6.25
E 14.00 1.78
F 21.67 2.78
G 14.33 2.78
H 14.17 2.25
I 20.67 2.78
-------------------------------------------
*** ACTIVITY SCHEDULE ***
EARLIEST LATEST EARLIEST LATEST CRITICAL
ACTIVITY START START FINISH FINISH SLACK ACTIVITY
-----------------------------------------------------------------------------
A 0.00 0.00 31.50 31.50 0.00 YES
B 0.00 42.00 7.00 49.00 42.00
C 0.00 50.33 27.83 78.17 50.33
D 31.50 31.50 63.00 63.00 0.00 YES
E 7.00 49.00 21.00 63.00 42.00
F 63.00 63.00 84.67 84.67 0.00 YES
G 21.00 63.83 35.33 78.17 42.83
H 84.67 84.67 98.83 98.83 0.00 YES
I 35.33 78.17 56.00 98.83 42.83
----------------------------------------------------------------------------
CRITICAL PATH: A-D-F-H
EXPECTED PROJECT COMPLETION TIME = 98.83
VARIANCE OF PROJECT COMPLETION TIME = 17.53
Appendix B cont.
Labor Cost = $3,000 per week
Labor cost budget of $250,000
Z= = -.88 19.49% Probability
70% probability to not lose money
Z=.52 = x=107.95
x*$3,000 = $323,836.8 ≈$325,000
Appendix C
LINEAR PROGRAMMING PROBLEM
MAX 2.5X1+0.3X2+3X3+1.4X4
S.T.
1) 45000X1+460X2+50000X3+29000X4<350000
2) 170X1+150X2+180X3+175X4<3000
3) 1X2<10
4) 1X1>1
5) 1X3+1X4>1
X1: Wood chipper
X2: Chainsaw
X3: Skid Steer
X4: 4x4 Tractor
OPTIMAL SOLUTION
Objective Function Value = 23.524
Variable Value Reduced Costs
-------------- --------------- ------------------
X1 1.000 0.000
X2 10.000 0.000
X3 6.008 0.000
X4 0.000 0.340
Constraint Slack/Surplus Dual Prices
-------------- --------------- ------------------
1 0.000 0.000
2 248.560 0.000
3 0.000 0.272
4 0.000 -0.200
5 5.008 0.000
Appendix C cont.
OBJECTIVE COEFFICIENT RANGES
Variable Lower Limit Current Value Upper Limit
------------ --------------- --------------- ---------------
X1 No Lower Limit 2.500 2.700
X2 0.028 0.300 No Upper Limit
X3 2.778 3.000 32.609
X4 No Lower Limit 1.400 1.740
RIGHT HAND SIDE RANGES
Constraint Lower Limit Current Value Upper Limit
------------ --------------- --------------- ---------------
1 No Lower Limit 350000.000 419044.444
2 2751.440 3000.000 No Upper Limit
3 0.000 10.000 11.676
4 0.000 1.000 6.564
5 No Lower Limit 1.000 6.008