We provide the solution to this challenge using:
Need a program? Click here.
You work at a small accounting firm that audits local companies and services. You have been assigned the task of scheduling the hours of your auditors across three different offices to cover six different audits. Each office has different billable rates and areas of expertise, and your goal is to determine the most cost-effective way to arrange the schedule to utilize the staff from each office to cover every audit.
Using the Solver tool in Excel, find the least expensive way to arrange the schedule to bill hours to each client. You are provided with the following to complete your task:
- a table showing the different billable rates of each office
- a matrix template
- the audit hours needed to complete each audit
- and hours available for each office.
Here are the following parameters of your task:
- Minimize Total Audit Costs.
- Input Excel formulas in the green highlighted cells.
- The only cells that should be variable in the Solver tool are cells D14:F19
- You must meet the number of audit hours needed without excess.
- You can’t schedule more hours than you have available.
- You can schedule only whole hours.
Suggestions and Hints
- If you do not have the Solver Add-in in Excel, follow this link for instructions on how to download the tool.
- You should have eight constraints added in the Solver tool.
- You will need to use a SUMPRODUCT function in cells D26:F26 (the audit costs per office).