Company Profit An automobile parts supplier produces four different parts: pistons, brake shoes, crankshaft pulleys, and exhaust valves. The company has determined that the costs per part arc as follows:

Labor Costs $4, $3, $5, and $9 respectively,

Materials $5, $3, $7, and $t respectively,

Maintenance (for machine) $5, $2, $6, and $10 respectively, and

Engineering $8, $11, $4, and $13 respectively.

The sum of these four costs is the manufacturing cost per part. The company has estimated that the overhead [the cost of insurance, lighting, rent on building, etc.) is equal to 20% of the manufactunng cost. The sum of the manufacturing cost and the overhead cost is the total cost of making the part. The company wants their profit to be equal to 22% of the total cost. The sum of the total cost and the profit is the selling price of the part.

Use Microsoft Excel for the following:

1. Develop a spreadsheet which shows the following costs: labor, materials, maintenance, engineering, the manufacturing cost, the overhead, the total cost, and the selling price for each product.

2. Develop a bar graph that shows all of the information on your spreadsheet in an easy to read format. Include labeling as appropriate.

3. If the company makes 150 pistons, 300 brake shoes, 200 crankshaft pulleys, and 130 exhaust valves per day and sells them all that day, what would be their total profit that day? Detemine the percent of total profit for each of the four products? Show these calculations on the spreadsheet.

4. Plot a graph [any kind) of the percentage of total profit gained from each type of part.

Answer rating: 100% (QA)

Answer 1 Particulars Pistons Brake shoes Crankshaft Pulleys Ex... View the full answer