New Perspectives Excel 365/2021 | Module 5 End of Module Project 1 | Lewellen Group

New Perspectives Excel 365/2021 | Module 5: End of Module Project 1 #newperspectives #module5

If you directly want to get the project from us then contact us on Whatsapp. Click on link below👇👇
https://wa.me/message/PPXGSJS2TLWUA1

Whatsapp Contact Number:
+91 8114420233

Email ID:
[email protected]

1. "Lonnie Holtsman is a financial analyst for the Lewellen Group, a management consulting firm headquartered in Providence, Rhode Island. The firm also has offices in Charlotte, North Carolina; Milwaukee, Wisconsin; and Denver, Colorado. Lonnie is using an Excel workbook with multiple worksheets to create a profit and loss statement for the firm. He asks for your help in completing the statement.
Go to the Dashboard worksheet. Lonnie first wants to complete the list of links in the range H7:H10. In cell H7, create a hyperlink to the www.lewellen.example.net website."
Format text as a hyperlink.
2. In cell H8, create a hyperlink to the [email protected] email address. Use Contact a Lewellen consultant as the ScreenTip text.
Hyperlink to an email address.
Set the ScreenTip for a hyperlink.
3. In cell H9, create a hyperlink to cell A1 in the Categories worksheet.
Format text as a hyperlink to a worksheet cell.
4. In cell H10, create a hyperlink to the workbook Support_EX365_2021_EOM5-1_2024.xlsx, which contains Lewellen profit and loss data from 2024.
Add a hyperlink of a external workbook to a cell.
5. Lonnie wants to compare 2024 profit and loss totals to those for 2025 and needs to add the 2024 data to the Dashboard worksheet. Open the file Support_EX365_2021_EOM5-1_2024.xlsx. Copy the values in the range C6:C19. In cell C6 of the Dashboard worksheet in the original workbook, use the Paste Link command to create external references to the values in the Support_EX365_2021_EOM5-1_2024.xlsx workbook. Delete the unnecessary values in cells C8, C12, and C16, and close the Support_EX365_2021_EOM5-1_2024.xlsx workbook.
Copy and paste a range of values.
Delete cell contents.
6. Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use the name PayrollExp to define the range D9:D11. Use the name OpExp to define the range D13:D15.
Create a defined name for a range.
Create a defined name for a range.
7. In cell D18, enter a formula using the SUM function to total the expense values using the defined range names PayrollExp and OpExp.
Create a formula using a function.
8. Lonnie prefers that accountants and others contact Lewellen through the link in cell H8 rather than one that connects to his email address. In cell C21, remove the hyperlink, leaving the unlinked text "Lonnie Holtsman" in the cell.
Remove a hyperlink.
9. Lonnie wants to combine the profit and loss data for 2025 from each of the three Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C6 in the Charlotte:Milwaukee worksheets. Copy the formula from cell E6 to cell E7, the range E9:E11, the range E13:E15, and the range E17:E19. In the range E17:E19, copy the formula and number formatting only.
Create a formula using a function.
Copy a formula into a range.
Copy a formula into a range.
10. Go to the Charlotte worksheet. Lonnie needs to calculate the percent of sales for each expense item. Assign the defined name Revenue to cell C6.
Create a defined name for a cell.
11. In cell D7, enter a formula without using a function that divides the Cost of sales value in cell C7 by the revenue amount, using the defined name Revenue for the Charlotte worksheet. Copy the formula from cell D7 to the range D9:D11, the range D13:D15, and the range D17:D19. In the range D17:D19, copy the formula and number formatting only.
Create a formula without using a function.
Copy a formula into a range.
Copy a formula into a range.
12. Lonnie wants to apply consistent formatting to the worksheets he collected from the three Lewellen offices. Group the Charlotte, Denver, and Milwaukee worksheets, and then apply the Heading 1 cell style to cell B2 to use the same formatting as in the Dashboard worksheet. Ungroup the worksheets, and then verify that the formatting is applied to all three worksheets.

#newperspectives #samprojects #excelproject #excel365/2021 #module5 #samproject1b #samproject1a #eom2 #eom1 #endofmodule #eomproject2 #EOMProject1 #shellycashman #lewellengroup #samcapstone1a