# BUS 474 Assignment 5

```University name: North Carolina State University
BUS 474 Assignment 5
```

Assignment 5: 78 points (plus 15 extra credit points)

If the file formatting is not followed, a penalty of up to 8 points may be assessed for this assignment.

Each section’s points available include points for accurate excel file formulas as well as the correctness and justification for the answers. If the excel file is not correct, and that leads to incorrect conclusions, then the points will only be taken off for the excel file portion. (Alignment of your data and your answers matters!)

File Submissions for this Assignment

1. Excel File for Chapter 5 Exercises:

• Clearly labelled tabs within the file (for each exercise)

2. Word Document or PDF File with Page Break Between each item listed below

• Answers to the Excel Tips Questions
• Answers / Summary of Results for the Chapter 5 Exercises
1. List the Decision Variables, Objective Function, and Constraints with their business definitions and business calculations (use words)

File Formatting Requirements

• Page number should be in the header of each file
• Do NOT include the question text in the Word / PDF file

## Excel Tips Questions (1-7, 3 Points for each question)

Using the sample data below:

1. What are two ways you can calculate the total capacity in the example above?
2. What formula would you use to calculate the average cost per unit of capacity in the example above?
3. What formula would you use to identify which location has the highest cost per unit of capacity?
4. Why would you use the SUMPRODUCT function?
5. What are the steps needed to create a capacity constraint in an optimization model using solver in Excel?
6. What is unmet demand used for in an optimization model using Solver in Excel?  How would you set up to check unmet demand in Solver in Excel? 7. What is a binary constraint used for in Solver in Excel?

Answers to the questions should be included in the Word / pdf File, and should include formula references as appropriate

Chapter 5 Exercise 3 (20 Points)

Sunchem, a manufacturer of printing inks, has five manufacturing plants worldwide. Their locations and capacities are shown in Table 5-6 along with the cost of producing 1 ton of ink at each facility. The production costs are in the local currency of the country where the plant is located. The major markets for the inks are North America, Europe, Japan, South America, and the rest of Asia. Demand at each market is shown in Table 5-6. Transportation costs from each plant to each market in U.S. dollars are shown in Table 5-6. Management must come up with a production plan for the next year.

Table 5-6 Capacity, Demand, Production, and Transportation Costs for Sunchem

 North America Europe Japan South America Rest of Asia Capacity Tons/Year Production Cost/Ton United States \$600 \$1,300 \$2,000 \$1,200 \$1,700 185 \$10,000 Germany \$1,300 \$600 \$1,400 \$1,400 \$1,300 475 15,000 euro Japan \$2,000 \$1,400 \$300 \$2,100 \$900 50 1,800,000 yen Brazil \$1,200 \$1,400 \$2,100 \$800 \$2,100 200 13,000 real India \$2,200 \$1,300 \$1,000 \$2,300 \$800 80 400,000 rupees Demand (tons/year) 270 200 120 190 100

Questions/Requirements:

1. If exchange rates are expected as in Table 5-7, and no plant can run below 50 percent of capacity, how much should each plant produce and which markets should each plant supply?

Table 5-7 Anticipated Exchange Rates for the Next Year

 US\$ Euro Yen Real Rupee US\$ 1 1.993 107.7 1.78 43.55 Euro 0.502 1 54.07 0.89 21.83 Yen 0.0093 0.0185 1 0.016 0.405 Real 0.562 1.124 60.65 1 24.52 Rupee 0.023 0.046 2.47 0.041 1
2. If there are no limits on the amount produced in a plant, how much should each plant produce?
3. Can adding 10 tons of capacity in any plant reduce costs?
4. How should Sunchem account for the fact that exchange rates fluctuate over time?

Separate tabs for each question requiring a new solver should be included in the Excel File Answers to the questions should be included in the Word / pdf File, including what the numbers mean.

Chapter 5 Exercise 4 (20 Points with 10 Point Extra Credit)

Sleekfon and Sturdyfon are two major cell phone manufacturers that have recently merged. Their current market sizes are as shown in Table 5-8. All demand is in millions of units.

Table 5-8 Global Demand and Duties for Sleekfon and Sturdyfon

 Market N.America S.America Europe (EU) Europe (Non-EU) Japan Rest of Asia Africa Sleekfon demand 10 4 20 3 2 2 1 Sturdyfon demand 12 1 4 8 7 3 1 Import duties (%) 3 20 4 15 4 22 25

Sleekfon has three production facilities in Europe (EU), North America, and South America. Sturdyfon also has three production facilities in Europe (EU), North America, and the rest of Asia. The capacity (in millions of units), annual fixed cost (in millions of \$), and variable production costs (\$ per unit) for each plant are as shown in Table 5-9.

Table 5-9 Plant Capacities and Costs for Sleekfon and Sturdyfon

 Capacity Fixed Cost/Year Variable Cost/Unit Sleekfon Europe (EU) 20 100 6 N. America 20 100 5.5 S. America 10 60 5.3 Sturdyfon Europe (EU) 20 100 6 N. America 20 100 5.5 Rest of Asia 10 50 5

Transportation costs between regions are as shown in Table 5-10. All transportation costs are shown in dollars per unit.

Table 5-10 Transportation Costs Between Regions (\$ per Unit)

 N. America S. America Europe (EU) Europe (Non-EU) Japan Rest of Asia Africa N. America 1 1.5 1.5 1.8 1.7 2 2.2 S. America 1.5 1 1.7 2 1.9 2.2 2.2 Europe (EU) 1.5 1.7 1 1.2 1.8 1.7 1.4 Europe (Non-EU) 1.8 2 1.2 1 1.8 1.6 1.5 Japan 1.7 1.9 1.8 1.8 1 1.2 1.9 Rest of Asia 2 2.2 1.7 1.6 1.2 1 1.8 Africa 2.2 2.2 1.4 1.5 1.9 1.8 1

The merged company has estimated that scaling back a 20-million-unit plant to 10 million units saves 30 percent in fixed costs. Variable costs at a scaled-back plant are unaffected. Shutting a plant down (either 10 million or 20 million units) saves 80 percent in fixed costs. Fixed costs are only partially recovered because of severance and other costs associated with a shutdown.

Questions/Requirements (20 Points):

Assuming no duty fees:

1. What is the lowest cost achievable for the production and distribution network prior to the merger? Which plants serve which markets?
2. What is the lowest cost achievable for the production and distribution network after the merger if none of the plants is shut down? Which plants serve which markets?
3. What is the lowest cost achievable for the production and distribution network after the merger if plants can be scaled back or shut down in batches of 10 million units of capacity? Which plants serve which markets?

Extra Credit (15 Points) (No Penalty if not answered)

Duties are applied on each unit based on the fixed cost per unit capacity, variable cost per unit, and transportation cost. Thus, a unit currently shipped from North America to Africa has a fixed cost per unit of capacity of \$5.00 (=100/20 from Table 5-9), a variable production cost of \$5.50, and a transportation cost of \$2.20. The 25 percent import duty is thus applied on \$12.70 ( 5.00 + 5.50 + 2.20 ) to give a total cost on import of \$15.88.

With applying the duty fees from Table 5-8:

• d. What is the lowest cost achievable for the production and distribution network prior to the merger? Which plants serve which markets?
• e. What is the lowest cost achievable for the production and distribution network after the merger if none of the plants is shut down? Which plants serve which markets?
• f. What is the lowest cost achievable for the production and distribution network after the merger if plants can be scaled back or shut down in batches of 10 million units of capacity? Which plants serve which markets?

Separate tabs for each question requiring a new solver should be included in the Excel File Answers to the questions should be included in the Word / pdf File, including what the numbers mean.

Chapter 5 Exercise 8 (20 Points)

Hot&Cold and CaldoFreddo are two European manufacturers of home appliances that have merged. Hot&Cold has plants in France, Germany, and Finland, whereas CaldoFreddo has plants in the United Kingdom and Italy. The European market is divided into four regions: north, east, west, and south. Plant capacities (millions of units per year), annual fixed costs (millions of euros per year), regional demand (millions of units), and variable production and shipping costs (euros per unit) are as shown in Table 513.

Table 5-13 Capacity, Cost, and Demand Data for Hot&Cold and CaldoFreddo

 Variable Production and Shipping Costs North East South West Capacity Annual Fixed Cost Hot&Cold France 100 110 105 100 50 1,000 Germany 95 105 110 105 50 1,000 Finland 90 100 115 110 40 850 Demand 30 20 20 35 CaldoFreddo U.K. 105 120 110 90 50 1,000 Italy 110 105 90 115 60 1,150 Demand 15 20 30 20

Each appliance sells for an average price of 300 euros. All plants are currently treated as profit centers, and the company pays taxes separately for each plant. Tax rates in the various countries are as follows: France, 0.25; Germany, 0.25; Finland, 0.3; UK, 0.2; and Italy, 0.35.

Questions/Requirements:

1. Before the merger, what is the optimal network for each of the two firms if their goal is to minimize costs? What is the optimal network if the goal is to maximize after-tax profits?
2. After the merger, what is the minimum cost configuration if none of the plants is shut down? What is the configuration that maximizes after-tax profits if none of the plants is shut down?
3. After the merger, what is the minimum cost configuration if plants can be shut down (assume that a shutdown saves 100 percent of the annual fixed cost of the plant)? What is the configuration that maximizes after-tax profits?

Separate tabs for each question requiring a new solver should be included in the Excel File Answers to the questions should be included in the Word / pdf File, including what the numbers mean.

### Name Of USA University

• Mercer University
• Middlebury Institute of International Studies at Monterey
• National University
• Navitas at the University of Massachusetts, Lowell
• Navitas at Western Kentucky University
• New Jersey Institute of Technology
• North Seattle College
• Northern Arizona University
• Northern Kentucky University
• Northwest Vista College (Alamo Colleges)
• Old Dominion University
• Pace University
• Pace University Global Pathways
• Palo Alto College (Alamo Colleges)
• Pennsylvania State University (Penn State, PSU)
• Pratt Institute
• Rennert International
• Riverside Community College
• Rivier University
• Robert Morris University
• Roosevelt University, International Assignment Center, via AssignmentGroup
• Sacred Heart University
• SAE USA
• Saginaw Valley State University
• Saint Francis University
• Saint Louis University
• Saint Mary's University of Minnesota
• Saint Michael's College
• San Antonio College (Alamo Colleges)
• San Joaquin Delta College
• Santa Barbara City College
• Savannah College of Art and Design (SCAD)
• Seattle Central College
• South Seattle College
• Southern Arkansas University
• Southern New Hampshire University (SNHU)
• St. Edward's University
• St. John’s University
• St. Phillip's College (Alamo Colleges)
• SUNY College at Old Westbury
• SUNY Fulton-Montgomery Community College
• SUNY Morrisville State University (via Cambridge Education Group)
• SUNY New Paltz
• Texas International Education Consortium
• Texas State University
• Texas A&M University (Texas A&M)
• The College of New Jersey
• Troy University
• Truman State University
• University of Alabama in Huntsville
• University of Arkansas
• University of Bridgeport
• University of California - Los Angeles (UCLA)
• University of California Riverside
• University of Dallas
• University of Dayton
• University of Findlay
• University of Florida (UF)
• University of Hartford
• University of Indianapolis
• University of La Verne
• University of Louisville
• University of Missouri, St. Louis
• University of New Haven
• University of New Mexico
• University of Rhode Island (URI)
• University of San Diego
• University of South Carolina via Shorelight Education
• University of Tampa
• University of Texas at Austin (University of Texas, UT)
• University of Vermont, International Assignment Center, via AssignmentGroup
• Valparaiso University
• Western Michigan University
• Western New England University