# 75|EXCEL – Audit Schedule Solver

BYU Student Author: @Jonathan_Weston
Reviewers: @klayton, @Jae
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

• Excel

Overview
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.

Instructions
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.

1. Minimize Total Audit Costs.
2. Input Excel formulas in the green highlighted cells.
3. The only cells that should be variable in the Solver tool are cells D14:F19
4. You must meet the number of audit hours needed without excess.
5. You can’t schedule more hours than you have available.
6. You can schedule only whole hours.

Data Files

Suggestions and Hints
2. You should have eight constraints added in the Solver tool.
3. You will need to use a SUMPRODUCT function in cells D26:F26 (the audit costs per office).

Solution

Here’s my solution. Figured a picture would be fine. I mean it has all the information you need.

10 Likes

One down. Let’s see how many more I can get!

Challenge75_Data.xlsx (11.5 KB)
This was great review!

Thanks for the challenge and tips!

Here is my solution! This was a great refresher!

3 Likes

Here’s my solution:

1 Like

Here is my solution!
Challenge75_Data.xlsx (11.6 KB)

1 Like

Here’s my solution!
Challenge75_Data.xlsx (11.5 KB)

Here is my solution!!

2 Likes

Challenge75_Data.xlsx (13.6 KB)
Solution for Emily Russell

Challenge75_Data_Bayles.xlsx (11.5 KB)
Solution for Maren Bayles

0210_Porter_Challenge75_Data.xlsx (14.3 KB)
Data Analytics Excel Task 2 of 2

This was a great review of how to use the solver function!

Thanks for that great review on solver!
Challenge75_Data_Sydni.xlsx (11.6 KB)

Here is my solution. Thanks for the solver review! It really helped.

1 Like

This was a great refresher for the Information Systems classes I’ve had on Solver already. Here is a screenshot of my Solver inputs and results.

2 Likes

Here is my solution!