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.

Looking for solution of this Assignment?

WHY CHOOSE US?

We deliver quality original papers

Our experts write quality original papers using academic databases.  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay