13. Switch back to the All Services worksheet. Use the Scenario Manager as follows to compare the profit per hour in each scenario: a. Create a Scenario PivotTable report for result cells C17:E17. b. Remove the Filter field from the PivotTable. c. Change the number format of the Profit_Per_Hour_Training, Profit_Per_Hour_Consulting, and Profit_Per_Hour_Corporate fields (located in the Values box of the PivotTable Field List) to Currency with 2 decimal places and $ as the symbol. d. Use Personal Training as the row label value in cell B3, Consulting as the value in cell C3, and Corporate Programs as the value in cell D3. e. In cell A1, use Profit Per Hour as the report title. f. Format the report title using the Title cell style. g. Resize columns A–D to 17.00. 14. Add a PivotChart to the Scenario PivotTable worksheet as follows: a. Create a Clustered Column PivotChart based on the PivotTable. b. Resize and reposition the chart so that it covers the range A8:D21. c. Hide the field buttons in the chart. [Mac Hint: The Field Buttons option is not available on Excel 365 for the Mac, so Mac users can ignore this instruction.] Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
Added by Eric N.
Close
Step 1
Step 1: Switch back to the All Services worksheet. Show more…
Show all steps
Your feedback will help us improve your experience
Akash M and 63 other AP CS educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
Leadership Training VESTA 6-Part Series INSURANCE GROUP Department First Name Last Name Part 1 Part 2 Part 3 Sales Bob Carver x x x Sales Heidi Lee x x Claims Josie Gates x x x Accounting Wendy Crocker x x Accounting Loretta Johnson x x Sales Walter Rivera x Claims Misty Whitfield x Marketing Matilda Lewis Accounting Elizabeth Hicks x x HR Alvin Rios x HR Brian Gaines x Sales Megan Bosworth Claims Maria Menzies x Claims Michael Russell x Total Paid Total Owed $50.00 $50.00 $75.00 $20.00 $20.00 $20.00 $50.00 $25.00
Akash M.
7. Write a formula in cell 'Salary Averages by Department'!B3, which can be copied across to cell 'Salary Averages by Department'!D3, and down to 'Salary Averages by Department'! D7 to calculate the average salary by department and by salary range. (Do not copy the formula to column E as you will need another formula for that column.) The salary ranges are as follows. $30,000 - $44,999.99 $45,000 - $54,999.99 $55,000 - $64,999.99 >= $65,000 Because there will be multiple criteria for this formula, you must use the AVERAGEIFS function. The arguments for this function will be as follows. Also, use the IFERROR function to return 0 if a department doesn't have salaries within a range. If you don't use the IFERROR function, Excel will return error messages. (Use cell references for all ranges and criterion.) Average Range: Employee's salary Range Range1: Employee's salary Range Criteria1: >=30000 Range2: Employee's salary range Criteria2: < 45,000 Range3: Departments Range Criteria3: Department (Add the appropriate $ to each cell reference.) If you write this formula correctly, you will only need to write one formula and copy it over and down. (Remember, do not copy the formula to column E.)
Task 1A: Calculate Count (1-3) Step 1: Use the =COUNTIF function in the highlighted Count column below to determine the number of projects 1-3. Step 2: Enter the following cell ranges for Quality (B13:B62), Speed (C13:C62), and Costs (D13:D62). Step 3: Use a Criteria of "1" Step 4: Ranges and Criteria must be entered correctly for the function to work. Please refer to the COUNTIF Functions Guide Tab for help on the correct format that should be used. Task 1B: Calculate Count (4-7) Step 5: Use the =COUNTIFS function in the highlighted Count column below to determine the number of projects 4-7. Step 6: Depending on the combinations you are solving for, use the following cell ranges for Quality (B13:B62), Speed (C13:C62), or Costs (D13:D62). Step 7: Use a Criteria of "1" Step 8: Ranges and Criteria must be entered correctly for the function to work. Please refer to the COUNTIF Functions Guide Tab for help on the correct format that should be used. Task 1C: Calculate PIP Percentage Step 9: Use the PIP Percentage column to divide each value you generated for the Count column by 50 to determine % of each. Task 1D: Calculate PIP Success Step 10: Use the =COUNTIFS function within the PIP Success column to determine the number of project(s) that the NEWS BOD found successful. Step 11: Depending on the combinations you are solving for, use the following cell ranges for Quality (B13:B62), Speed (C13:C62), or Costs (D13:D62), as well as, Results (H13:H62). Step 12: Then, divide the =COUNTIFS function by 50.
Sri K.
Recommended Textbooks
Computer Science and Information Technology
Introduction to Programming Using Python
Computer Science - An Overview
Transcript
Watch the video solution with this free unlock.
EMAIL
PASSWORD