MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

  1. The following table gives the distances between Boston, Chicago, Dallas, Los Angeles, and Miami. Each city requires 40,000 kilowatt hours (kWh) of power, and Chicago, Dallas, and Miami are capable of producing 70,000 kWh. Assume that shipping 1000 kWh over 100 miles costs $4. From where should power be sent to minimize the cost of meeting each city’s demand?

    Open table as spreadsheet
     

    Boston

    Chicago

    Dallas

    Los Angeles

    Miami

    Chicago

    983

    0

    1205

    2112

    1390

    Dallas

    1815

    1205

    0

    801

    1332

    Miami

    1539

    1390

    1332

    2757

    0

  2. We produce and sell drugs at several different locations. The decision of where to produce goods for each sales location can have a huge impact on profitability. Our model is similar to the model used in this chapter to determine where drugs should be produced. We’re using the following assumptions:

    We produce drugs at six locations and sell to customers in six different areas.

    • Tax rate and variable production cost depend on the location where the drug is produced. For example, any units produced at Location 3 cost $6 per unit to produce; profits from these goods are taxed at 20 percent.

    • The sales price of each drug depends on where the drug is sold. For example, each product sold in Location 2 is sold for $40.

      Open table as spreadsheet

      Production location

      1

      2

      3

      4

      5

      6

      Sales price

      $45

      $40

      $38

      $36

      $39

      $34

      Tax rate

      29%

      40%

      20%

      40%

      35%

      18%

      Variable production cost

      $8

      $7

      $6

      $9

      $7

      $7

    • Each of our six plants can produce up to 6 million units per year.

    • The annual demand (in millions) for our product in each location is as follows:

      Open table as spreadsheet

      Sales location

      1

      2

      3

      4

      5

      6

      Demand

      1

      2

      3

      4

      5

      6

    • The unit shipping cost depends on the plant where the product is produced and the location where the product is sold.

      Open table as spreadsheet
       

      Sold 1

      Sold 2

      Sold 3

      Sold 4

      Sold 5

      Sold 6

      Plant 1

      $3

      $4

      $5

      $6

      $7

      $8

      Plant 2

      $5

      $2

      $6

      $9

      $10

      $11

      Plant 3

      $4

      $3

      $1

      $6

      $8

      $6

      Plant 4

      $5

      $5

      $7

      $2

      $5

      $5

      Plant 5

      $6

      $9

      $6

      $5

      $3

      $7

      Plant 6

      $7

      $7

      $8

      $9

      $10

      $4

      For example, if we produce a unit at Plant 1 and sell it in Location 3, it costs $5 to ship it.

  1. How can we maximize after-tax profit with our limited production capacity?

    Suppose that each day, northern, central, and southern California each use 100 billion gallons of water. Also assume that northern California and central California have available 120 billion gallons of water, whereas southern California has 40 billion gallons of water available. The cost of shipping one billion gallons of water between the three regions is as follows:

    Open table as spreadsheet
     

    Northern

    Central

    Southern

    Northern

    $5,000

    $7,000

    $10,000

    Central

    $7,000

    $5,000

    $6,000

    Southern

    $10,0000

    $6,000

    $5,000

    We will not be able to meet all demand for water, so we assume that each billion gallons of unmet demand incurs the following shortage costs:

    Open table as spreadsheet
     

    Northern

    Central

    Southern

    Shortage cost/billion gallons short

    $6,000

    $5,500

    $9,000

    How should California’s water be distributed to minimize the sum of shipping and shortage costs?

Related

Категории