Exercise 1 You own an internet firm and ship products throughout the US. Your clients understand...

   

Transcribed Image Text:

Exercise 1 You own an internet firm and ship products throughout the US. Your clients understand that it will take 10-14 days for them to receive any item that they purchase through your website. You have negotiated a flat rate shipping rate. However, your flat rate shipping rate is determined by the day of the week that the item is shipped and what region of the country the item is shipped to. Shipping rates by day of the week and region are listed below. While your shipping department is extremely efficient it can only ship the following units per day: T Monday Tuesday Wednesday Thursday Friday Saturday All orders must ship within seven days of receipt. T Shipping costs -in dollars T Region 1900 T 2000 T Monday Tuesday Wednesday 1800 T 1700 T 1500 T Solver T 1200 T Units Ordered East 1600 Southeast 1500 Midwest 2000 Southwest 1500 West 1700 Northeast 1600 Monday Tuesday Wednesday Thursday Friday 5 5 4 7 8 9 6 5 5 6 70 8 40 4 3 6 7 11 78 70 70 9 10⁰ 8 8 78 8 8 9 15 Saturday 9 8 7 12 12 20 In addition to the shipping costs there is a labor cost per item to ship each item. The labor cost varies by the day of the week: T $1.75/unit $1/unit T $1/unit T дддд Д Thursday Friday Saturday There is also a fixed cost to operate the warehouse of $6,000 per month, a fixed cost for support staff of $7,000 per month, and a commission paid to the shipping consolidator of 10% of gross shipping costs for the week (not counting overhead). T You also can't ship all of one location's merchandise on the same day (lowers the risk) T $1/unit $2/unit $2.5/unit T Note: make sure that you handle fixed and variable costs appropriately. Keep in mind that you are calculating the costs for a week. T You need to find the lowest shipping costs for the company to ship the units listed in the above schedule. Your spreadsheet must include an answer report. T Exercise 1 You own an internet firm and ship products throughout the US. Your clients understand that it will take 10-14 days for them to receive any item that they purchase through your website. You have negotiated a flat rate shipping rate. However, your flat rate shipping rate is determined by the day of the week that the item is shipped and what region of the country the item is shipped to. Shipping rates by day of the week and region are listed below. While your shipping department is extremely efficient it can only ship the following units per day: T Monday Tuesday Wednesday Thursday Friday Saturday All orders must ship within seven days of receipt. T Shipping costs -in dollars T Region 1900 T 2000 T Monday Tuesday Wednesday 1800 T 1700 T 1500 T Solver T 1200 T Units Ordered East 1600 Southeast 1500 Midwest 2000 Southwest 1500 West 1700 Northeast 1600 Monday Tuesday Wednesday Thursday Friday 5 5 4 7 8 9 6 5 5 6 70 8 40 4 3 6 7 11 78 70 70 9 10⁰ 8 8 78 8 8 9 15 Saturday 9 8 7 12 12 20 In addition to the shipping costs there is a labor cost per item to ship each item. The labor cost varies by the day of the week: T $1.75/unit $1/unit T $1/unit T дддд Д Thursday Friday Saturday There is also a fixed cost to operate the warehouse of $6,000 per month, a fixed cost for support staff of $7,000 per month, and a commission paid to the shipping consolidator of 10% of gross shipping costs for the week (not counting overhead). T You also can't ship all of one location's merchandise on the same day (lowers the risk) T $1/unit $2/unit $2.5/unit T Note: make sure that you handle fixed and variable costs appropriately. Keep in mind that you are calculating the costs for a week. T You need to find the lowest shipping costs for the company to ship the units listed in the above schedule. Your spreadsheet must include an answer report. T

Answer rating: 100% (QA)

Well Firstly Modelling the Data into a Report Format is the key solving point Using solver would be ... View the full answer