question archive MOS Excel 2019 | Module 5: SAM Project 1a Prism Real Estate ANALYZE DATA WITH TABLES AND CHARTS GETTING STARTED • Open the file SAM_EX19_MOS_5a_FirstLastName_1
MOS Excel 2019 | Module 5: SAM Project 1a Prism Real Estate ANALYZE DATA WITH TABLES AND CHARTS GETTING STARTED • Open the file SAM_EX19_MOS_5a_FirstLastName_1
Subject:MS ExcelPrice: Bought3
MOS Excel 2019 | Module 5: SAM Project 1a Prism Real Estate ANALYZE DATA WITH TABLES AND CHARTS
GETTING STARTED
• Open the file SAM_EX19_MOS_5a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SAM_EX19_MOS_5a_FirstLastName_2.xltx by changing the “1” to a “2”.
o If you do not see the .xltx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
o Support_SAM_EX19_MOS_5a_Annual.txt
o Support_SAM_EX19_MOS_5a_Quarterly.csv
• With the file SAM_EX19_MOS_5a_FirstLastName_2.xltx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. Kendra Han is an administrative assistant for the Prism Real Estate Agency in Princeton, New Jersey. She is using an Excel workbook to analyze information about the firm's agents and sales. She asks for your help with importing data, working with tables, and inserting charts to complete the workbook.
Go to the Single-Family Listings worksheet, which contains listing data for single-family homes. Kendra wants to work with this data using Excel's table features. Convert the data to a table as follows:
a. Format the range A3:I28 as a table with headers using the Blue, Table Style Medium 6 style.
b. Use SingleFamily as the table name.
c. Add a Total row, which sums the values in the Asking Price column.
2. Kendra knows the SingleFamily table contains duplicate records and wants to remove them. Find and delete the duplicate records as follows:
a. In the Listing ID column (range A4:A28), apply a Duplicate Values Highlight Cells rule that formats the duplicate values using Light Red Fill with Dark Red Text.
b. Use the Remove Duplicates command to delete the duplicate records based on the values in the Listing ID column.
c. Delete the incomplete record for Listing ID LS-171.
3. The SingleFamily table is sorted by Listing ID, but Kendra wants to sort it by price, and then display data for two-story houses only. Sort and filter the table as follows:
a. Sort the SingleFamily table in ascending order by Asking Price.
b. Filter the table to show records with the Two Story Property Type only.
4. Kendra might want to print the Single-Family Listings, Condo Listings, and All Listings worksheets, which have a similar format. However, the contents of the worksheets are slightly wider than can fit on the standard paper size. Change the margins for the three worksheets as follows:
a. Group the Single-Family Listings, Condo Listings, and All Listings worksheets.
b. Change the margins to Narrow.
c. Ungroup the worksheets.
5. Go to the Condo Listings worksheet, which contains the Condos table with listings for recent condos and townhouses. Kendra wants to format the Condos table to match the SingleFamily table and remove the conditional formatting, which does not highlight meaningful information. Format the table as follows:
a. Apply the Blue, Table Style Medium 6 style to the Condos table.
b. Display Banded Rows in the table.
c. Clear the conditional formatting rule in the range F4:F20.
6. Go to the All Listings worksheet, which contains the Listings table with listings for all types of properties. Kendra wants to prepare for adding subtotals for each property type to the data, which you cannot do with a table. Sort and convert the table as follows:
a. Sort the Listings table first in ascending order by Property Type and then in descending order by Asking Price.
b. Convert the table to a normal range.
7. If Kendra prints the All Listings worksheet, she wants all the Townhouse data to stay together on the same page. She also wants to keep the Stats for Agents data private. Adjust the layout of the worksheet as follows:
a. Insert a page break before the first Townhouse record in row 31.
b. Hide columns K:L.
8. Kendra has a text file listing annual revenue data since the beginning of the agency. She wants to import the data on a new worksheet. Import the text file as follows:
a. Create a new worksheet after the All Listings worksheet. Use Annual Revenue as the worksheet name.
b. Get data from the text file Support_SAM_EX19_MOS_5a_Annual.txt, and then load it into the Annual Revenue worksheet as a table starting in cell A1.
9. The imported data lists the year and the business year in the same column, but Kendra wants them to appear in separate columns. Convert the data to separate columns as follows:
a. In the range A1:A14, use the Convert Text to Columns Wizard to separate the year from the business year.
b. Specify that the data has a Fixed width format.
c. Divide the data between the four-digit year (such as 2009) and the business year (such as 1).
d. Accept the General column data format.
e. Replace the cells in the range B1:B14 with the converted text. (Hint: Cell B1 is not blank – replace the existing data.)
f. Resize column B to a width of 15.00 to display the complete header text.
10. Kendra wants to display a chart comparing the annual revenue to the properties sold. Insert and modify a chart as follows:
a. Based on the business year, revenue, and properties sold (range B1:D14), insert a Clustered Column – Line on Secondary Axis combination chart.
b. Move and resize the chart so that the upper-left corner is in cell A16 and the lower-right corner is in cell E30.
c. Delete the Chart Title.
11. Go to the Quarterly Sales worksheet, which contains data about the quarterly sales goals for 2021 for each type of property. Kendra has a comma-separated values (CSV) text file containing the actual quarterly sales data. Import and format the data as follows:
a. Get data from the text file Support_SAM_EX19_MOS_5a_Quarterly.csv, and then load it into the existing worksheet as a table starting in cell A10.
b. Apply the Blue, Table Style Medium 6 style to coordinate with the Goals for 2021 table.
12. The Quarterly Sales Goals: Single Family and Condo chart compares data for three quarters but should compare data for four quarters. Add the Qtr 4 data series to the chart for sales goals of single-family homes and condos.
13. Kendra wants to display the data on the Quarterly Sales worksheet in three additional charts. First, insert a chart based on the actual quarterly sales as follows:
a. Based on the sales data for each quarter and each property type (range A10:E14), insert a Recommended Chart using the Stacked Column chart type that displays the four property types in each column. (Hint: Use the second stacked column chart option from the Recommended Charts.)
b. Move and resize the Stacked Column chart so that the upper-left corner is in cell A18 and the lower-right corner is in cell G34.
c. Edit the horizontal axis labels to use the text in the range A5:A8 to display more descriptive labels.
d. Delete the Chart Title.
e. Change the vertical axis major units to 1.0 to make it easier to interpret the column values.
14. Insert a second chart based on the actual quarterly sales as follows:
a. Based on the sales data for the four types of properties (range B10:E14), insert a Clustered Column chart.
b. Remove the Multifamily and Land data series from the chart to compare the sales of single-family homes and condos only.
c. Move and resize the Clustered Column chart so that the upper-left corner is in cell I18 and the lower-right corner is in cell O34.
d. Delete the Chart Title.
15. Next, insert a chart based on the sales goals for 2021 as follows:
a. Based on the quarterly sales goals for single-family homes in 2021 (range A4:B8), use the Quick Analysis button to insert a Pie chart.
b. Move and resize the Pie chart so that the upper-left corner is in cell I37 and the lower-right corner is in cell O50.
16. Go to the Profit & Loss worksheet. Kendra has created some defined names on this worksheet and asks you to add more and then complete the net profit calculations.
a. For the range D8:D10, use Payroll2022 as the defined name for the range.
b. For the range D12:D14, use OpExp2022 as the defined name for the range.
c. In cell D17, insert a formula using the SUM function that totals the values in the Payroll2022 and OpExp2022 named ranges to calculate the total expenses for 2022.
d. In cell D18, apply the defined names Profit2022 and Expenses2022 into the formula to calculate the net profit using defined names.
17. Kendra plans to provide this workbook to agents as a template they can use to track their sales. She wants to modify the document properties to make it easy to identify the file, check compatibility to alert agents who use earlier versions of Excel, and then save the workbook as a template.
a. Add Prism Agents and Sales as the Title property for the workbook.
b. Edit the Categories property so that it appears as Agents; Listings; Sales and Revenue to better identify the contents.
c. Check the compatibility of the workbook, and then click the Copy to New Sheet button to save the compatibility report on a worksheet.
d. Save the workbook as an Excel Template in its original location without changing the file name. Do not have Excel clear external data before saving the template.
Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
