Zilholm is a global consulting company, specializing in accounting services. It provides a range of services, ranging from tax consulting to auditing. The company offers 5 types of services called premium service packages. The deliverables and expectations of a premium service package are standard and do not vary from one client to the next. Each premium service package is delivered with the input of employees in four expertise areas: E1, E2, E3, and E4. However, each package has different labor requirements in these expertise areas. Table-Q4-1 below shows the labor hour requirements per package. For instance, when Zilholm signs a contract with a client to provide premium service package#1, then Zilholms employees with expertise E1, E2, E3, and E4, have to dedicate 5, 25, 20, and 10 hours, respectively, of their time to this client. The profit Zilholm makes based on each service package sold is also given in Table-Q4-1.

Premium service packages	Labor-Hour Requirements by Expertise (hours/unit)				Unit Profit ($ per package sold)
	E1	E2	E3	E4
Package#1	5	25	20	10	$1,000
Package#2	15	20	40	10	$1,500
Package#3	20	25	25	15	$1,300
Package#4	15	30	30	10	$1,200
Package#5	10	15	35	10	$900

Table-Q4.1. Labor hour requirements and unit profits of service packages.

The total labor hours available per month in each expertise area is given in Table-Q4-2.

Expertise	Labor hours available per month
E1	2000
E2	4000
E3	4500
E4	1600

Table-Q4.2. Monthly capacity

Zilholm has an ongoing business relationship with a Fortune-100 company. This client has signed multiple contracts with Zilholm. The number of service packages that Zilholm has to complete for the Fortune-100 company per month is given in Table-Q4-3. However, the Fortune-100 company is not the only client that Zilholm works with. Zilholms sales force is very successful in identifying new clients and Zilholm can sell more service packages in a given month in order to maximize the total profit.

Premium service package	Number of premium service packages to be completed for the Fortune-100 client
Package#1	20
Package#2	25
Package#3	30
Package#4	10
Package#5	20

Table-Q4.3. Monthly requirements to meet the contract obligations for the Fortune-100 company

You are asked to determine the optimal product mix (i.e., the monthly number of service packages to be provided) that maximizes Zilholms profits. You will formulate a linear programming model to determine the optimal solution using Excel Solver.

NOTE: SHOW ALL YOUR WORK.

1. (3 points) What are the decision variables? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem.

2. (2 points) What is the objective function? Clearly explain the objective function in English and provide the mathematical formulation.

3. (6 points) What are the constraints of the problem? Clearly explain each constraint in English and provide the mathematical formulation.

4. (1 points) Are there any sign or type restrictions in your formulation? Why or why not? Clearly explain the sign and type restrictions (if any).

5. Prepare a spreadsheet to determine the optimal production plan using Excel Solver.

1. (2 points) Provide a screenshot of your spreadsheet model. The screenshot should show all the cells involved in formulating the optimization problem. NOTE: Do not copy and paste your spreadsheet to your answer report as an Excel object. Instead, capture a screenshot in Excel and paste this as an image.

2. (2 points) Provide a screenshot of your spreadsheet with the formula view. You can switch from normal view to formula view in Excel by clicking the CTRL and ~ keys on your keyboard simultaneously.

3. (2 points) Prepare the Solver model by logging all the information to the Solver Parameters window in Excel. Provide a screenshot of the Solver Parameters window in Excel.

4. (2 points) Finally, solve the optimization problem using the Simplex LP method in Excel Solver. What is the optimal solution, i.e., the optimal values of the objective function and the decision variables?

6. (2 points) Zilholm is considering improvements in its service process. Investing in new accounting software and technology will cut the labor hour requirements for each project by 10%. (That is, each service package will be completed using fewer labor hours.) What will be the new optimal product mix and the optimal monthly profit if the company invests in software and technology? Will the optimal solution be different than the one in part e? Explain clearly why or why not.

7. (2 points) If Zilholm did not have the contracts and obligations for the Fortune-100 company and could sell any number of service packages, what would be the optimal product mix and the optimal profit? Would the optimal profit be lower or higher than the one in part (e)? Is the Fortune-100 companys ongoing business helping or hurting Zilholms operations? Explain clearly why. NOTE: Answer this question independent of the technology changes mentioned in part f.

ADDITIONAL DATA

Stores located in TX 33 193 1202 986 Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina Distance from/to (in miles) Distribution center located in GA NC 1109 1286 261 638 1918 1981 495 105 619 833 111 326 454 450 685 747 2546 2551 288 153 1216 361 973 715 397 1533 1020 Retail stores States No. of stores Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina Distribution center located in NC GA TX Distribution center capacity Monthly truckload capacity 700 500 550 Stores located in TX 33 193 1202 986 Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina Distance from/to (in miles) Distribution center located in GA NC 1109 1286 261 638 1918 1981 495 105 619 833 111 326 454 450 685 747 2546 2551 288 153 1216 361 973 715 397 1533 1020 Retail stores States No. of stores Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina Distribution center located in NC GA TX Distribution center capacity Monthly truckload capacity 700 500 550