75|EXCEL – Audit Schedule Solver

[Jonathan_Weston]

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

We provide the solution to this challenge using:

• Excel

Need a program? Click here.

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.

Here are the following parameters of your task:

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

• Challenge75_Data.xlsx

Suggestions and Hints

1. If you do not have the Solver Add-in in Excel, follow this link for instructions on how to download the tool.
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

Challenge75_Solution.xlsx
Solution Video: Challenge 75|EXCEL – Audit Schedule Solver

[Dallin_Hwang]

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

[Richard_Robertson]

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

[Tyler_Hughes]

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

[Dakin_Rust]

Thanks for the challenge and tips!

[Kelty_Dagley]

Here is my solution! This was a great refresher!

[jdwilliams4]

Here’s my solution:

[Ethan_Hunt]

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

[Maddy_Bunn]

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

[Lauren_Garner]

[Adam_Hildt]

Here is my solution!!

[Emily_Russell]

Challenge75_Data.xlsx (13.6 KB)
Solution for Emily Russell

[baylesma]

Challenge75_Data_Bayles.xlsx (11.5 KB)
Solution for Maren Bayles

[Brad_Jones]

Challenge75_Data_BradJones.xlsx (17.9 KB)

[Sanford_Porter]

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

[Keilani_Hess]

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

[Sydni_Hales]

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

[Chase_Halverson]

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

[Morgan_M_Hansen]

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.

[Madeline_Westover]

Here is my solution!