Question

_NEED EXCEL PLEASE!

1. You are an experienced machinist with a small tool shop. You have been asked to submit a bid to produce twelve (12) special clamps for a new die that is under construction. You estimate that the material costs (metal and fasteners) for each clamp are $150.00. You also estimate that 10.00 labor hours are needed to produce the first clamp. The value of your labor is $50/hour.
   1. From similar orders in the past, you estimate that there is a 90% learning curve for this type of work. Using this information, what is the lowest total bid you should submit if you charge a 10% markup ON MATERIAL ONLY and you wish to recoup your labor costs? (Hint: if you know how, it will save time to set up formulas in Excel. Just attach a print-out of Excel work) (3 POINTS)

2. Your potential customer is receptive to your bid from part b but is hesitant to award you the order because the average cost per clamp that you bid is 5% higher than his target. You respond to the customer that if they would increase their order from 12 clamps to X lamps, you could beat his target price. What is X? (Hint: again, while you can do it by hand, it is tedious so setting it up in Excel saves time. Just attach a print-out of Excel work) (3 POINTS)

Answer 1

a.

Total absolute time for twelfth unit with a Learning bend of 90%

= 9.374 x10 hours = 93.74 hours

Work cost = 93.74 x50 = 4687

Cost of material = 150 x12 =1800

Since 10% markup is required, the material cost will be 1800x1.10 = 1980

All out min. offer for 12 units = 4687+1980 = 6667

b.

Target price of the customer = 6667 /1.05 = 6349.52 =6350

Since expense of material is consistent, the work cost should be diminished

Work cost = 6667-1980 = 4370

Cost per unit for new recommendation

N hours x 50 = 4370

N = 0.874

avg time per unit before = 9.374/12 =0.7811

avg time per unit required now = 0.874x10/12 =0.728

Number of relating units =20

Subsequently, if the vender builds the request size to 20 units, he can be conveyed the item at 5% less.

( The qualities are gotten from the table demonstrating total time for various number of units at various expectation to absorb information)

Booki - Microsoft Exce eview View Home Insert Page Layout Formulas Σ AutoSum |= ==|y.,! Sawrap Text General Fill 11.1A. A, | B l u. m.: S. 스. Cut Sort & Find & Conditional Format . Cell- Insert Delete Format Formatting as Table Styles 2ClearFilter- Select Editing Copy i E Merge & Center. $ % , 넒 pastFormat Painter Cells Styles Alignment 1123455677.9 10: 1221147 21 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22.