Your case study includes following activities that should be provided in a word document:
1- The problem description (in two pages) is attached below should be downloaded.
2- First you need to formulate the problem as a linear programming model.
3- Solve the formulated model in the Excel solver
4- Report the final optimal solution.
5- Provide sensitivity analysis as well with enough interpret on the result.
6- Follow APA standard in writing your report and use Grammarly to check your writing quality.
At its final meeting of the fiscal year, the Salem City Council will be making plans to allocate funds remaining in this year’s budget. Nine projects have been under consideration throughout the entire year. To gauge community support for the various projects, questionnaires were randomly mailed to voters throughout the city asking them to rank the projects (9 = highest priority, 1 = lowest priority). The council tallied the scores from the 500 usable responses it received. Although the council has repeatedly maintained that it will not be bound by the results of the questionnaire, it plans to use this information while taking into account other concerns when making the budget allocations. The estimated cost of each project, the estimated number of permanent new jobs each would create, and the questionnaire point tallies are summarized in Table 3.10. The council’s goal is to maximize the total perceived voter support (as evidenced through the questionnaires), given other constraints and concerns of the council, including the following:
• $900,000 remains in the budget. • The council wants to create at least 10 new jobs. • Although crime deterrence is a high priority with the public, the council feels that it must also be fair to other sectors of public service (fire and education). Accordingly, it wishes to fund at most three of the police-related projects. • The council would like to increase the number of city emergency vehicles but feels that, in the face of other pressing issues, only one of the two emergency vehicle projects should be funded at this time. Thus, either the two police cars or the fire truck should be purchased.
The council believes that if it decides to restore funds cut from the sports programs at the schools, it should also restore funds cut from their music programs, and vice versa. By union contract, any additional school funding must go toward restoring previous cuts before any new school projects are undertaken. Consequently, both sports funds and music funds must be restored before new computer equipment can be purchased. Restoring sports and music funds, however, does not imply that new computers will be purchased, only that they can be.
Table 3.10 Project Costs, New Jobs, and Point Tallies
| Project | Costs ($1000) | New Jobs | Points |
| Hire seven new police officers | $400 | 7 | 4176 |
| Modernize police headquarters | $350 | 0 | 1774 |
| Buy two new police cars | $50 | 1 | 2513 |
| Give bonuses to foot patrol officers | $100 | 0 | 1928 |
| Buy new fire truck/support equipment | $500 | 2 | 3608 |
| Hire assistant fire chief | $90 | 1 | 962 |
| Restore cuts to sports programs | $220 | 8 | 2829 |
| Restore cuts to school music | $150 | 3 | 1708 |
| Buy new computers for high school | $140 | 2 | 3003 |