Based on the lecture “Aggregate Planning,” work on the problem below on the Excel template provided. When you are finished, you should ONLY upload a single Excel file that contain only one workbook of...

Based on the lecture “Aggregate Planning,” work on the problem below on the Excel template provided. When you are finished, you should ONLY upload a single Excel file that contain only one workbook of your answer. Once you submit your answer, you should see the solution Excel file.

To process income tax forms, the Internal Revenue Service (IRS) first sends each form through the data preparation (DP) department, where information is coded for computer entry. Then the form is sent to data entry (DE), where it is entered into the computer. During the next 3 weeks, the following numbers of forms will arrive: week 1, 40,000; week 2, 30,000; week 3, 60,000. All employees work 40 hours per week and are paid $500 per week.

Data preparation of a form requires 15 minutes, and data entry of a form requires 10 minutes. Each week an employee is assigned to either data entry or data preparation. The IRS must complete processing all forms by the end of week 5 and wants to minimize the cost of accomplishing this goal. Assume all employees are capable of performing data preparation or data entry, but must be assigned to one task for an entire week at a time.

Question:

1. Formulate an LP model for the staffing problem. Use the spreadsheet provided to execute the model in Excel.


2. The solution obtained in part a) may suggest imbalanced workload over time. That is, a lot of forms are done in some week, while many fewer are done in others. Such a schedule is not practically appealing. To make sure a more balanced load over time, we impose a constraint that the forms processed at each step in each month should not exceed 30% of the total number of forms to be processed. Modify the model (in a separate workbook) to find the new schedule.