Please specify what formulas to use in colored section of graph.
Problem 1: The Green Machine Manufacturing Company is trying to decide which of two suppliers to purchase engines from. Green Machine has agreed to cover the tooling cost for the supplier selected. The relevant information is provided below. Your work should be done using the tab labeled Problem 1 in the spreadsheet template for Homework #1. The spreadsheet should be formatted to show all calculations out to two decimal places using the currency format. However, the calculations for each questions should be rounded off to zero decimal places using the ROUND function. If done correctly, this means all answers should have a zero in the cents portion of the calculation (i.e., the cents portion of the number should be shown, but it should always be .00). Order lot size 860 units 86,000 units Engine weight Order processing cost Inventory carrying rate l $275/ order 9% per year Supplier Supplier 2 Unit Price 1 to 999 units/order Unit Price 1000 to 2999 units $206.00 order $220.00 $209.00 $215.00 Unit Price 3000+ units order$200.00 $205.00 Tooling Cost Distance $11,500 105 miles 145 miles $9,750 Supplier defect rate (% of engine cost 3% 190 Truckload (TL40,000 lbs.): $1.25 per ton-mile Less-than-truckload (L.TL): S1.95 per ton-mile Note: per ton-mile 2.000 pounds per mile A. What is the purchasing cost of the engines from each supplier? B. What is the annual ordering cost from each supplier? C. What is the annual inventory carrying cost for each supplier? D. What is the annual tooling cost for each supplier? E. What is the annual transportation cost for each supplier? F. What is the annual quality cost for each supplier? G. What is the total cost for each supplier? Order lot size (units) Annual demand (units) Engine weight bs Order processing cost ($/order) 860 86,000 51.5 $275 9% Supplier 1 er Unit Price 1 to 999 units/ order Unit Price 1000 to 2999 units/ order Unit Price 3000 +units/ order Tooling Cost Distance (miles) $220.00 $205.00 $9,750 145 1% $200.00 105 3% Truckload (TL 240,000 lbs.): $ per ton- mile Less-than-truckload (LTL): $ per ton- Note: per ton-mile pounds per mile $1.25 Total weight Formula Total Engine Cost Ordering Cost Carrying Cost Tooling Cost Transportation Cost Quality Cost Supplier 1 Formula Formula Formula Formula Formula Formula Supplier 2 Formula Formula Formula Formula Formula ormu Total Cost Formula Formula Problem 1: The Green Machine Manufacturing Company is trying to decide which of two suppliers to purchase engines from. Green Machine has agreed to cover the tooling cost for the supplier selected. The relevant information is provided below. Your work should be done using the tab labeled Problem 1 in the spreadsheet template for Homework #1. The spreadsheet should be formatted to show all calculations out to two decimal places using the currency format. However, the calculations for each questions should be rounded off to zero decimal places using the ROUND function. If done correctly, this means all answers should have a zero in the cents portion of the calculation (i.e., the cents portion of the number should be shown, but it should always be .00). Order lot size 860 units 86,000 units Engine weight Order processing cost Inventory carrying rate l $275/ order 9% per year Supplier Supplier 2 Unit Price 1 to 999 units/order Unit Price 1000 to 2999 units $206.00 order $220.00 $209.00 $215.00 Unit Price 3000+ units order$200.00 $205.00 Tooling Cost Distance $11,500 105 miles 145 miles $9,750 Supplier defect rate (% of engine cost 3% 190 Truckload (TL40,000 lbs.): $1.25 per ton-mile Less-than-truckload (L.TL): S1.95 per ton-mile Note: per ton-mile 2.000 pounds per mile A. What is the purchasing cost of the engines from each supplier? B. What is the annual ordering cost from each supplier? C. What is the annual inventory carrying cost for each supplier? D. What is the annual tooling cost for each supplier? E. What is the annual transportation cost for each supplier? F. What is the annual quality cost for each supplier? G. What is the total cost for each supplier? Order lot size (units) Annual demand (units) Engine weight bs Order processing cost ($/order) 860 86,000 51.5 $275 9% Supplier 1 er Unit Price 1 to 999 units/ order Unit Price 1000 to 2999 units/ order Unit Price 3000 +units/ order Tooling Cost Distance (miles) $220.00 $205.00 $9,750 145 1% $200.00 105 3% Truckload (TL 240,000 lbs.): $ per ton- mile Less-than-truckload (LTL): $ per ton- Note: per ton-mile pounds per mile $1.25 Total weight Formula Total Engine Cost Ordering Cost Carrying Cost Tooling Cost Transportation Cost Quality Cost Supplier 1 Formula Formula Formula Formula Formula Formula Supplier 2 Formula Formula Formula Formula Formula ormu Total Cost Formula Formula
