Exploring Microsoft Excel 2016, Application

Prepared Exam
Exploring Microsoft Excel 2016, Application

Mark’s Construction and Realty

Mark’s Construction and Realty buys and flips houses. They have hired you to look at their sales numbers from last year to analyze the data. They have instructed you to see if there are any trends in the types of houses they typically purchase, if there is a correlation between the number of houses sold and sale date, and which sales should be included in the project managers’ year-end bonus.

Perform Preliminary Work

You need to open the existing workbook they provided, save it with a new name, and make a few preliminary modifications.

a. Open the e00_cumexam_data workbook and save the workbook as e00_cumexam_LastFirst.

b. Change the width of column E to 15.00.

c. Insert a new row above row 1.

d. Enter the text 2017 Sales Data in cell A1, change the font size to 14 and apply bold.

e. Add a fill color of Blue, Accent 1 for cell A1.

f. Merge and center the range A1:I1.

g. Freeze the headers so when you scroll down the headers are always visible.

Calculate Profit and Determine if Project is Eligible for Bonus

You are ready to calculate the total costs.

h. Insert two columns after Sale Price named Total Cost and Profit respectively.

i. Insert a function in cell H3 to calculated the total cost of each project.

j. Insert a function in cell I3 to calculated the profit of each project.

k. Create an IF statement in cell K3 where if the profit is greater than $50,000 it will display the text Yes, otherwise it will display the text No.

l. Copy the three formulas down their respective columns.

Organize Monthly Data

The company has already collected data about how many of each type of house they sold each month. You will convert the data into a table, apply a table style to make the data more visually appealing, and add total rows for use in the analysis grids later.

m. Click the Sheet2 worksheet and rename it Analysis.

n. Convert the entire dataset into a new table named Date with a table style of Table Style Medium 6.

o. Calculate a total for each house type in column N.

House Type Analysis

You will analyze each house type to determine if the company has a tendency to purchase a certain type of a house.

p. Create a 2D-Pie chart that displays the total number of houses by house type and apply chart type Style 7.

q. Move the chart to the left side of the screen directly under the table data.

r. Change the chart title to House Type.

s. Move the legend to the top-left corner.

t. Explode the largest piece of the pie to a Point Explosion of 20%

u. Add Data Callout data labels to the chart.

Sales Analysis

You will analyze the sales numbers across each month to determine in which months buyers tend to purchase houses.

v. Calculate a new total row for each month.

w. Create a 2D-Line chart that displays total house sales by Month and apply chart style Style 6 and move it to the right of the pie chart.

x. Change the chart title to Total Sales

y. Change the plotted line color to White, Background 1.

z. Add a column to the Date table after House Type called Sparkline.

aa. Add a sparkline for each house type over the 12-month period in the new Sparkline column.

Finalize the Workbook

You will make some small formatting changes.

bb. Set the page orientation to Landscape.

cc. Insert a footer with your instructor’s name on the left side, the date in the center, and your Last,First name on the right side.

dd. Save and close the workbook. Based on your instructor’s directions, submit e00_cumexam_LastFirst