Creating a Product Analysis in Excel

Creating a Product Analysis in Excel
Lesson Plan
Grading Rubric

Microsoft Excel is a program that is used by businesses to evaluate product performance and then displays it in easily read charts and graphs. For this activity, you will actually make up fictitious information on the number of products sold during a week period.

Open Excel by following the directions listed below:

Click on Start, select Programs, and the click on Microsoft Excel.

Save Excel Worksheet:

Click on File on the Menu Toolbar, then select Save As to open the Save As dialog box.
Navigate to your folder in the Save In text box by clicking the down pointing arrow.
Name the Excel Worksheet by clicking in the File Name text box, and then typing the files name "Product Analysis."
Click OK to close the Save As dialog box.

Setting up the Worksheet and Inputting Data

Open a worksheet in Microsoft Excel. In cell A1, type the title Your Business Name Product Analysis, where Your Business Name is the name of your company. Finish putting in the rest of the data in the table below into Excel. Then go back and merge your title to appear centered across your rows.

Moving Around in the Worksheet:

There are several methods that can be used to move through the worksheet:

The Down Arrow Key-Moves you down one cell at a time in the worksheet.
The Up Arrow Key-Moves you up one cell at a time in the worksheet.
The Right and Left Arrow Keys-Moves you one cell to the right or left in the worksheet.
Page Up and Page Down-Moves you up and down a page.
End Key-Moves you to the last cell in the worksheet.
Home Key-Moves you to the first cell in the worksheet.

Entering Data:

Data is entered into single cells. The cell that has been selected to enter data is called the active cell and is identified by a heavy border. Only one cell can be active at a time. To change the active cell, move the mouse pointer into a cell and just click. To enter data, simply click in the cell and then type in the information you want to appear in the cell. In this case, you would enter a letter in each cell.

Editing Data

Once you have entered data into a cell, that data can be edited by first clicking on the cell to make the cell active and then clicking on the formula bar above the worksheet. The formula bar is shown below:

This allows you to insert or delete characters in that cell. An alternate method of editing the contents of a cell is to click on the cell to make it the active cell and then press the [F2] function key at the top of the keyboard. You can delete the contents of a cell by clicking on the cell and pressing [Delete] on the keyboard.

Formatting Worksheet:

The formatting toolbar, shown below, allows you to format the contents of a highlighted cell in a number of ways. If this toolbar is not already visible on the screen, select View>Toolbars>Formatting from the menu bar.

Merging the Title:

Place your cursor in cell A1 and type the title in the cell. Do not resize the cell!!! Also, enter all the data into the worksheet before merging the title.
Click again in cell A1.
While holding the left mouse button down, drag across the cells to you reach the last column that contains your data.
Click the merge and center cells button on the formatting toolbar.
Your title should now appear centered at the top of your worksheet.

Align Cells with Center Alignment:

Select the cells that you want to align to the center.
Click the center alignment button on the Formatting Toolbar.
The letters should now be centered in each cell.

Formatting Cells Bold:

Place your cursor in the cell you wish to format bold.
Click the bold button on the Formatting Toolbar.
The letters should now appear bold in the cell.

Changing Font Color:

Select the cells that you want to change the font color of.
Click the font color button on the Formatting Toolbar.
Click on the down arrow to get a list of colors. Choose the color you want.
The letters should now be the color you selected.

Highlight Row:

Select the row that you want to highlight.
Click the fill button on the Formatting Toolbar.
Click on the down arrow to get a list of colors. Choose the color you want.
The row should now be the color you selected.

Resizing Cells:

Some of the data overflow the cell. In this case you can drag the cell out to display all the information in the cell. To do this, place your cursor on the top of the column between the two columns you want to resize (crosshairs will appear) , hold down the left mouse, and drag the cell to the right until your cell is the size you want.

The following information should be typed in the designated cells, where Product 1, Product 2, etc. is the actual name of one of your products. You will input fictitious data for number sold and the price of the items. Do not enter information in Total Sales of Week, Minimum Sold, Maximum Sold, and Average Number Sold. You will insert formulas in these cells that will calculate the data for you.

Entering Formulas

You will now enter formulas to determine the total sales of week, minimum and maximum number sold, and the average number sold. You need to be in the cell you want your calculation to be. For example, cell C12 will hold the calculation for total sales for the week. Follow the directions below to enter your first formula:

Click in cell C12.
Click the function wizard button . This will open the Paste Function dialog box.
Under the Function Category list, select All.
Under the Function Name list, select SUM from the choices.
Click OK to close the Paste Function dialog box.
The SUM dialog opens. Click in the Number 1 text box and type in the first cell you want the results to be compare with. You can also click the red arrow button shown below, then select the cells you want to include in your calculation. For this formula you want the program to look at B4:B9.

After selecting the cells, click the red arrow button again to return to the insert formula dialog box. You should now see B4:B9 in the Number 1 text box.

Click OK to close the SUM function dialog box. The lowest number should now appear in cell C15.

Continue entering formulas in the cells highlighted below. The function keywords are:

Total-SUM
Minimum-MIN
Maximum-MAX
Average-AVERAGE

Creating Charts and Graphs:

Now you will create charts and graphs that will display the information in a more user friendly format. In order to compare information graphically, you must choose the data to reflect in our graphs. You want to compare:

Total Number Sold for Week:
- To select the data, place you cursor in cell B10, press the left mouse button, and drag to select all the cells to F10.
  Click the Chart Wizard button on the standard toolbar to view the first Chart Wizard dialog box.
  Chart Type - Choose the Chart type and the Chart subtype if necessary. Click Next.
- Chart Source Data - Instead of rows, you will need to click columns to display the five different products as shown below:
- Click the Series tab at the top of the chart wizard dialog box. You want to rename Series 1, Series 2, etc. with the name of your product. Click on Series one, click in the name box, and type in the name of your product. Also include text in the Category (S) axis labels. Continue until you have all five product names entered as shown below:
- Click Next.
- Chart Options - Enter the name of the chart (Total Number of Products Sold) and titles for the X- and Y-axes.
- Click Next.
- Chart Location - Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu. Choose As object in.
- Then click finish to complete the graph
Choose the chart types that best represent the data such as bar chart or column chart. Make sure you give your charts a title and label the x and y axis. Follow the directions above to include charts for the following:
- Total Sales For Week
- Minimum Sales For Week
- Maximum Sales For Week
- Average Sales For Week
Save your document with the name Product Analysis.
Proofread and spell check you document. Have you teacher check it over, then print it out for your portfolio. Make sure you have your teacher fill in your grade for this project on you Grading Rubric.