Assignment Prompt & Instructions
**files (4)**
– ## OptimizationGroupProjectInstructionsW126.doc
– ## Groupprojectinstructions.docx
– ## CurrentAffairsDataGP.xlsx
– ## CurrentAffairsLightingCompanyCaseGP.doc
**## OptimizationGroupProjectInstructionsW126.doc**
[removed]
**## Groupprojectinstructions.docx**
Please submit ONE EXCEL file AND ONE WORD file.
Prepare a
**business memo** to report and discuss your findings on this case and answer the case questions.
Follow proper formatting including APA style to prepare the report and include NSU Cover Sheet at the beginning of the report.
The (volunteer) Team Leaders will coordinate the work done by other team members and facilitate communications with the team, and serve as team liaison with the instructor. The team lead should upload the final report and Excel file with the complete solutions.
For full credit, submit the following.
1. Formatted business report (see #2 Part C)
2. One Excel file coded with formulas (no hard-coded numbers) containing the following 2 (or 3) models
a. Initial optimization model (#1 Part A)
b. Outsourcing model (#1 Part B)
c. Advertising tradeoff (optional)
**## CurrentAffairsDataGP.xlsx**
## Initial
| | | | | | | | | |
| — | — | — | — | — | — | — | — | — |
| Costs and prices: | r = regular time; o = overtime |
| | Table lamps (T) | | Floor lamps (F) | | Ceiling lamps (C ) | | Pendant lamps (P) |
| | Tr | To | Fr | Fo | Cr | Co | Pr | Po |
| Sales price | 128 | 128 | 150 | 150 | 105 | 105 | 160 | 160 |
| Material costs | 57 | 57 | 95 | 95 | 60 | 60 | 80 | 80 |
| Production costs | 16 | 18 | 16 | 18 | 12 | 15 | 12 | 15 |
| Unit profit |
| | Department 1 | | | | Department 2 |
| Decision Variables: |
| | Tr | To | Fr | Fo | Cr | Co | Pr | Po |
| Units produced |
| | T | F | C | P |
| Advertising |
| Objective function: |
| max (Profit) = |
| Constraints: |
| Capacity constraints: | LHS | RHS |
| Department 1 regular time | 0 | 100000 |
| Department 1 overtime | | 25000 |
| Department 2 regular time | | 90000 |
| Department 2 overtime | | 24000 |
| Demand constraints: |
| Table lamps | | 60000 |
| Floor lamps | | 20000 |
| Ceiling lamps | | 100000 |
| Pendant lamps | | 35000 |
| Advertising constraint | 0 | 18000 |
**## CurrentAffairsLightingCompanyCaseGP.doc**
**Current Affairs Lighting Company**
The Current Affairs Lighting Company manufactures four types of lamps at its factory including table lamps, floor lamps, ceiling lamps, and pendant lamps. Table 1 presents the average material costs for each of the products. Each product is made in one of two production processes by purchasing components, assembling and testing the product, and finally packaging it for shipping. Table lamps and floor lamps go through the assembly and finishing process in Department 1, while ceiling fixtures and pendant lamps go through the process in Department 2. Variable production costs and capacities are shown in Table 2. The capacities are measured in units of product. Note that there are regular and overtime possibilities for each department.
Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices (see Table 3). Sales levels can also be affected by advertising expenditures. Starting with the demand levels in the table, in advertising raises the demand by the percentage shown in the last row times the amount spent on advertising. For example, an increase in advertising of $5,000 for table lamps would raise demand by 600 units. However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products.
Table 1
| | | | | |
| — | — | — | — | — |
| Product | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Material cost | $57 | $95 | $60 | $80 |
Table 2
| | | | | |
| — | — | — | — | — |
| **Process** | **Regular Time** | **Overtime** |
| | **Unit Cost** | **Capacity** | **Unit Cost** | **Capacity** |
| Department 1 | $16 | 100,000 | $18 | 25,000 |
| Department 2 | $12 | 90,000 | $15 | 24,000 |
Table 3
| | | | | |
| — | — | — | — | — |
| | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Selling price | $128 | $150 | $105 | $160 |
| Potential sales (000) | 60 | 20 | 100 | 35 |
| Advertising effect | 12.5% | 10.5% | 9% | 15.5% |
This problem was taken from Powell, S.G. and Baker, K.R. (2007). Management Science: The Art of Modeling with Spreadsheets, 2nd ed., Hoboken, NJ: John Wiley & Sons, Inc., p. 280-281
0
**## CurrentAffairsLightingCompanyCaseGP.doc**
**Current Affairs Lighting Company**
The Current Affairs Lighting Company manufactures four types of lamps at its factory including table lamps, floor lamps, ceiling lamps, and pendant lamps. Table 1 presents the average material costs for each of the products. Each product is made in one of two production processes by purchasing components, assembling and testing the product, and finally packaging it for shipping. Table lamps and floor lamps go through the assembly and finishing process in Department 1, while ceiling fixtures and pendant lamps go through the process in Department 2. Variable production costs and capacities are shown in Table 2. The capacities are measured in units of product. Note that there are regular and overtime possibilities for each department.
Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices (see Table 3). Sales levels can also be affected by advertising expenditures. Starting with the demand levels in the table, in advertising raises the demand by the percentage shown in the last row times the amount spent on advertising. For example, an increase in advertising of $5,000 for table lamps would raise demand by 600 units. However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products.
Table 1
| | | | | |
| — | — | — | — | — |
| Product | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Material cost | $57 | $95 | $60 | $80 |
Table 2
| | | | | |
| — | — | — | — | — |
| **Process** | **Regular Time** | **Overtime** |
| | **Unit Cost** | **Capacity** | **Unit Cost** | **Capacity** |
| Department 1 | $16 | 100,000 | $18 | 25,000 |
| Department 2 | $12 | 90,000 | $15 | 24,000 |
Table 3
| | | | | |
| — | — | — | — | — |
| | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Selling price | $128 | $150 | $105 | $160 |
| Potential sales (000) | 60 | 20 | 100 | 35 |
| Advertising effect | 12.5% | 10.5% | 9% | 15.5% |
This problem was taken from Powell, S.G. and Baker, K.R. (2007). Management Science: The Art of Modeling with Spreadsheets, 2nd ed., Hoboken, NJ: John Wiley & Sons, Inc., p. 280-281
0
**## OptimizationGroupProjectInstructionsW126.doc**
[removed]
**## Groupprojectinstructions.docx**
Please submit ONE EXCEL file AND ONE WORD file.
Prepare a
**business memo** to report and discuss your findings on this case and answer the case questions.
Follow proper formatting including APA style to prepare the report and include NSU Cover Sheet at the beginning of the report.
The (volunteer) Team Leaders will coordinate the work done by other team members and facilitate communications with the team, and serve as team liaison with the instructor. The team lead should upload the final report and Excel file with the complete solutions.
For full credit, submit the following.
1. Formatted business report (see #2 Part C)
2. One Excel file coded with formulas (no hard-coded numbers) containing the following 2 (or 3) models
a. Initial optimization model (#1 Part A)
b. Outsourcing model (#1 Part B)
c. Advertising tradeoff (optional)
**## CurrentAffairsDataGP.xlsx**
## Initial
| | | | | | | | | |
| — | — | — | — | — | — | — | — | — |
| Costs and prices: | r = regular time; o = overtime |
| | Table lamps (T) | | Floor lamps (F) | | Ceiling lamps (C ) | | Pendant lamps (P) |
| | Tr | To | Fr | Fo | Cr | Co | Pr | Po |
| Sales price | 128 | 128 | 150 | 150 | 105 | 105 | 160 | 160 |
| Material costs | 57 | 57 | 95 | 95 | 60 | 60 | 80 | 80 |
| Production costs | 16 | 18 | 16 | 18 | 12 | 15 | 12 | 15 |
| Unit profit |
| | Department 1 | | | | Department 2 |
| Decision Variables: |
| | Tr | To | Fr | Fo | Cr | Co | Pr | Po |
| Units produced |
| | T | F | C | P |
| Advertising |
| Objective function: |
| max (Profit) = |
| Constraints: |
| Capacity constraints: | LHS | RHS |
| Department 1 regular time | 0 | 100000 |
| Department 1 overtime | | 25000 |
| Department 2 regular time | | 90000 |
| Department 2 overtime | | 24000 |
| Demand constraints: |
| Table lamps | | 60000 |
| Floor lamps | | 20000 |
| Ceiling lamps | | 100000 |
| Pendant lamps | | 35000 |
| Advertising constraint | 0 | 18000 |
**## CurrentAffairsLightingCompanyCaseGP.doc**
**Current Affairs Lighting Company**
The Current Affairs Lighting Company manufactures four types of lamps at its factory including table lamps, floor lamps, ceiling lamps, and pendant lamps. Table 1 presents the average material costs for each of the products. Each product is made in one of two production processes by purchasing components, assembling and testing the product, and finally packaging it for shipping. Table lamps and floor lamps go through the assembly and finishing process in Department 1, while ceiling fixtures and pendant lamps go through the process in Department 2. Variable production costs and capacities are shown in Table 2. The capacities are measured in units of product. Note that there are regular and overtime possibilities for each department.
Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices (see Table 3). Sales levels can also be affected by advertising expenditures. Starting with the demand levels in the table, in advertising raises the demand by the percentage shown in the last row times the amount spent on advertising. For example, an increase in advertising of $5,000 for table lamps would raise demand by 600 units. However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products.
Table 1
| | | | | |
| — | — | — | — | — |
| Product | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Material cost | $57 | $95 | $60 | $80 |
Table 2
| | | | | |
| — | — | — | — | — |
| **Process** | **Regular Time** | **Overtime** |
| | **Unit Cost** | **Capacity** | **Unit Cost** | **Capacity** |
| Department 1 | $16 | 100,000 | $18 | 25,000 |
| Department 2 | $12 | 90,000 | $15 | 24,000 |
Table 3
| | | | | |
| — | — | — | — | — |
| | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Selling price | $128 | $150 | $105 | $160 |
| Potential sales (000) | 60 | 20 | 100 | 35 |
| Advertising effect | 12.5% | 10.5% | 9% | 15.5% |
This problem was taken from Powell, S.G. and Baker, K.R. (2007). Management Science: The Art of Modeling with Spreadsheets, 2nd ed., Hoboken, NJ: John Wiley & Sons, Inc., p. 280-281
0
**## OptimizationGroupProjectInstructionsW126.doc**
[removed]
**## Groupprojectinstructions.docx**
Please submit ONE EXCEL file AND ONE WORD file.
Prepare a
**business memo** to report and discuss your findings on this case and answer the case questions.
Follow proper formatting including APA style to prepare the report and include NSU Cover Sheet at the beginning of the report.
The (volunteer) Team Leaders will coordinate the work done by other team members and facilitate communications with the team, and serve as team liaison with the instructor. The team lead should upload the final report and Excel file with the complete solutions.
For full credit, submit the following.
1. Formatted business report (see #2 Part C)
2. One Excel file coded with formulas (no hard-coded numbers) containing the following 2 (or 3) models
a. Initial optimization model (#1 Part A)
b. Outsourcing model (#1 Part B)
c. Advertising tradeoff (optional)
**## CurrentAffairsDataGP.xlsx**
## Initial
| | | | | | | | | |
| — | — | — | — | — | — | — | — | — |
| Costs and prices: | r = regular time; o = overtime |
| | Table lamps (T) | | Floor lamps (F) | | Ceiling lamps (C ) | | Pendant lamps (P) |
| | Tr | To | Fr | Fo | Cr | Co | Pr | Po |
| Sales price | 128 | 128 | 150 | 150 | 105 | 105 | 160 | 160 |
| Material costs | 57 | 57 | 95 | 95 | 60 | 60 | 80 | 80 |
| Production costs | 16 | 18 | 16 | 18 | 12 | 15 | 12 | 15 |
| Unit profit |
| | Department 1 | | | | Department 2 |
| Decision Variables: |
| | Tr | To | Fr | Fo | Cr | Co | Pr | Po |
| Units produced |
| | T | F | C | P |
| Advertising |
| Objective function: |
| max (Profit) = |
| Constraints: |
| Capacity constraints: | LHS | RHS |
| Department 1 regular time | 0 | 100000 |
| Department 1 overtime | | 25000 |
| Department 2 regular time | | 90000 |
| Department 2 overtime | | 24000 |
| Demand constraints: |
| Table lamps | | 60000 |
| Floor lamps | | 20000 |
| Ceiling lamps | | 100000 |
| Pendant lamps | | 35000 |
| Advertising constraint | 0 | 18000 |
**## CurrentAffairsLightingCompanyCaseGP.doc**
**Current Affairs Lighting Company**
The Current Affairs Lighting Company manufactures four types of lamps at its factory including table lamps, floor lamps, ceiling lamps, and pendant lamps. Table 1 presents the average material costs for each of the products. Each product is made in one of two production processes by purchasing components, assembling and testing the product, and finally packaging it for shipping. Table lamps and floor lamps go through the assembly and finishing process in Department 1, while ceiling fixtures and pendant lamps go through the process in Department 2. Variable production costs and capacities are shown in Table 2. The capacities are measured in units of product. Note that there are regular and overtime possibilities for each department.
Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices (see Table 3). Sales levels can also be affected by advertising expenditures. Starting with the demand levels in the table, in advertising raises the demand by the percentage shown in the last row times the amount spent on advertising. For example, an increase in advertising of $5,000 for table lamps would raise demand by 600 units. However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products.
Table 1
| | | | | |
| — | — | — | — | — |
| Product | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Material cost | $57 | $95 | $60 | $80 |
Table 2
| | | | | |
| — | — | — | — | — |
| **Process** | **Regular Time** | **Overtime** |
| | **Unit Cost** | **Capacity** | **Unit Cost** | **Capacity** |
| Department 1 | $16 | 100,000 | $18 | 25,000 |
| Department 2 | $12 | 90,000 | $15 | 24,000 |
Table 3
| | | | | |
| — | — | — | — | — |
| | **Table** | **Floor** | **Ceiling** | **Pendant** |
| Selling price | $128 | $150 | $105 | $160 |
| Potential sales (000) | 60 | 20 | 100 | 35 |
| Advertising effect | 12.5% | 10.5% | 9% | 15.5% |
This problem was taken from Powell, S.G. and Baker, K.R. (2007). Management Science: The Art of Modeling with Spreadsheets, 2nd ed., Hoboken, NJ: John Wiley & Sons, Inc., p. 280-281
0