-Perform certain calculations discussed in the assigned reading in order to derive an answer for each problem.

• -Perform certain calculations discussed in the assigned reading in order to derive an answer for each problem.
• -Use Microsoft Excel to complete each problem and submit a single Excel spreadsheet that contains a separate worksheet (i.e., separate tab) for each problem.
• -Have each worksheet be clearly labeled to identify the associated problem.
• -To show all calculations or other work performed to derive your answer(s) for each problem.
• -Have your spreadsheet be fully functional (i.e., configured to allow the reader to not only see all numerical values, but also be able to see the underlying formula associated with each calculated value).
• -To label your work in each worksheet to clearly identify the nature of each piece of data or calculated value.
• -No credit will be granted for problems that are not completed using Excel, for which your Excel worksheet is not fully functional, or for which you have not shown all of the calculations or other work performed to derive your answer(s). 

Week 6 Homework Assignment

A start-up woodworking company will be manufacturing and selling dining room tables and chairs.  The owner assumes that his prospective customers are interested in buying tables and chairs individually rather than having to buy them in pre-defined sets, as is the case with most furniture manufacturers.  The following considerations apply to the company’s first month of production:

>The company’s objective is to maximize profit during each monthly production cycle.

>The company will earn a potential profit of $300 for each table sold and $55 for each chair sold.

>All tables and chairs manufactured during a given production period will be sold during the same production period.

>Fabrication of each table requires 8 units of wood and each chair requires 2.5 unit of wood. 

>Fabrication of each table requires 25 units of fabrication labor and each chair requires 12 units of fabrication labor. 

>Fabrication of each table requires 8 units of assembly labor and each chair requires 5 units of assembly labor. 

>Fabrication of each table requires 10.5 units of finishing labor and each chair requires 8 units of finishing labor. 

>Fabrication of each table requires 3 unit of packaging labor and each chair requires 1.5 unit of packaging labor. 

>Producing partially manufactured (i.e., partially fabricated, assembled, finished and/or packaged) tables and/or chairs is acceptable during any given production period since they can be completed during the subsequent production cycle. 

>For the first month of production, the owner anticipates having 1,000 units of wood, 3,000 units of fabrication labor, 2,000 units of assembly labor, 2,000 units of finishing labor and 500 units of packaging labor available. 

1. The company’s president has asked you to develop a linear programming model using the Excel Solver method in order to answer the following questions as part of the planning for the first month of production: 

a) What is the optimal number of tables the company should produce during the upcoming production cycle?

b) What is the optimal number of chairs the company should produce during the upcoming production cycle?

c) What is the total amount of profit that the company would earn for producing the optimal number of complete tables and chairs during the upcoming production cycle (partially manufactured tables and/or chairs do not contribute to profit earned since they cannot be sold during the upcoming production cycle)?

d) Which resources will be fully used in producing the optimal number of tables and chairs during the upcoming production cycle?

2. After reviewing your recommendations based upon your original model, the president has asked you to revise your original model to include the following additional criteria:

> A minimum of 50 tables shall be produced.

> A minimum of 100 chairs shall be produced.

Use your revised model and Excel Solver to answer the following questions as part of the revised planning for the first month of production: 

a) What is the optimal number of tables the company should produce during the upcoming production cycle?

b) What is the optimal number of chairs the company should produce during the upcoming production cycle?

c) What is the total amount of profit that the company would earn for producing the optimal number of complete tables and chairs during the upcoming production cycle (partially manufactured tables and/or chairs do not contribute to profit earned since they cannot be sold during the upcoming production cycle)?

d) Which resources will be fully used in producing the optimal number of tables and chairs during the upcoming production cycle?

3. While the owner of the company was satisfied with your revised model, he decided to survey prospective customers prior to commencing production and discovered that the vast majority of his prospective customers are only interested in purchasing table and chair sets, with each set consisting of one table and four chairs.  Consequently, he has requested that you revise your model to delete the requirement to produce a minimum of 50 tables and a minimum of 100 chairs, and instead include a requirement that exactly four chairs be produced for every table produced.  Use your revised model and Excel Solver to answer the following questions as part of the further revised planning for the first month of production: 

a) What is the optimal number of tables the company should produce during the upcoming production cycle?

b) What is the optimal number of chairs the company should produce during the upcoming production cycle?

c) What is the total amount of profit that the company would earn for producing the optimal number of complete tables and chair sets during the upcoming production cycle (i.e., partially manufactured tables and chairs, as well as partial table and chair sets do not contribute to profit earned since they cannot be sold during the upcoming production cycle)?

e) Which resources will be fully used in producing the optimal number of tables and chairs during the upcoming production cycle?

4. During the first week of the initial production cycle a large number of the company’s employees have been stricken with the flu.  The owner of the company has estimated that he will now only have 2,000 units of fabrication labor, 1,500 units of assembly labor, 1,500 units of finishing labor and 300 units of packaging labor available for the first month of production.  Revise your model from problem 3 to reflect the revised resource quantities  Use your revised model and Excel Solver to answer the following questions as part of the further revised planning for the first month of production: 

a) What is the optimal number of tables the company should produce during the upcoming production cycle?

b) What is the optimal number of chairs the company should produce during the upcoming production cycle?

c) What is the total amount of profit that the company would earn for producing the optimal number of complete tables and chair sets during the upcoming production cycle (i.e., partially manufactured tables and chairs, and/or partial table and chair sets do not contribute to profit earned since they cannot be sold during the upcoming production cycle)?

d) Which resources will be fully used in producing the optimal number of tables and chairs during the upcoming production cycle?

Hints for the preceding problems:

> Do not forget to include a non-negativity constraint in your models (i.e., you cannot produce a negative number of tables or chairs).

> A given resource is fully consumed only if all of the resource has been used (i.e., if even a fraction of the resource remains unused, then the resource has not been fully consumed).

> You do not need to create a separate variable to represent a table and chair set for problem 3 and 4.  Instead, create a simple algebraic equation using only the two variables that represent the number of tables to be produced and the number of chairs to be produced that describes the exact ratio in which tables and chairs must be manufactured to produce complete sets.

> Do not forget to account for the constraint requiring that you produce a specific minimum number of table and chair sets in problems 3 and 4.

> Do not overlook the stipulation that only complete tables and/or chairs contribute to earned profit in problems 1 and 2.

> Do not overlook the stipulations that only complete table and chairs sets contribute to earned profit in problems 3 and 4.