Preparing For The Microsoft Excel 2016 Exam

Prepared Examexploring Microsoft Excel 2016 Applicationmarks Constru

Explore and analyze the sales data for Mark’s Construction and Realty by performing a series of tasks in Microsoft Excel 2016. This includes preliminary modifications, data calculations, creating tables and charts, and final formatting to prepare the workbook for review and submission.

Paper For Above instruction

Mark’s Construction and Realty has tasked you with analyzing last year's sales data to identify trends, correlations, and determine which sales should qualify for the project managers’ year-end bonus. The process begins by opening the given dataset, making initial adjustments, and performing calculations necessary for comprehensive analysis. Subsequently, you will organize the data into tables, create visual representations through charts, and finalize the report with appropriate formatting before submitting the workbook.

First, you open the provided workbook titled e00_cumexam_data and save it with a new name, e00_cumexam_LastFirst. You then adjust the layout by changing the width of column E to 15.00 units and inserting a new row above the first row. In the newly created header, you type 2017 Sales Data in cell A1, increase its font size to 14, and apply bold formatting. To enhance visual appeal, fill A1 with Blue, Accent 1 color and merge across cells A1 through I1, centering the text. Additionally, freeze the header row so that it remains visible when scrolling through the data.

Next, you proceed to perform key calculations. You insert two new columns after the 'Sale Price' column, naming them Total Cost and Profit. In cell H3, you calculate the total cost (possibly by adding cost components or using a given formula), and in cell I3, you compute the profit by subtracting total costs from sale price. You then create an IF statement in cell K3 that labels projects as Yes if the profit exceeds $50,000, and No otherwise. These formulas are copied down the respective columns for all data entries.

Moving on to data organization, you convert the dataset in the second worksheet (renamed Analysis) into an Excel table named Date, applying the Table Style Medium 6 for clarity and aesthetic appeal. You then insert total row calculations at the bottom of the table to sum or analyze data for each house type.

The next phase involves analyzing house types through a visual representation. You create a 2D-Pie chart that visualizes the total number of each house type using Chart Style 7. This chart is shifted beneath the table data, and its title is renamed House Type. To improve readability, the legend is moved to the top-left corner, and the largest pie segment is exploded by 20%. Data callout labels are added to the chart to display specific values.

Similarly, you analyze the monthly sales data. You first compute total sales per month using a total row, then generate a 2D-Line chart that charts total house sales across months with Chart Style 6. This chart is positioned to the right of the pie chart, titled Total Sales. The line color is changed to White, Background 1 to align with the theme. To provide visual insights into sales trends, sparklines are added for each house type across 12 months in a new Sparkline column within the data table.

Finally, you prepare the workbook for presentation. The page setup is switched to Landscape orientation, and a footer is inserted containing the instructor’s name on the left, the current date centered, and your Last, First name on the right. The workbook is then saved and closed, ready for submission in accordance with the instructor's instructions.

References

  • Microsoft. (2016). Microsoft Excel 2016 Step by Step. Microsoft Press.
  • Walkenbach, J. (2015). Excel Bible. Wiley.
  • Chamberlain, S. (2017). Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. Wiley.
  • Gerlach, C. (2018). Learning Excel 2016. Packt Publishing.
  • Few, S. (2014). Show Me the Numbers: Designing Tables and Graphs to Enlighten. Analytics Press.
  • Few, S. (2009). Now You See It: Simple Visualization Techniques for Quantitative Analysis. Analytics Press.
  • Evergreen, S. (2014). Effective Data Visualization: The Right Chart for the Right Data. Sage.
  • Few, S. (2012). Information Dashboard Design: The Effective Visual Communication of Data. O'Reilly Media.
  • Heuer, F. (2009). Visualize Data with Excel 2016. Technical Publishing.
  • Microsoft. (2021). Create and Format Charts in Excel. Microsoft Support.

Related Assignments