New Perspectives Excel 2019 Sam projects assignment help

These New perspectives (NP_EX19) assignments are about our Microsoft Excel assignment help service. Students get lots of orders throughout the term and it contains many assignments and tests. This is one of our successfully delivered projects on Pureassignment excel help. Attached files section contains related documents that are given by the student. After you share your assignment documents with us, one of our MS Excel expert freelancers log in with your country’s VPN and take the tests & do the SAM projects for you. If you need similar help with a New Perspectives Excel project on SAM, register real quick and post a job on our Platform or contact with the support team using WhatsApp link at the right-bottom of the page.

Assignment Type: Excel Assignment on Dashboard

Assignment Categories: Excel Assignment Help, Dashboard Assignment Help

Platform: SAM projects on Client’s Dashboard

University: Unknown

Template: No template

Assignment Size: Approximately 1 minute for each task

Attached Files For New perspectives excel 2019 Sam projects assignment

New Perspectives Excel 2019 | Module 1: SAM Project 1a

*     GETTING STARTED

Open the file NP_EX19_1a_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as NP_EX19_1a_FirstLastName_2.xlsx by changing the “1” to a “2”.

If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file NP_EX19_1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

As the office administrator, you use Excel to maintain personnel data and summarize cash flow. You are finalizing the Personnel and Cash Flow worksheets for the current week.

Begin on the Personnel worksheet by cutting the contents of the range B1:B2 and pasting them into the range A1:A2.

Adjust the width of column A using AutoFit.

Change the width of column B to 16.00.

Enter the values shown in Table 1 into the corresponding cells in the range B14:C16.

Table 1: Data for the Range B14-C16

Enter [email protected] in cell D5. Select the range D5:D16, and then use the Flash Fill button (using the Fill drop-down arrow in the Editing group on the Home tab) to automatically enter codes into the remaining cells in the range. (Hint: You must use the Flash Fill button to properly receive credit for this step.)

Change the width of column D to 36.00.

Enter the word Total in cell A17.

In cell B17, create a formula using the SUM function to determine the total salary paid by Tabula Insurance Agency by totaling the values in the range B5:B16. Copy the formula you created in cell B17 to cell C17.

Enter Number of Employees in cell A19.

In cell B19, create a formula using the COUNT function to determine the number of people employed by Tabula Insurance Agency by counting the values in the range B5:B16.

Enter the text Average Commission in A21.

Switch to the Cash Flow worksheet. Change the orientation of the Cash Flow worksheet to Landscape.

In cell A2, change the cell content to Cash Flow: April 4-10, 2021 (instead of just reading “April 4-10, 2021”).

Select the range A4:E4 and the nonadjacent cell G8, and then increase the font size of the cells to 12 point.

To better display the content, manually change the height of row 3 to 8.00. (Hint: Do not use AutoFit.)

In cell B7, enter a formula without using a function to determine the profit generated from auto insurance policies by subtracting auto expenses (cell B6) from auto sales (cell B5) for the week of April 4-10, 2021. Copy the formula you created in cell B7 to the range C7:D7.

After determining the total profits generated by each store, you are now interested in calculating the total sales, expenses, and profits across all types of insurance sold. Select the range E5:E7, and then apply AutoSum to calculate the totals for these categories across all three types of insurance.

Since you just edited the worksheet, the content in cell A9 is no longer accurate. Click on cell A9 and clear its contents.

Apply the Wrap Text formatting to cell G8.

Adjust the Zoom level of the Cash Flow worksheet to 130%.

After reviewing the Personnel and Cash Flow worksheets, you typically generate some additional data for accounting. To create a placeholder worksheet for this data, insert a new worksheet in the workbook, rename the worksheet Accounting,and if necessary, move the new worksheet after the Cash Flow worksheet.

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 SAM website to submit your completed project.

*       Final Figure 1: Personnel Worksheet

*       Final Figure 2: Cash Flow Worksheet

City of New Monroe

PROJECT STEPS

As a member of the Finance Committee for the City of New Monroe, you use Excel to maintain city data and summarize cash flow. Go to the Committees worksheet. Cut the contents of the range C2:C3 and paste them into the range B2:B3.

Adjust the width of column B using AutoFit.

Change the width of column F to 26.00.

Enter the values shown in Table 1 into the corresponding cells in the range B8:E8.

*   Table 1: Data for the Range B8:E8

Enter [email protected] in cell F6. Select the range F6:F14, and then use the Flash Fill button (in the Editing group on the Home tab) to automatically enter codes into the remaining cells in the range. (Hint: You must use the Flash Fill button to properly receive credit for this step.)

Adjust the width of column G using AutoFit.

In cell C15, enter Totals to identify the values you will calculate in the range D15:E15.

In cell D15, create a formula using the SUM function to determine the total amount of grants received by the city by totaling the values in the range D6:D14. Copy the formula you created in cell D15 to cell E15 to determine the total budget amounts. [Mac Hint: The user may need to increase the column width to preview the result.]

In cell B17, enter Number of Committees to identify the value you will calculate in cell C17.

In cell C17, create a formula using the COUNT function to determine the number of committees by counting the values in the range D6:D14.

In cell B18, enter Average Grant to identify the value in cell C18.

Switch to the Cash Flow worksheet. Change the orientation of the Cash Flow worksheet to Landscape.

In cell B2, change the cell content to City of New Monroe (instead of just reading “City of”).

Select the range B5:B9 and the nonadjacent cell C5, and then increase the font size of the cells to 12 point.

To better display the column headings, manually change the height of row 6 to 36.00. (Hint: Do not use AutoFit.)

In cell C9, enter a formula without using a function to determine the change in cash in week 1 by subtracting disbursements (cell C8) from receipts (cell C7). Copy the formula you created in cell C9 to the range D9:F9.

Select the range G7:G9, and then apply AutoSum to calculate the total receipts, disbursements, and change in cash for weeks 1 through 4.

Clear the contents of cell B11 because you edited the worksheet after February 1, 2021.

Apply the Wrap Text formatting to cell G6.

Adjust the Zoom level of the Cash Flow worksheet to 110% to make the contents easier to read.

You typically submit financial data to the auditor for verification. To create a placeholder worksheet for this data, insert a new worksheet in the workbook, rename the worksheet Auditing, and if necessary, move the new worksheet after the Cash Flow worksheet.

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 SAM website to submit your completed project.

Bergamot Urgent Care Clinic

PROJECT STEPS

Jennifer Williams is the business manager at the Bergamot Urgent Care Clinic. Jennifer is adding formatting to the workbook she uses to track statistics on patient visits to make it easier to read and use. Change the theme of the workbook to Office.

Switch to the Visits worksheet and then format the headings. Merge and Center the range A1:H1.

Apply the Heading 1 style to the range A1:H1.

Merge and Center the range A2:H2.

Next, Jennifer needs to format the report dates. Change the background color of the range A4:B5 to Gray, Accent 3, Lighter 80% (7th column, 2nd row of the Theme Colors palette).

Format the range A4:A5 as bold.

Format the range B4:B5 to use a date format with a two-digit month, a two-digit day, and a two-digit year (e.g., 09/26/21).

She also wants to format the table headings. Format the range A7:H8 as italic.

Apply horizontal centering to the contents of each cell in the range A7:H8.

Change the background color of the range A7:H7 to Orange, Accent 2, Lighter 60% (6th column, 3rd row of the Theme Colors palette).

Change the background color of the ranges B8:D8 and F8:H8 to Orange, Accent 2, Lighter 40% (6th column, 4th row of the Theme Colors palette).

Merge and Center the range A7:A8 and then center the contents of the merged cell vertically.

Merge and Center the range E7:E8 and then center the contents of the merged cell vertically.

Merge and Center the range B7:D7 and then Merge and Center the range F7:H7.

Jennifer would like to spruce up the table data. Change the background color of the range A10:H10 to White, Background 1, Darker 5% (1st column, 2nd row of the Theme Colors palette) and then repeat for the range A12:H12.

In the range F9:H12, apply the Percentage number format with zero decimal places.

Use the Highlight Cells Rules conditional formatting to format cells in the range G9:G12 with a value less than 6% using Light Red Fill with Dark Red Text.

For the merged range A17:A23, rotate the cell contents to 0 degrees and then change the indent to 2.

Find and replace all instances of the text “Ins” with Insurance, not including any punctuation. (Hint: You should find and replace two instances.)

Finally, she needs to format the workbook for printing. Insert a page break to start a new page at row 14.

Set the margins to Narrow.

Set rows 1 and 2 as print titles.

Create a custom footer for the worksheet. In the left footer section, display the current page number using a Header and Footer element. In the center footer section, display the Sheet Name using a Header and Footer element.

Villanova Technology Group

PROJECT STEPS

Mitchell Dupree is an associate consultant at the Villanova Technology Group, a consulting firm that specializes in information technology. Mitchell uses an Excel workbook to track statistics on client billing and is formatting the workbook to make it easier to read and use.

Change the theme of the workbook to Office to use the standard theme for Villanova Technology.

Switch to the Billing Summary worksheet. Merge and center the range A1:I1 to begin formatting the contents as the worksheet title.

Apply the Title cell style to the range A1:I1.

Merge and center the range A2:I2 to clarify the range contains the worksheet subtitle.

Format the date information to separate it from the rest of the workbook. For the range A3:B4, change the background color to Gold, Accent 4, Lighter 80% (8th column, 2nd row in the Theme Colors palette).

Format the range A3:B4 as bold.

Format the range B3:B4 to use a date with a two-digit month, a two-digit day, and a two-digit year (e.g., 10/04/21).

The range B6:I11 serves as a table showing billing details such as service and hours billed. Format the column headings in the range B6:I7 as italic.

Center the contents of each cell in the range B6:I11.

For the range B6:I6, change the background color to Gold, Accent 4, Lighter 60% (8th column, 3rd row in the Theme Colors palette).

For the ranges C7:E7 and G7:I7, change the background color to Gold, Accent 4, Lighter 40% (8th column, 4th row in the Theme Colors palette).

Merge the range B6:B7, and then middle align the contents of the merged cell.

Merge the range F6:F7, and then middle align the contents of the merged cell.

Merge and center the range C6:E6, and then merge and center the range G6:I6.

Format the table data to make it easy to read and interpret. For the ranges B9:I9 and B11:I11, change the background color to Gold, Accent 4, Lighter 80% (8th column, 2nd row in the Theme Colors palette).

In the range G8:I11, apply the Percentage number format with one decimal place.

In the range G8:G11, use Conditional Formatting Highlight Cells Rules to format cells with a value less than 20% using Light Red Fill with Dark Red Text.

For the merged range A8:A11, change the rotation of the text to 0 degrees, and then change the indent to 2 to clarify that the text applies to the table data.

Find and replace all instances of the text “hr” with “hour” to use the full word instead of an abbreviation. (Hint: You should find and replace three instances.)

Finally, Mitchell wants to format the workbook for printing. Insert a page break to start a new page at row 13.

Change the workbook margins to Narrow.

Use rows 1 and 2 as print titles.

Create a custom header for the worksheet. In the left header section, display the Sheet Name using a Header and Footer element. In the right header section, display the Page Number using a Header and Footer element.

Scrub Jay Peripherals

PROJECT STEPS

Danilo Lewellen is an entrepreneur planning to manufacture and market an innovative handheld controller for virtual reality gaming. Danilo is building a business plan as he recruits investors and plans logistics for the launch of his new company, Scrub Jay Peripherals, and wants to use Excel to summarize data and create projections.

Switch to the Sales Projections worksheet. Use the values in the range B6:C6 to extend the incremented percentages to the range D6:H6.

Use the values in the range A7:A8 to extend the list of years to the range A9:A11.

Use AutoFill to fill the range * with the formatting from the range A7:H8.

In cell B8, create a formula without using a function that multiplies the value in cell B7 by the value in cell B6 and then adds the value in B7. In this formula, use a mixed reference for cell B6 by including an absolute reference to row 6. Copy the formula from cell B8 to the range B9:B11 and then copy the formulas from the range B8:B11 to the range C8:H11.

Switch to the Cost Estimates worksheet. In cell A9, create a formula using the AVERAGE function that calculates the average of the values in the range A5:A7, then copy your formula to cell D9.

In cell A10, create a formula using the MAX function that identifies the maximum value in the range A5:A7 and then copy your formula to cell D10.

In cell A11, create a formula using the MIN function that identifies the minimum value in the range A5:A7 and then copy your formula to cell D11.

In cell B13, create a formula using the VLOOKUP function that looks up the value from cell A11 in the range A5:B7, returns the value in column 2, and specifies an exact match. Copy the formula to cell E13.

Switch to the Profit Projections worksheet. In cell H5, use the TODAY function to insert the current date.

In cell H7, create a formula that uses the NETWORKDAYS function to calculate the number of working days between the dates in cells H5 and H6. Leave the Holidays argument blank.

Use the values in the range B12:C12 to extend the incremented percentages to the range D12:H12.

Use the values in the range A13:A14 to extend the list of years to the range A15:A17.

In cell B13, create a formula without a function using absolute references that subtracts the values of cells B5 and B7 from cell B6 and then multiplies the result by cell B8. Copy the formula from cell B13 to the range C13:H13.

In cell B14, create a formula without using a function that adds 1 to the value in cell B12 and then multiplies the result by the value in cell B13. In this formula, use a mixed reference for cell B12 by including an absolute reference to row 12. Copy the formula from cell B14 to the range B15:B17 and then copy the formulas from the range B14:B17 to the range C14:H17.

Delete row 19.

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 SAM website to submit your completed project.

(Note: The value in cell H7 of the Profit Projections worksheet may differ from that shown below.)

Alanis Parks Department

PROJECT STEPS

Gudrun Duplessis is a manager in the Alanis Parks Department. Gudrun is compiling spending data in preparation for a city bond offering for park funding. She wants to use Excel to create charts to illustrate some of her data and to apply a function to calculate the potential monthly and yearly costs to the city of different bond scenarios.

Switch to the Spending worksheet. In the range E5:E10, add Conditional Formatting to show Solid Blue Data Bars.

In the range F5:F10, add Line sparklines based on the data in the range B5:E10.

Apply the Blue, Accent 1, Darker 25% (5th column, 5th row in the Theme Colors palette) sparkline color to the range F5:F10.

Gudrun has created a pie chart representing the percentage of the parks’ budget that went to each park in 2018. Modify the chart in the range G2:O20 as follows:

Enter 2018 Park Spending as Percentage of Total as the chart title.

Change the data labels to include the Category as well as the Percentage, and position the labels in the Outside End location.

Remove the Legend from the chart.

Gudrun would like a pie chart representing the percentage of the parks’ budget that would go to each park in 2021.

Create a 2-D Pie chart based on the data in the range E5:E9 and using the category labels in the range A5:A9.

Resize and reposition the chart so that the upper-left corner is located within cell G21 and the lower-right corner is located within cell O39, and then left-align the chart with the pie chart above it in the range G2:O20.

Customize the chart in the range G21:O39 as follows:

Enter 2021 Park Spending as Percentage of Total as the chart title.

Apply Style 3 to the chart.

Gudrun wants a stacked bar chart showing spending per park for each year.

Create a Stacked Bar chart based on the data in the range A4:E9.

Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower-right corner is located within cell F39.

Customize the chart in the range A11:F39 as follows:

Enter Spending per Park 2018-2021 as the chart title.

Change the Maximum value to $2,300,000 on the horizontal axis.

Apply a shape fill of White, Background 1, Darker 5% (1st column, 2nd row in the Theme Colors palette) to the chart area.

Update the Park Spending 2018-2021 combo chart in the range A40:G56 as follows:

Move the chart to the Projection worksheet.

Resize and reposition the chart so that the upper-left corner is located within cell A4 and the lower-right corner is located within cell K22.

For the Park Spending 2018-2021 combo chart, update the chart area as follows:

Apply the Monochromatic Palette 4 color scheme to the chart.

Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the format pane to add the axis.]

Add Axis Titles to the chart, and then enter Spending per park as the left vertical axis title, and then enter Total spending as the right vertical axis title. Finally, delete the horizontal axis title placeholder.

Gudrun wants to compare future payments for three different scenarios for a park’s bond offering. Switch to the Bond Offering worksheet. She has already entered formulas in the range B9:D10 to calculate the quarterly and annual payments for each option.

In the range E9:E10, add Column sparklines using the data in the range B9:D10. Ungroup the sparklines, and then set the vertical axis maximum value to -15000 for cell E9 and to -60000 for cell E10.

Gudrun wants a clustered column chart comparing the costs of the bond options.

Create a Clustered Column chart based on the values in the range A9:D10.

Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower-right corner is located within cell D28.

Customize the chart in the range A11:D28 as follows:

Enter Bond Offering Cost Comparison as the chart title.

Add Data Labels to the chart.

Narbonne Suites

Edra Kinsman is the sales manager for Narbonne Suites hotel chain, which has locations in Washington, Oregon, Idaho, and Montana. Edra is using multiple worksheets to summarize sales data by state.
Break the external link in the worksheet, so that the formulas in the range B4:B7 of the Manager Information worksheet are replaced with static values. Then switch to the Manager Information worksheet.

In cell C5, remove the hyperlink, leaving the unlinked text “Bozeman, MT” in the cell.

In cell D7, create a hyperlink to an email address as follows:

Link to the email address:
[email protected]

Use [email protected] as the text to display.

Use Email the statewide manager for Washington as the ScreenTip text.

In cell B9, create a hyperlink to the Narbonne Suites Locations listing as follows:

Link to the file Support_EX19_5a_Locations.docx.

Use Narbonne Suites locations as the text to display.

Use View the current list of Narbonne Suites Locations as the ScreenTip text.

Edit the hyperlink in cell B10 as follows:

Use Narbonne Suites website as the display text.

Use View the public-facing website for Narbonne Suites as the ScreenTip text.

Edra wants to apply consistent formatting to the worksheets submitted by each of the statewide managers. Group the Idaho, Montana, and Oregon worksheets together and then make the following formatting updates:

Change the font size in the merged range A1:F1 to 18 point.

Apply the 20% – Accent 6 cell style to the merged range A2:F2.

Bold the values in the range A6:A10.

Apply the Accounting number format with zero decimal places and $ as the symbol to the range B6:F10.

Resize the column width of column B-F to 12. Do not ungroup the worksheets.

With the Idaho, Montana, and Oregon worksheets still grouped, update the worksheet as follows:

In cell A6, change the text “In Person” to read:
Walkup

In cell A9, change the text “Other” to read:
Other referrals
Do not ungroup the worksheets.

With the Idaho, Montana, and Oregon worksheets still grouped, create a formula as follows:

Enter a formula in cell B10 using the SUM function that totals the sales for Q1.

Copy the formula to the range C10:E10.
Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 6-8 are present in all three worksheets.

Edra wants to create a copy of the formatted Oregon worksheet to use for the Washington section of the chain’s sales data. Create a copy of the Oregon worksheet between the Oregon worksheet and the Consolidated Sales worksheet, and then update the worksheet as follows:

Change the worksheet name to Washington for the copied worksheet.

Edit the text to read Washington in the merged range A2:F2.

Clear the contents of the range B6:E9.

Edra wants to consolidate the data from each of the regions. Switch to the Consolidated Sales worksheet, then update the worksheet as follows:

In cell A6, enter a formula without using a function that references cell A6 in the Washington worksheet.

Copy the formula from cell A6 to the range A7:A9 without copying the formatting.

In cell B6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B6 in the Idaho:Washington worksheets.

Copy the formula from cell B6 to the range B7:B9 without copying the formatting.

Then copy the formulas and the formatting from the range B6:B9 to the range C6:E9.

Edra started to create named ranges in the worksheet and has asked you to complete the work. Create a defined name for the range B6:E6 using Walkup as the range name.

Create names from the range A7:E9 using the values shown in the left column.

Apply the defined names Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales to the formulas in the range B10:E10 in the Consolidated Sales worksheet.

Change the defined name to Totals_2021 for the range F6:F9. [Mac Hint: Delete the existing defined name “Totals” and add the new defined name.]

Edra wants to compare 2021 sales totals to the sales totals for 2020 and needs to add the 2020 data to the Consolidated Sales worksheet. Open the file Support_EX19_5a_2020_Sales.xlsx. Switch back to the NP_EX19_5a_FirstLastName_2.xlsx workbook and go to the Consolidated Sales worksheet. Create external references as follows:

Link cell G6 in the Consolidated Sales worksheet to cell F6 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.

Link cell G7 in the Consolidated Sales worksheet to cell F7 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.

Link cell G8 in the Consolidated Sales worksheet to cell F8 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.

Link cell G9 in the Consolidated Sales worksheet to cell F9 in the Consolidated Sales 2020 worksheet in the Support_EX19_5a_2020_Sales.xlsx workbook.

Do not break the links. Close the Support_EX19_5a_2020_Sales.xlsx workbook.

In cell G10, enter a formula to total the values in the defined range Totals_2020, using the SUM function and the defined range name.

Oval Lake Hospital

PROJECT STEPS

Donnie Cruz is a finance assistant for Oval Lake Hospital in south Florida. His current project is to format departmental data for the years 2021-2023 to make it easier to analyze.
Switch to the Cancer Center worksheet. Unfreeze the top row of the worksheet.

Sort the data in the CancerCenter table first in ascending order by the Admission field and then in ascending order by the Service field.

Insert a Total Row in the CancerCenter table, and then use the Total row to calculate the total of the values in the 2021, 2022, and 2023 fields.

The administrator overseeing the cancer center is considering mandating that all inpatient service names be different from outpatient service names, and wants to see which duplicate names are currently in use. Apply a conditional formatting rule to the range C4:C11 that uses Light Red Fill with Dark Red Text for duplicate values.

Switch to the Cardiac Care worksheet. Freeze the first column of the worksheet.

Format the range A3:F10 as an Excel table with headers using the Gold, Table Style Medium 5 table style. Enter CardiacCare as the name of the table.

Donnie notices that the CardiacCare table is missing a record. Add the record shown in Table 1 to the end of the CardiacCare table.

*   Table 1: Data for the Range A11:F11

The Maternity department has more admission criteria than other major divisions of the hospital. Donnie wants to summarize Maternity admissions data using subtotals to show the relationships between admission type and number of patients. Switch to the Maternity worksheet and complete the following steps:

Sort the table by the Admission field in ascending order.

Convert the table to a normal range.

Insert subtotals into the range A3:F13, with the subtotals appearing at each change in the Admission column value. The subtotals should use the SUM function and include subtotals for the 2021, 2022, and 2023 fields.

Switch to the Psychiatric worksheet and remove the duplicate record based on the values in the Admission and Service columns.

The data bars in the last three columns make some of the numbers hard to read. Change the Data Bars conditional formatting for the range D4:F12 to use the Solid Fill Green Data Bar format.

Donnie wants to calculate total admissions for the hospital for each year. Switch to the All Departments worksheet, then create the following formulas:

In cell D40 use the SUM function with a structured reference to total the values in the 2021 column of the AllDepartments table. 

In cell E40 use the SUM function with a structured reference to total the values in the 2022 column of the AllDepartments table. 

In cell F40 use the SUM function with a structured reference to total the values in the 2023 column of the AllDepartments table. 

Donnie wants to prepare the AllDepartments table for 2024 data when it becomes available.
Add a table column to the end of the table.

Valerian State College

PROJECT STEPS

Lael Masterson works in the Student Activities Office at Valerian State College in Illinois. Lael has started compiling information on students who are interested in helping run student organizations at Valerian State, and she needs your help completing the workbook.
Switch to the Student Representatives worksheet. In cell E2, enter a formula using the HLOOKUP function as follows to determine a student’s potential base hourly rate (which is based on the number of years of post-secondary education):

Use a structured reference to look up the value in the Post-Secondary Years column. Retrieve the value in the 2nd row of the table in the range P13:U14, using an absolute reference. Because base hourly rate is tiered based on the number of years of education, find an approximate match.

Fill the formula into the range E3:E31, if necessary.

Student organizations sometimes require transportation for off-campus activities, and school policy requires students to be over 23 years old to serve as transport.
Lael wants to determine how many of the active students will be eligible to transport other group members. In cell J2, enter a formula using the IF function and structured references as follows to determine if Kay Colbert can serve as authorized transport:

The function should use a reference to the Age column to determine if the student’s age is greater than 23, and should return the text Yes if true and No if false.

Fill the formula into the range J3:J31, if necessary.

To be eligible for the leadership training program offered by the office, a student must have at least 2 years of post-secondary education or have gone through the organization finance training.
In cell K2 enter a formula using the IF and OR functions and structured references as follows to determine if Kay Colbert can join the leadership training program:

The IF function should determine if the student’s Post-Secondary Years is greater than or equal to 2 OR if the student’s finance certified status is “Yes”, returning the text Yes if a student meets one or both of those criteria or the text No if a student meets neither of those criteria.

Fill the formula into the range K3:K31, if necessary.

Experienced students may serve as mentors if they are at least age 21 and have at least 3 years of post-secondary education. In cell L2, enter a formula using the IF and AND functions and structured references as follows to determine if Kay Colbert is eligible to serve as a mentor:

The IF function should determine if the student’s age is greater than or equal to 21 AND the student’s post-secondary years are greater than or equal to 3, and should return the text Yes if a student meets both of those criteria or the text No if a student meets none or only one of those criteria.

Fill the formula into the range L3:L31, if necessary.

Lael is always on the lookout for students who might be interested in running for office in student groups.
In cell M2, enter a formula using a nested IF function and structured references as follows to determine first if a student has already been elected to office in a student group, and if not, whether that student meets the qualifications to run in the future:

If the value in the Elected column is equal to the text “Yes”, the formula should display Elected as the text.

Otherwise, the formula should determine if the value in the Finance Certified column is equal to the text “Yes” and return the text Yes if true And No if false.

Students who work with student organizations are also considered for employment at the Student Activities Office. Students with more than 4 years of post-secondary education are qualified for more complex Tier 2 jobs.
In cell N1, enter the text Tier as the column heading.

In cell N2, enter a formula using the IF function and structured references as follows to determine which work tier Kay Colbert is qualified for:

The IF function should determine if the student’s Post-Secondary Years is greater than or equal to 4, and return the value 2 if true or the value 1 if false.

Fill the formula into the range N3:N31, if necessary.

Lael wants a quick way to look up students by their Student ID.
In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text Invalid Student ID should display.

Lael wants to determine several totals and averages for active students.
In cell Q8, enter a formula using the COUNTIF function and structured references to count the number of students who have been elected to offices in student organizations.

In cell R8, enter a formula using the AVERAGEIF function and structured references to determine the average number of post-secondary years for students who have been elected.

In cell R9, enter a formula using the AVERAGE function and structured references to determine the average number of years of post-secondary education of all students as shown in the Post-Secondary Years column.

Switch to the Academic Groups worksheet. In cell A14, use the INDEX function and structured references to display the value in the first row and first column of the AcademicGroups table.

In cell A17, use the SUMIF function and structured references to display the total membership in 2023 for groups with at least 40 members.

Lael is also planning for student groups that the office will be working with in the coming year. She decides to create a PivotTable to better manipulate and filter the student group data.
Switch to the Academic PivotTable worksheet, then create a PivotTable in cell A1 based on the AcademicGroups table. Update the PivotTable as follows so that it matches Final Figure 2:

Change the PivotTable name to: AcademicPivotTable

Add the Activities field and the Group Name field (in that order) to the Rows area.

Add the 2021, 2022, and 2023 fields (in that order) to the Values area.

Change the display of subtotals to Show all Subtotals at Top of Group.

Change the report layout to Show in Outline Form.

Update the Sum of 2021 field in the Values area to display the name 2021 Membership with the Number number format with 0 decimal places.

Update the Sum of 2022 field in the Values area to display the name 2022 Membership with the Number number format with 0 decimal places.

Update the Sum of 2023 field in the Values area to display the name 2023 Membership with the Number number format with 0 decimal places.

Lael wants to summarize data for all student groups in a PivotTable. To do so, she must first update the AllGroups table.
Switch to the All Groups worksheet then edit the record for the Astronomy Society to use 76 as the 2023 field value.

Switch to the All Groups PivotTable worksheet. Refresh the PivotTable data, then verify that the 2023 Membership value for the Astronomy Society in row 6 reflects the change you made in the previous step.

Apply the Light Blue, Pivot Style Medium 2 PivotTable style to the PivotTable.

Add the Office field to the Filters area of the Pivot Table. Filter the table so that only organizations with private offices are visible.

Filter the PivotTable as follows:

Create a Slicer based on the Activities field value.

Resize the slicer so that it has a height of 2.2″ and a width of 3.2″.

Move the slicer so that its upper-left corner appears within cell F3 and its lower-right corner appears within cell J14.

Use the slicer to filter the PivotTable so that only Fraternal groups are visible.

Lael also wants to summarize membership data for all organizations using a PivotChart to help determine which groups are showing the most interest from students.
Switch to the Activities PivotTable worksheet. Based on the PivotTable on the Activities PivotTable worksheet, insert a PivotChart using the Clustered Column chart type and format it as follows:

Resize and reposition the PivotChart so that the upper-left corner is located within cell F3 and the lower-right corner is located within cell O19.

Add the chart title Membership by Type to the PivotChart using the Above Chart option.

Filter the PivotChart so that only the membership data for groups with educational, field, and fraternal activities in each type of group displays in the chart. (This filter may be automatically applied when you create the table.)

Cairo Consulting

• PROJECT STEPS
• Dewayne Lipinski works for Cairo Consulting in Albuquerque, New Mexico. As an intern, he is developing a workbook that includes the financial details of the consulting services that the company offers. Dewayne has been asked to analyze the data to find the most profitable mix of offerings using the most cost-effective service providers. Switch to the Management worksheet. Create a one-variable data table to calculate the sales, expenses, and profit based on the hours of management consulting contracted, as follows:
  • In cell D5, enter a formula without using a function that references cell B4, which is the expected hours of management consulting contracted.
  • In cell E5, enter a formula without using a function that references cell B19, which is the expected total sales for management consulting.
  • In cell F5, enter a formula without using a function that references cell B20, which is the expected total expenses for this product.
  • In cell G5, enter a formula without using a function that references cell B21, which is the expected gross profit for this product.
  • Select the range D5:G10 and then complete the one-variable data table, using cell B4 as the Column input cell for your data table.
• Create a two-variable data table to calculate the gross profit based on the hours of management consulting contracted and the hourly rate charged:
  • For the range D14:K19, create a two-variable data table using the hourly rate charged (cell B5) as the Row input cell.
  • Use the hours of management consulting contracted (cell B4) as the Column input cell.
  • Apply a custom format to cell D14 to display the text Hours/Price in place of the cell value.
• Switch to the Change worksheet. Create a Scatter with Straight Lines chart based on the range D4:F14 in the data table Organizational Change – Break-Even Analysis.
• Modify the new chart as follows:
  • Resize and reposition the chart so that it covers the range D15:H32.
  • Remove the chart title from the chart.
  • Add Sales and Expenses as the vertical axis title and Hours Contracted as the horizontal axis title.
• Change the Bounds Axis Options as follows:
  • Change the Minimum Bounds of the vertical axis to 1,000,000 and leave the Maximum Bounds at 2,000,000.
  • Change the Number format of the vertical axis to Currency with 0 Decimal places and $ as the Symbol.
  • Change the Minimum Bounds of the horizontal axis to 3,000 and the Maximum Bounds to 5,000.
• Create two scenarios to compare the costs for standard consultants and consultants with change certification as follows:
  • In the Scenario Manager, add two scenarios using the data shown in bold in Table 1 below.
  • The changing cells for both scenarios are the nonadjacent cells B11 and B14.
  • Close the Scenario Manager without showing any of the scenarios.

*   Table 1: Organizational Change Scenario Values

Switch to the Process worksheet. Create a Scatter with Straight Lines chart based on range D6:I14 in the data table Process Consulting – Net Income Analysis.

Modify the new chart as follows:

Resize and reposition the chart so that it covers the range D15:I32.

Remove the chart title from the chart.

Reposition the chart legend to the right of the chart.

Add Net Income as the vertical axis title and Hours Contracted as the horizontal axis title.

Change the colors of the chart to Monochromatic Palette 1 (1st row in the Monochromatic palette).

Change the Bounds Axis Options for the new chart as follows:

Change the Minimum Bounds of the vertical axis to -100,000 and the Maximum Bounds to 650,000.

Set the horizontal axis to cross at the axis value -100,000.

Change the Number format of the vertical axis to Currency with 0 Decimal places and $ as the Symbol.

Change the Minimum Bounds of the horizontal axis to 3,500 and the Maximum Bounds to 7,500.

Edit the chart series names as follows:

For Series 1, use cell E5 as the series name.

For Series 2, use cell F5 as the series name.

For Series 3, use cell G5 as the series name.

For Series 4, use cell H5 as the series name.

For Series 5, use cell I5 as the series name.

Cairo Consulting wants to determine whether reselling process consulting services provided by third parties would reduce the costs of this type of consulting. Switch to the Process Providers worksheet, and then run Solver to solve this problem as follows:

Set the objective as minimizing the value in cell E10 (Total Costs).

Use the range B4:D4 as the changing variable cells.
Adjust the hours provided by each company using the following constraints:

E4=5500, the total hours of process consulting signed

E10 is less than or equal to 1350000, the maximum third-party fees

B4:D4 is less than or equal to 5000, the maximum hours provided by a single third-party provider

B4:D4 should be an Integer

Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model to the range A14:A21, and then close the Solver Parameters dialog box.

Switch to the All Types worksheet. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Status Quo, Third Party, and Raise Rates scenarios. Use the range B17:D17 as the result cells.

Switch back to the All Types worksheet. Use the Scenario Manager as follows to compare the profit per hour in each scenario:

Create a Scenario PivotTable report for result cells B17:D17.

Remove the Filter field from the PivotTable.

Change the Number format of the Profit_Per_Hour_Contracted_Mana, Profit_Per_Hour_Contracted_Chan, and Profit_Per_Hour_Contracted_Proc fields (located in the Values box of the PivotTable Field List) to Currency with 2 Decimal places and $ as the Symbol.

Use Management as the row label value in cell B3, Change as the value in cell C3, and Process as the value in cell D3.

In cell A1, use Profit Per Hour Contracted as the report title.

Format the report title using the Title cell style.

Resize columns A–D to 15.00.

Add a PivotChart to the Scenario PivotTable worksheet as follows:

Create a Clustered Column PivotChart based on the PivotTable.

Resize and reposition the chart so that it covers the range A8:D20.

Hide the field buttons in the chart. [Mac Hint: The Field Buttons option is not available on Excel 2019 for the Mac, so Mac users can ignore this instruction.]

Mount Moreland Hospital

PROJECT STEPS

Pranjali Kashyap is a financial analyst at Mount Moreland Hospital in Baltimore, Maryland. She is using an Excel workbook to analyze the financial data for a proposed program called Neighborhood Nurse. The program involves nurses and nurse practitioners providing healthcare services to Baltimore neighborhoods from a van outfitted with medical equipment and supplies. She asks for your help in correcting errors and making financial calculations in the workbook.
Go to the Loan Payments worksheet. The hospital needs a loan to buy the medical van for the Neighborhood Nurse program. Before Pranjali can calculate the principal and interest payments on the loan, she asks you to correct the errors in the worksheet. Correct the first error as follows:

In cell H17, use the Error Checking command to identify the error in the cell.

Correct the error to total the values in the range C17:G17. In a later step, you will calculate the interest and principal in the range C17:G18 to remove the remaining errors.

Correct the #VALUE! errors in the worksheet as follows:

Use Trace Precedents arrows to find the source of the #VALUE! error in cell C20.

Correct the formula in cell C20, which should divide the remaining principal (cell C19) by the loan amount (cell D5) to find the percentage of remaining principal.

Fill the range D20:G20 with the formula in cell C20 to correct the remaining #VALUE! errors.

Remove any remaining trace arrows.

Now Pranjali is ready to calculate the annual principal and interest payments for the medical van. Start by calculating the cumulative interest payments as follows:

In cell C17, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell C15 through payment 12 in cell C16). Use 0 as the type argument in your formula because payments are made at the end of the period.

Use absolute references for the rate, nper, and pv arguments, which are listed in the range D5:D11.

Use relative references for the start and end arguments.

Fill the range D17:G17 with the formula in cell C17 to calculate the interest paid in Years 2–5 and the total interest.

Calculate the cumulative principal payments as follows:

In cell C18, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell C15 through payment 12 in cell C16). Use 0 as the type argument in your formula because payments are made at the end of the period.

Use absolute references for the rate, nper, and pv arguments, which are listed in the range D5:D11.

Use relative references for the start and end arguments.

Fill the range D18:G18 with the formula in cell C18 to calculate the principal paid in Years 2–5 and the total principal.

Go to the Depreciation worksheet. Pranjali needs to correct the errors on this worksheet before she can perform any depreciation calculations.
Correct the errors as follows:

Use Trace Dependents arrows to determine whether the #VALUE! error in cell D12 is causing the other errors in the worksheet.

Use Trace Precedents arrows to find the source of the error in cell D12.

Correct the error so that the formula in cell D12 calculates the cumulative straight-line depreciation of the medical van by adding the Cumulative depreciation value in Year 1 to the Annual depreciation value in Year 2.

Pranjali wants to compare straight-line depreciation amounts with declining balance depreciation amounts to determine which method is more favorable for the hospital’s balance sheet. In the range D5:D7, she estimates that the Neighborhood Nurse program will have $234,000 in tangible assets at startup, and that the useful life of these assets is seven years with a salvage value of $37,440.
Start by calculating the straight-line depreciation amounts as follows:

In cell C11, enter a formula using the SLN function to calculate the straight-line depreciation for the medical van during its first year of operation.

Use absolute references for the cost, salvage, and life arguments in the SLN formula.

Fill the range D11:I11 with the formula in cell C11 to calculate the annual and cumulative straight-line depreciation in Years 2–7.

Calculate the declining balance depreciation amounts for the medical van as follows:

In cell C18, enter a formula using the DB function to calculate the declining balance depreciation for the medical van during its first year of operation.

Use Year 1 (cell C17) as the current period.

Use absolute references only for the cost, salvage, and life arguments in the DB formula.

Fill the range D18:I18 with the formula in cell C18 to calculate the annual and cumulative declining balance depreciation in Years 2–7.

Pranjali also wants to determine the depreciation balance for the first year and the last year of the useful life of the medical van.
Determine these amounts as follows:

In cell E22, enter a formula using the SYD function to calculate the depreciation balance for the first year.

Use Year 1 (cell C17) as the current period.

In cell E23, enter a formula using the SYD function to calculate the depreciation balance for the last year.

Use Year 7 (cell I17) as the current period.

Go to the Earnings Projections worksheet. Pranjali has entered most of the income and expense data on the worksheet. She knows the income from municipal grants will be $25,000 in 2022, and estimates it will be $40,000 in 2026. She needs to calculate the income from municipal grants in the years 2023–2025. The grants should increase at a constant amount from year to year.
Project the income from Municipal grants for 2023–2025 (cells D5:F5) using a Linear Trend interpolation.

Pranjali also needs to calculate the income from insurance reimbursements in the years 2023–2025. She knows the starting amount and has estimated the amount in 2026. She thinks this income will increase by a constant percentage.
Project the income from Insurance reimbursements for 2023–2025 (cells D7:F7) using a Growth Trend interpolation.

Pranjali needs to calculate the payroll expenses in the years 2023–2026. She knows the payroll will be $140,000 in 2022 and will increase by at least five percent per year.
Project the payroll expenses as follows:

Project the expenses for Payroll for 2023-2026 (cells D13:G13) using a Growth Trend extrapolation.

Use 1.05 (a 5 percent increase) as the step value.

The Projected Revenue line chart in the range H4:Q19 shows the revenue Pranjali estimates in the years 2022–2026. She wants to extend the projection into 2027.
Modify the Projected Revenue line chart as follows to forecast the future trend:

Add a Linear Trendline to the Projected Revenue line chart.

Format the trendline to forecast 1 period forward.

The Revenue Trend scatter chart in the range A21:G40 is based on monthly revenue estimates listed on the Monthly Revenue Projections worksheet. Pranjali wants to include a trendline for this chart that shows how revenues increase quickly at first and then level off in later months.
Modify the Revenue Trend scatter chart as follows to include a logarithmic trendline:

Add a Trendline to the Revenue Trend scatter chart.

Format the trendline to use the Logarithmic option.

Go to the Investment worksheet. This worksheet should show the returns potential investors could realize if they invested $165,000 in the Neighborhood Nurse program. Pranjali figures a desirable rate of return would be 7.3 percent. She estimates the investment would pay different amounts each year (range C7:C12) and wants to calculate the present value of the investment.
Calculate the present value of the investment as follows:

In cell C15, enter a formula that uses the NPV function to calculate the present value of the investment in a medical van for the Neighborhood Nurse program.

Use the desired rate of return value (cell C14) as the rate argument.

Use the payments in Years 1–6 (range C7:C12) as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)

Pranjali also wants to calculate the internal rate of return on the investment. If it is 7 percent or higher, she is confident she can attract investors.
Calculate the internal rate of return on the investment as follows:

In cell C17, enter a formula that uses the IRR function to calculate the internal rate of return for investing in a medical van for the Neighborhood Nurse program.

Use the payments for startup and Years 1–6 (range C6:C12) as the returns paid to investors.

Lighting Designs

PROJECT STEPS

Jacob Sherman is the director of sales for Lighting Designs, a store and website that caters to building contractors in five locations in the Seattle area. Jacob asks for your help in producing a sales report. He wants to analyze sales for the past year and project future sales for all the stores. To create the report, you need to import data from various sources and use the Excel Power tools.
Go to the Sales Summary worksheet, where Jacob wants to display a summary of the company’s annual sales since the first store opened in the year 2000. He has a text file that already contains this data.
Use Power Query to create a query and load data from a CSV file into a new table as follows:

Create a new query that imports data from the Support_EX19_10a_Summary.csv text file.

Edit the query to remove the Units Sold and Notes columns.

Close and load the query data to a table in cell A2 of the existing worksheet.

Go to the Previous Year worksheet, which lists the sales per month for the previous year in a table and compares the sales in a chart. Jacob imported this data from the Orders table in an Access database. He wants to track the changes in monthly sales and project the first six months of this year’s monthly sales.
Create a forecast sheet as follows to provide the data Jacob requests:

Based on the data in the range A2:B14, create a forecast sheet.

Use 6/30/2022 as the Forecast End date to forecast the next six months.

Use Six Month Forecast as the name of the new sheet.

Resize and move the forecast chart so that the upper-left corner is within cell C2 and the lower-right corner is within cell E12.

Go to the Stores worksheet. Jacob wants to display information about lighting products purchased according to store and manufacturer. He has been tracking this data in an Access database. Import the data from the Access database as follows:

Create a new query that imports data from the Support_EX19_10a_LD.accdb database.

Select the 2021_Orders, Products, and Purchases tables for the import.

Only create a connection to the data and add the data to the Data Model.

On the Stores worksheet, Jacob wants to show the categories of products sold in each of the company’s five stores during 2021.

In cell A2 of the Stores worksheet, use Power Pivot to insert a PivotTable based on the data in the 2021_Orders table.

Edit the PivotTable as follows to provide this information for Jacob:

Use the following fields from the 2021_Orders table in the PivotTable:
· Category field for the row headings
· StoreCity field for the column headings
· ItemQty field for the values

Use Products Sold as the custom name of the Sum of ItemQty field.

In cell A3, use Lighting Products to replace “Row Labels”, and then resize column A to its best fit.

In cell B2, use Stores to replace “Column Labels”.

Jacob occasionally would like to focus on the number of lighting products sold in the five stores per month.
Add a Timeline Slicer as follows to the Stores worksheet:

Insert a Timeline Slicer that uses the OrderDate field from the 2021_Orders table.

Move and resize the Timeline Slicer so it covers the range A12:G18.

Scroll the Timeline Slicer to display all periods.

Jacob also wants to examine the percentage each type of lighting product contributed to total sales in each store. Create a PivotChart as follows:

Based on the PivotTable on the Stores worksheet, create a 100% Stacked Column PivotChart.

Move and resize the PivotChart so that its upper-left corner is in cell H2 and its lower-right corner is in cell N16.

Go to the Manufacturers worksheet. Jacob wants to compare products sold by category and manufacturer. This data is stored in the Purchases and Products tables.
Create a PivotTable as follows that provides the products sold by manufacturer information for Jacob:

In cell A2, use Power Pivot to insert a PivotTable in the Manufacturers worksheet.

Use the following fields in the PivotTable:
· Manufacturer field from the Products table for the row headings
· Category field from the Products table for the column headings
· ItemQty field from the Purchases table for the values

In order to relate the data in the Products and Purchases tables to make a proper comparison, use the Power Pivot window to create a relationship between the Purchases and Products tables based on the ItemID field.

Jacob asks for another way to visualize the products sold by manufacturer. Create a PivotChart as follows:

Based on the PivotTable on the Manufacturers worksheet, create a Stacked Bar PivotChart.

Move and resize the PivotChart so that its upper-left corner is within cell I2 and its lower-right corner is within cell O25.

Hide all the field buttons in the PivotChart.

Jacob also wants to be able to focus on a single category of product at a time.
Add a slicer to the PivotChart as follows:

Add a slicer based on the Category field from the Products table.

Move the slicer below the PivotChart so that it covers the range I26:K38.

Use the slicer to filter the PivotTable and PivotChart to show only products in the Commercial category.

Hempstead University Foundation

PROJECT STEPS

Camille Darbonne is the director of the Office of Capital Projects for the Hempstead University Foundation. She uses an Excel workbook to track building projects on three Hempstead  University campuses in the state of New York. She asks for your help in summarizing project data as she prepares for a presentation to the Foundation’s board. To do so, you will use database functions and advanced PivotTable features.
Go to the Capital Projects worksheet, which contains a table named Projects listing details about the university’s building projects. In the range J2:M7, Camille wants to summarize project information.
Start by calculating the number of each project type as follows:

In cell K3, enter a formula using the COUNTIF function that counts the number of Expansion projects, checking that the column of project types in the Projects table (Projects[Project Type]) is equal to the value in cell J3.

Fill the range K4:K7 with the formula in cell K3.

In column L, Camille wants to calculate the total cost of each project type. Determine the total costs as follows:

In cell L3, enter a formula using the SUMIF function that totals the cost for Expansion projects, checking that the column of project types in the Projects table (Projects[Project Type]) is equal to the value in cell J3, and that the formula totals all the final costs (Projects[Final Cost]).

Fill the range L4:L7 with the formula in cell L3.

In column M, Camille wants to calculate the average cost of each project type. Determine the average costs as follows:

In cell M3, enter a formula using the AVERAGEIF function that averages the cost for Expansion projects, checking that the column of project types in the Projects table (Projects[Project Type]) is equal to the value in cell J3, and that the formula averages all the final costs (Projects[Final Cost]).

Fill the range M4:M7 with the formula in cell M3.

Camille needs to identify the number of projects that have a final cost of more than $100,000 and those that were completed in 2019.
Create formulas that provide this information as follows:

In cell K11, create a formula using the DCOUNT function to count the number of projects with final costs of more than $100,000, using the data in the entire Projects table (Projects[#All]) and counting the values in the column of final costs (“Final Cost”) that are equal to the values in the range J9:J10.

In cell K15, create a formula using the DCOUNTA function to count the number of projects completed in 2019, using the data in the entire Projects table (Projects[#All]) and counting the values in the column of completion dates (“Completed”) that are equal to the values in the range J13:J14.

Camille also needs to calculate the total cost of the building projects on the Newburgh campus and the average cost of the projects on the Hempstead campus.
Create formulas that provide this information as follows:

In cell K19, create a formula using the DSUM function to calculate the total cost of the Newburgh campus projects, using the data in the entire Projects table (Projects[#All]) and totaling the values in the column of final costs (“Final Cost”) that are equal to the values in the range J17:J18.

In cell K23, create a formula using the DAVERAGE function to calculate the average cost of the Hempstead campus projects, using the data in the entire Projects table (Projects[#All]) and averaging the values in the column of final costs (“Final Cost”) that are equal to the values in the range J21:J22.

Go to the Cost by Green Feature worksheet. Camille has created a PivotTable on this worksheet to list the final costs of the projects by green feature, years, and campus. She grouped the year data into two-year spans, but wants them listed as separate years.
Ungroup the year data in the PivotTable.

Camille thinks the PivotTable looks crowded in its default Compact layout.
Change the report layout to show the PivotTable in Outline Form.

Go to the Cost by Campus worksheet. Camille created a PivotTable that lists each project by ID and campus, and then displays its final cost. The data is sorted in alphabetic order by project ID, but Camille wants to sort the data from smallest to largest total amount. She also wants to focus on renovation projects only, which have an ID that begins with “RE”.
Change the display of the PivotTable as follows:

Sort the data in ascending order by Grand Total.

Apply a Label Filter that displays Project ID values that begin with RE.

Go to the Project Budgets worksheet. The PivotTable on this worksheet lists the final cost totals and the budgeted amounts by project type. Camille wants to include the number of projects for each project type in the second column of the PivotTable.
Make this change for Camille as follows:

Add the ID field to the Values area of the PivotTable, making it the first field in the Values area.

Change the number format of the Count of ID amounts to Number with 0 decimal places.

Camille wants to know the difference and the percentage of difference between the final costs and the budgeted amounts.
Provide this information for Camille as follows:

Insert a calculated field named Difference that subtracts the Budget field amount from the Final Cost field amount.

Insert another calculated field named % Difference that subtracts the Budget field amounts from the Final Cost field amounts, and then divides the result by the Budget field amount.

Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points.

Camille also wants to include the average budget for each project type as the last column in the PivotTable.
Add this information to the PivotTable as follows:

Add the Budget field to the Values area of the PivotTable, making it the last field in the Values area.

Use the Average calculation to summarize the Budget field data.

Go to the Project Types by Year worksheet. The PivotTable on this worksheet lists the final costs of the building projects by year and type. Camille wants to show the names of each project and display the information about the two project types with the highest total final cost.
Provide this information for Camille as follows:

Expand the Project Type field to show the names of each project.

Apply a Value Filter to the Project Type field that displays the top 2 items by the sum of final cost.

Penta Insurance

PROJECT STEPS

Luis Palomba is in charge of community relations for Penta Insurance, a health insurance agency in Minneapolis, Minnesota. Luis is coordinating a program that sends tutors from Penta Insurance to area elementary schools. He is developing an Excel workbook to track and record the tutoring information, and needs to complete it before the upcoming Volunteer Fair. He asks for your help in automating the workbook.
Go to the Tutor Summary worksheet and then unprotect it so you can edit the contents.

Luis wants to include an eye-catching title on the worksheet, which he shares with others at Penta Insurance.
Insert and format WordArt as follows:

Insert WordArt using the Fill: Dark Red, Accent color 1; Shadow style.

Type Penta Tutoring Program in the WordArt to make the worksheet title.

Change the text fill of the WordArt to Green, Accent 6, Darker 50%.

Move the WordArt to row 1 so that it fits within columns A:H.

Luis created a macro to insert a plain worksheet title, and then attached the macro to the Insert Title button. He no longer needs the button.
Delete the Insert Title button in the range H5:H6.

Luis plans to print the Tutor Summary worksheet before he visits tutors in the schools. He wants to use the Print Setup button to prepare the worksheet for printing.
Record a macro that sets up the worksheet for printing as follows:

Record a macro named Print_Setup stored in the current workbook.

With the macro recording, click the File tab on the ribbon, and then click Print.

Change the page orientation to Landscape Orientation.

Change the scaling setting to Fit Sheet on One Page.

Return to the Tutor Summary worksheet.

Stop recording the macro.

Luis created the Print Setup button using a Rounded Rectangle shape. He wants to be able to click the shape to run the Print_Setup macro.
Assign the Print_Setup macro to the Print Setup shape.

Go to the Tutor Records worksheet, where Luis tracks tutor assignments. He and his staff usually enter the tutoring details directly on the worksheet. He wants to make sure everyone enters the tutoring data correctly, especially the dates in the Tutoring Start Date column. The tutoring program begins on March 22, 2021 and ends on May 14, 2021.
Create a data validation rule as follows:

In the range C5:C18, create a data validation rule that accepts only date values between 3/22/21 and 5/14/21.

Create an input message that uses Tutoring Start Date as the title and the following text as the input message:
Enter the date to start tutoring.

Create a Stop style error alert that uses Invalid Date as the title and the following text as the error message:
Enter a date between March 22 and May 14.

Luis wants to make sure all of the dates entered in the Tutoring State Date column are valid.
Identify and correct any invalid values as follows:

In the range C5:C18, circle invalid data.

Change the first invalid date to 3/24/21 and change the second invalid date to 3/26/21 to correct the errors.

Go to the Tutor Registration worksheet. During the Volunteer Fair, Luis and his staff will enter tutor information using the form on this worksheet. Luis has created two macros to automate this form, but they are not working correctly.
View and edit the macros as follows:

Open the Clear_Form macro in the Visual Basic Editor. The macro should delete the values in the range C4:C8, but specifies the range C3:C7.

In the Clear_Form macro VBA code, change the statement between the “‘Edit the code below” and “‘Edit the code above” comments to the following statement:
Range(“C4:C8”).Select

Scroll down to display the code for the Add_Tutor macro, the second macro Luis created. This macro calls, or runs, the Clear_Form macro at the end of the code, so it should now work correctly. Save the code and then close the Visual Basic Editor.

Assign the Clear_Form macro to the Clear Form button so that Luis and his staff can use the button to clear the form.

Use the Clear Form button to clear the form and test the macro.

The Add_Tutor macro should copy the data in the range C4:C8 on the Tutor Registration worksheet, go to the Tutor Records worksheet, find the next blank row, and then paste the copied data, transposing it to fit in a row instead of a column. Then the macro should return to the Tutor Registration worksheet and run the Clear_Form macro, which deletes the data in the range C4:C8.
Luis wants to assign the Add_Tutor macro to a new button to make it easy to run the macro. Create the button as follows:

Insert a Button (Form Control) button in column C to the right of the Clear Form button.

Change the height to 0.3″ and the width to 1.8″.

Align the top of the new button with the top of the Clear Form button.

Edit the text to display Add Tutor on the button.

Format the button control to use Calibri 11 point text if necessary.

Assign the Add_Tutor macro to the new button.

First insert the tutor registration data shown in Table 1. Then use the Add Tutor button to run the Add_Tutor macro, and then verify that it added a record on the Tutor Recordsworksheet.

  Table 1: Tutor Registration Data

Legal Note for this New Perspectives Excel 2019 Sam projects assignment

The assignment file and details given on this page is just an example file for this Excel assignment help order. If any individual/institution believe publication of this assignment is offensive; please contact with us using Contact Form and we will get back to you in shortest possible notice.

Pure Assignment has established in Turkey. Pure Assignment is only charged of Turkish law and all matters discussed in Terms of Use and the other subtitles must be considered according to Turkish Law. According to related law (law number 5651) published in official paper;

MADDE 5 (Article number: 5)–

(1) Yer sağlayıcı, yer sağladığı içeriği kontrol etmek veya hukuka aykırı bir faaliyetin söz konusu olup olmadığını araştırmakla yükümlü değildir.

(1) The site provider is not obliged to check the content it provides or to investigate whether there is an illegal activity. (Translated by Google Translate)

(2) Yer sağlayıcı, yer sağladığı hukuka aykırı içerikten, ceza sorumluluğu ile ilgili hükümler saklı kalmak kaydıyla, bu Kanunun 8 inci ve 9 uncu maddelerine göre haberdar edilmesi halinde ve teknik olarak imkân bulunduğu ölçüde hukuka aykırı içeriği yayından kaldırmakla yükümlüdür.

(2) The site provider is obliged to remove the illegal content from the publication; if it is informed in accordance with the 8th and 9th articles of this Law. And to the extent technically possible, provided that the provisions regarding the criminal liability are reserved for the illegal content it provides. (Translated by Google Translate)

Pure assignment offers the Site and Site Services for clients to be able to share any freelance job to let them find the help from the right expert they need with their business or assignments; papers and projects; either for business purpose or educational purpose. Pure assignment aims to get clients and freelancers together to be able to help them each other. In this case, Pure assignment works as a service provider as mentioned in law (law number 5651).