CEHE APP110 Week 4 Assessment Latest 2019 December

APP 110 Business Computer Fundamentals

Week 4 Assessment

Microsoft Excel

Learning Objectives Covered:

LO 06.04 – Demonstrate how to create tables and graphs in Microsoft Excel including the use of formulas

Career Relevancy

Segment of a slide ruleIf you've seen the movie Hidden Figures, you may have noticed one of the manual calculating tools used in the movie was a slide rule (a segment is pictured here). Luckily, we do not have to use slide rules for our calculations today. For quick calculations, we can grab the calculator programmed into our phone. For more complex calculations and calculations that have to be done repetitively, we can program our spreadsheet to do the work for us using formulas. The use of formulas in a spreadsheet improve work effectiveness and efficiency in these ways:

Multiple similar calculations can be done quickly without error

The spreadsheet with formulas can be easily used by different people with consistent results

The spreadsheet formulas can be used over and over without having to do the manual calculations again simply because one or more pieces of input have changed

Tables and graphs are ways to display data so it makes sense to us. You have used tables and graphs most of your life—they have become increasingly common in media and on social media as well as in the professional world.

Background

Tables

You have already created tables if you have completed the Week 4 Assignment. A table is nothing more than data organized in rows and columns. Rows go down, columns go across. The individual spots where data is held are called cells.

To make a table more useful, we can add labels. Let's use a simple example from the business world. You are the supervisor of a small sales team. You need to track the weekly sales of your team members, Ash, Kourtney, Sherra, and Tamika. Here is the raw data:

Ash sold $1,050 in week 1, $1,125 in week 2, and $976 in week 3

Kourtney sold $1,245 in week 1, $1,175 in week 2, and $1,480 in week 3

Sherra sold $1,090 in week 1, $890 in week 2, and $1,115 in week 3

Tamika sold $1,205 in week 1, $1,225 in week 2, and $1,330 in week 3

That's a pretty confused way to look at it, so let's put together a table listing the weeks across the top and the sales people on the left side.

Ash        Kourtney             Sherra   Tamika

Week 1                                                

Week 2                                                

Week 3                                                

This is not a pretty table, but it works. We can do the same thing in Excel. It is easier to make the table work for us in Excel than on the course page. Here is the basic table in Excel.

The basic table in Excel

Now, let's add in the sales numbers.

Table with basic sales data

This is pretty easy to read compared to what we had before, but we want it to look a little clearer. We can apply some simple formatting—like you did in the Word assignments. More importantly, we can set up these cells to display the numbers as currency. Because we have a few other pieces we'll add when we put in the formulas, we'll set more than just our active cells to show numbers as currency. Start by selecting cell B2 and then, while keeping the mouse or right click active, select through cell G5. (If you struggle to make the full selection, you can also do it one cell at a time or by a smaller selection.) Your selection will look something like this:

Selection of money cells

Formulas

Your table should now look good and it is readable, but it would be nice to know what we are selling per week and how much each salesperson has sold. It would also be good to know our grand total. We can find out all of this by adding in new labels and then putting in some formulas. First, the labels. We're going to add a label for weekly totals, one for salesperson totals, and one for the grand total.

We can make this much clearer by drawing in thicker lines or changing the color of the cells. Experiment a bit with the look. (If you're stuck on how to do that, a simple Google search will give multiple ideas with instructions.)

A note about formulas, you do need to get the format correct for the formulas, but there are different ways to do them as well. For this task, we are going to go with the most basic way to help explain it, but we will show a simple way to make life easier.

First, go to the cell you want to put the formula in. We'll start with cell A1, then click on Formulas from your menu tabs. As you will see, there are several classifications of formulas available to you. Explore the formulas by clicking on the financial drop-down menu and hover over the formulas to view a description. The description provides details for when to use the formula.

Excel image - Financial

Graphs

Once your data set is complete, you can create a chart, otherwise known as a graph, to provide a visual representation of the data. The chart tool allows you to create a visual presentation of the data in your table. You can select a format that fits your goal and data set. Your options are a Area, Bar, Column, Waterfall, Pie, Line, Scatter, Surface, or Radar charts. With Excel, it is easy to create a chart by simply selecting your data set and then click Insert, then the type of chart you prefer.

Chart image

Prompt

For this assessment, we're going to create an Excel spreadsheet making a useful table, create a graph from it, and use formulas to help understand the data better.

Assessment instructions:

Download data set by clicking herePreview the document

Step 1: Add employee name data and column and row names

Add the employee name information from the data set to a blank excel file. Be sure to properly name your columns and rows for each category.

Step 2: Create additional employee data and arrange names alphabetically

Add five additional employees to the "Employee" column. You will now need to arrange your employees in alphabetical order. Select all the names in your "Employee" column and then click on the data tab. Locate the "Sort & Filter" group and click on the sort A to Z icon. This will arrange all your names alphabetically and you are ready to move on to the next step.

Step 3: Add remaining data into coordinating fields

Now that you've arranged your employees in alphabetical order you can add the absence information for each employee to the correlating month.

Step 4: Add a formula

Create a new column titled "Total days missed" at the end of the data set. Click on cell N2, then click the formulas tab. In the formulas tab click Autosum, then click Sum. The row data should now be encased in a green colored box. Hit the enter key. Click back into cell N2 and drag the green square down to N18. The sums will now be calculated for each employee.

Take a minute to review the days missed for each employee and think about how this data could be useful.

Step 5: Format as a table

Use the short key code of CRTL + A to select the data set. Click Format as table and select a table format. Next, click the My table has headers.

Step 6: Insert a Graph

Select your table, then click the Insert tab. Under the insert tab select Column or Bar chart, then 2D Stacked column. The chart will appear on the screen. In order to make it easier to view the data set and chart you will need to move the chart below the table by right-clicking on the chart and dragging to the desired location.

Step 7: Save file

Save your file as an excel file using App110_lastname_week4_assessment