Use of a Spreadsheet to Design a Cable Support System

Lab Assignment - Optimization of a cable support system

Background:

Engineers design a wide variety of things. Mechanical engineers may design machine components; Architectural engineers design structures; Electrical engineers design circuits; etc. In all cases though, these different breeds of engineers strive for design quality. Perhaps we can do top quality design work for the Dynamo Lift Company, makers of a wide variety of material handling equipment.

Please consider the blueprint sent to us from Dynamo:

cable angle must be between 20 & 70 degrees cable diameter may be 0.187", 0.250", 0.312", or 0.375" 10 ft. Beam in compression 2850 lb. max load extends from wall

Question? What is a quality design for this lift system?

In real life, Dynamo Lift might include a wide variety of specifications for this lift system. However, in most engineering designs there are two nearly universal principles that help define what quality is. Our definition of quality for this design will be:

LIFT QUALITY = MECHANICALLY FIT for USE (strength) + LOW COST

We will use our developing spreadsheet skills to simultaneously determine strength related fitness for use and cost and thus ensure the best quality design (are we having fun yet?). Let's look closer at this lift system.

As with all designs, some features of the system are fixed and cannot readily be altered. In this case the maximum load of 2850 lbs. and the 10 ft. distance from the wall are fixed customer specifications that determine how big and heavy an item can be supported. Dynamo Lift has, however, asked us to find the best cable size and angle.

Ahhh, but here is the rub! It has been determined by mother nature and father industry that skinny cables cost less than fat ones but skinny cables cannot hold as much weight. Also let it be known that as the cable angle gets larger, the cable length gets longer (look at the drawing, you will see!). Long cables cost more than short ones because we pay for them by the foot. Finally, the load in the supporting cable decreases as the angle is increased. Lower loads mean we can use skinny cables that cost less than fat ones.

Yarrgggh!!!! This is clear as mud. At this point we have two options: 1.) change majors from engineering to basket weaving or 2.) clarify the problem further using Foreign Ginst diagrams (named after Knot A. Ginst, the notoriously positive expatriate that came to America with this method of problem solving; not to be confused with his oft troubled step brother, Bach A. Ginst-duWall).

Those of us that are still breathing will notice at first glance that the factors for low cost are the factors against high strength (and vice versa). Recall that we wish to do top quality design work and quality = low cost + high strength. We must somehow strive to minimize cost while maintaining sufficient strength to reliably support the maximum load of 2850 lbs. Let's develop a spreadsheet to help us optimize our lift system. On the next page there is an example spreadsheet. We'll use it as a guide when creating our spreadsheets. Use the following steps to flesh out the spreadsheet.

Open a new spreadsheet and type in the column headings as shown. If you find that you need to insert an extra row or column, position the cursor and use INSERT, ROWS or INSERT, COLUMNS. Delete rows and columns by selecting the row number or column letter and press delete. If you wish to alter a column width use FORMAT, COLUMN, WIDTH.

Type 20 in the cell beneath the heading "angle in degrees". Then click, hold and drag downward 51 cells to highlight them. Use EDIT, FILL, SERIES... to incrementally number these cells from 20 to 70 degrees (our design limits specified by the customer's blueprint). Remember to enter the appropriate step and stop value in the pop-up dialogue

WARNING! The computer seldom understands degrees. Usually, computers work with radians (another unit of measure for angles). We must convert degrees into radians by multiplying by pi (3.14159, this is @PI() in spreadsheet language) and then dividing by 180. Let's say for example that we have 20 in cell A10. In spreadsheet lingo, we write the conversion as =A10*PI()/180. We use the cell address rather than the number 20 so we can copy our formula in a relative manner; that is, the address changes automatically and in a proportionate or relative manner to the direction of the movement.. By the way, 20 degrees = 0.349 radians (check to see that you did it right).

Highlight the cells into which you wish to copy your formula. Use your mouse to highlight the formula. Click the right mouse button to bring up the edit menu. Select COPY. Now highlight the fifty cells below that in which you entered the formula. Click the right mouse button again and select paste. If you did it correctly, all cells should now have the proper formula entered.

The force in the cable is a function of the load suspended and the angle of the cable. We will use the maximum load since this is the worst case scenario for our design. Mathematically, cable force=2850/sin(angle). Let's use as an example the 0.349 radians in cell B10. In spreadsheet lingo our formula becomes =2850/sin(B10). Fill this down as we did in the last step.

The length of the cable is 10/cos(angle). Are you getting the picture?

The cost of the cable system is the length of the cable times its price/foot plus the cost of 2 end fittings. This information is included in the table below with the maximum safe working force allowed for the different cable diameters.     .187" .250" .312" .375"   cable $/ft/ft. $.82 $1.23 $1.47 $2.13   each end fitting $7.45 $8.42 $9.23 $13.13   max force 3000# 4200# 6200# 9300#

After you have written and filled down all of your formulas, click the print view button. If your spreadsheet prints out on more than one page, select a cell beneath and to the right of your row and column titles. Use WINDOW, FREEZE PANES to keep your titles on the screen. To keep your titles on the page, use the FILE, PAGE SETUP... menu item. Under the page tab of the dialogue box, select the FIT TO: radio button and enter as 1 page wide and 1 page tall. This forces a reduction in font size to fit the output on 1 page. If you do not mind printing on multiple pages, select the sheet tab and enter the appropriate rows and columns you wish to "hold constant", that is, to have print on each page.

Finally, let's choose the highest quality design. Using the force column, identify the minimum angle with which each cable can be used. The force cannot exceed the maximum safe working load of the cable. At the minimum angle we use the least amount of cable. This will be the most cost effective solution for each cable system (bigger angles cost more, smaller angles cause to much force). After you identify the most cost effective solution for each cable system, compare the different cable systems. One is the most cost effective and still has sufficient strength. This is the best design.

Write a brief formal letter to Ms. Vinelle Dinette of Dynamo Lift explaining your solution. Include a copy of the spreadsheet.

Sample Layout of spreadsheet:

your name		Cable optimization for the Dynamo Lift Co.
				Total cable system cost
				cost = cable + 2 fittings
angle in degrees	angle in radians	force in cable in pounds	length of cable in feet	0.187" Dia.	0.250" Dia.	0.312" Dia.	0.375" Dia.
20
21
22
23
24
25
...........
69
70

This example might be a good starting point for your spreadsheet

Return to Home Page