Reliable Dog Walkers

Reliable Dog Walkers existing customers and part-time employees data are:

CustomerName Address AccountBalance EmployeeName EmployeeRate WorkingHours

Your Own Name 123 Fifth $45 Patty $15 5

Sam Nichols 205 Norton $20 John $17.25 4

Tom Hart 147 Main $20 Adam $20 3

Carl Mint 358 Pine $0 Patty $15 6

Adam Smith 410 Orange $30 Adam $20 2

Rick Jones 220 Division $40 Patty $15 1.5

Brian White 661 Martin $100 John $17.25 3

John Thomas 211 Martin $50 John $17.25 7

The OverHead Rate is 25%. Place heading and value in spreadsheet to reference in a formula.

Please use Excel to:

1. Create a properly, well-designed worksheet including a properly formatted title and input the properly formatted data. Name the sheet DogWalkers (15 points) DO NOT MAKE YOUR SHEET INTO A TABLE.
2. Add a new column Subtotal, calculate SubTotal as EmployeeRate*WorkingHours*(1+OverHeadRate) (15 points)
3. Add a new column NewAcctBalance, calculate NewAcctBalance as SubTotal+AccountBalance (5 points)
4. Calculate the Total, Average, Highest, Lowest of each: AccountBalance, EmployeeRate, WorkingHours, SubTotal, and NewAcctBalance (10 points)
5. Highlight the lowest NewAcctBalance by conditional formatting using Bold and Red Font (10 points)
6. Add a new column for AdjustedPayRate by using the rule: if the current pay rate is less than $16., then update the rate to $16.50. (15 points)
7. Create another sheet for employees pay raise data: Patty 3.5% John 3% Adam 2%. Name the sheet PayRaise (5 points)
8. In the DogWalkers sheet, add a new column 2021PayRate, calculate 2021PayRate as AdjustedPayRate*(1+PayRaise) (hint: use VLOOKUP to get the pay rate raise data from the sheet PayRaise.) (15 points)
9. Create a pivot table and a pivot chart in another sheet to show the total working hours of each employee. (10 points)