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)
  2. Answers to the questions

File Formatting Requirements

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

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

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

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

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)

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

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