Reliable Dog Walkers Reliable Dog Walkers existing customers and part-time employees data are: CustomerName Address AccountBalance EmployeeName
Fantastic news! We've Found the answer you've been seeking!
Question:
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:
- 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.
- Add a new column Subtotal, calculate SubTotal as EmployeeRate*WorkingHours*(1+OverHeadRate) (15 points)
- Add a new column NewAcctBalance, calculate NewAcctBalance as SubTotal+AccountBalance (5 points)
- Calculate the Total, Average, Highest, Lowest of each: AccountBalance, EmployeeRate, WorkingHours, SubTotal, and NewAcctBalance (10 points)
- Highlight the lowest NewAcctBalance by conditional formatting using Bold and Red Font (10 points)
- 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)
- Create another sheet for employees pay raise data: Patty 3.5% John 3% Adam 2%. Name the sheet PayRaise (5 points)
- 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)
- Create a pivot table and a pivot chart in another sheet to show the total working hours of each employee. (10 points)
Related Book For
Posted Date: