I would suggest using the excel spreadsheet attached below as a template for this question

You are a logistics manager for BMW, and you need to decide which mode(s) of transportation toutilize to transport 400 vehicles (new BMW X5s, each valued at $62,000) from a manufacturing site in Greer, South Carolina to car dealerships across the state of California. You may assume that each dealership is approximately 2,300 miles away (on average). Some portions of the vehicles are for customer orders (about 30%) and some, (about 70%) are for their lot inventories. The factory has a rail siding on-site to load cars. But no dealer has an on-site rail siding a) Use the information below to compare the cost of both truck and rail transportation options, assuming each vehicle weighs 2 tons. (5 points) b) Then, choose which option(s) is/are best for BMW considering both cost and any other decision factors (i.e., risk, transit time, etc.). A mixed-mode option (combination of both truck/rail) is also possible as is a blend of modes. Explain your answer. (10 points) Factor Truck Rail Cost per Ton Mile $0.45 $0.055 0.8% 0.5% Risk (possible damages) Transit Time 4 days 14 days Load capacity 6 per carrier 12 per rail car Comparing Freight Costs Q Search Sheet Formulas Data Review View 3+ She Insert Cut Page Layout Calibri (Body) 11 Wrap Text General . - A A- AT A Capy Auto Sum Fill Clear 49 Merge & Center - % > Insert Delete Format Format Conditional Format Call Formatting as Table Styles Sort Filter date To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for U B C D E F G H ! K Mode Cost per Ton Mile (US$ Cents) Transit Time (relative) Air 463 1 Truck 45 4 Rail 10,55 14 Ocean 3 21 Pipeline 2 30 Product Weight (US #'s) 500 Short tons (2,000 # equivalents) =D11/2000 Distance Traveled (miles) 2300 Air Truck Rail Ocean Pipeline =(C4/100)*$D$12*013 (C5/100) $D$12*$D$13 =(C6/100)*$D$12*$D$13 =(C7/100)*$D$12*$D$13 =(C8/100)"$D$12*$D$13 Compared to: Air =G14 =G$14/$F16 Truck =H14 =G$14/$F17 $ Rail =114 =G$14/$F18 Ocean =J14 =$14/$F19 Pipeline =K14 =G$14/$F20 =H$14/$F16 =H$ 14/$F17 =H$14/$F18 =H$14/$F19 =H$14/$F20 =1$14/$F16 = $14/$F17 =$14/$F18 =I$14/$F19 1=1$14/$F20 =J$14/$F16 =J$14/$F17 =]$14/$F18 $ =J$14/$F19 = $14/$F20 =K$14/$F16 =K$/$ =K$14/$F17 =K$14/$F18 =K$14/$F19 =K$14/$F20 Comparing Freight Costs Q Search She Insert Page Layout Formulas Data Review View Calculate Now ? A. O. AutoSum Recently Financial Logical Text Date & Lookup & Math & Used Time Reference Trig Define Name Create from Selection Trace Precedents 15 fx ** Trace Dependents Show Error Remove Arrows' Formulas Checking More Functions Calculation Options Calculate Sheet Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. B D E F G G 1 J K L M N 0 P Mode Air Truck Rail Ocean Pipeline Cost per Ton Mile (US $ Cents) Transit Time (relative) 463 1 45 4 0,55 14 3 21 2 30 500 Product Weight (US #'s) Short tons (2,000 # equivalents) Distance Traveled (miles) Air Truck Rail Ocean Pipeline 0,25 2300 $2.662,25 $ 258,75 $ 3,16 $ $ 17,25 $ 11,50 Compared to: Air 2.662,25 Truck $ 258,75 Rail $ 3,16 Ocean $ 17,25 Pipeline $ 11,50 1,000 10,289 841,818 154,333 231,500 0,097 1,000 81,818 15,000 22,500 0,001 0,012 1,000 0,183 0,275 0,006 0,067 5,455 1,000 1,500 0,004 0,044 3,636 0,667 1,000 You are a logistics manager for BMW, and you need to decide which mode(s) of transportation toutilize to transport 400 vehicles (new BMW X5s, each valued at $62,000) from a manufacturing site in Greer, South Carolina to car dealerships across the state of California. You may assume that each dealership is approximately 2,300 miles away (on average). Some portions of the vehicles are for customer orders (about 30%) and some, (about 70%) are for their lot inventories. The factory has a rail siding on-site to load cars. But no dealer has an on-site rail siding a) Use the information below to compare the cost of both truck and rail transportation options, assuming each vehicle weighs 2 tons. (5 points) b) Then, choose which option(s) is/are best for BMW considering both cost and any other decision factors (i.e., risk, transit time, etc.). A mixed-mode option (combination of both truck/rail) is also possible as is a blend of modes. Explain your answer. (10 points) Factor Truck Rail Cost per Ton Mile $0.45 $0.055 0.8% 0.5% Risk (possible damages) Transit Time 4 days 14 days Load capacity 6 per carrier 12 per rail car Comparing Freight Costs Q Search Sheet Formulas Data Review View 3+ She Insert Cut Page Layout Calibri (Body) 11 Wrap Text General . - A A- AT A Capy Auto Sum Fill Clear 49 Merge & Center - % > Insert Delete Format Format Conditional Format Call Formatting as Table Styles Sort Filter date To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for U B C D E F G H ! K Mode Cost per Ton Mile (US$ Cents) Transit Time (relative) Air 463 1 Truck 45 4 Rail 10,55 14 Ocean 3 21 Pipeline 2 30 Product Weight (US #'s) 500 Short tons (2,000 # equivalents) =D11/2000 Distance Traveled (miles) 2300 Air Truck Rail Ocean Pipeline =(C4/100)*$D$12*013 (C5/100) $D$12*$D$13 =(C6/100)*$D$12*$D$13 =(C7/100)*$D$12*$D$13 =(C8/100)"$D$12*$D$13 Compared to: Air =G14 =G$14/$F16 Truck =H14 =G$14/$F17 $ Rail =114 =G$14/$F18 Ocean =J14 =$14/$F19 Pipeline =K14 =G$14/$F20 =H$14/$F16 =H$ 14/$F17 =H$14/$F18 =H$14/$F19 =H$14/$F20 =1$14/$F16 = $14/$F17 =$14/$F18 =I$14/$F19 1=1$14/$F20 =J$14/$F16 =J$14/$F17 =]$14/$F18 $ =J$14/$F19 = $14/$F20 =K$14/$F16 =K$/$ =K$14/$F17 =K$14/$F18 =K$14/$F19 =K$14/$F20 Comparing Freight Costs Q Search She Insert Page Layout Formulas Data Review View Calculate Now ? A. O. AutoSum Recently Financial Logical Text Date & Lookup & Math & Used Time Reference Trig Define Name Create from Selection Trace Precedents 15 fx ** Trace Dependents Show Error Remove Arrows' Formulas Checking More Functions Calculation Options Calculate Sheet Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. B D E F G G 1 J K L M N 0 P Mode Air Truck Rail Ocean Pipeline Cost per Ton Mile (US $ Cents) Transit Time (relative) 463 1 45 4 0,55 14 3 21 2 30 500 Product Weight (US #'s) Short tons (2,000 # equivalents) Distance Traveled (miles) Air Truck Rail Ocean Pipeline 0,25 2300 $2.662,25 $ 258,75 $ 3,16 $ $ 17,25 $ 11,50 Compared to: Air 2.662,25 Truck $ 258,75 Rail $ 3,16 Ocean $ 17,25 Pipeline $ 11,50 1,000 10,289 841,818 154,333 231,500 0,097 1,000 81,818 15,000 22,500 0,001 0,012 1,000 0,183 0,275 0,006 0,067 5,455 1,000 1,500 0,004 0,044 3,636 0,667 1,000