Lab 13 Excel
Sam1
ExcelInstructions.docx8/4/2016 Due Date: _________________________
CISB11
EXCEL SPREADSHEET ASSIGNMENT
PURPOSE:
This assignment will expand your knowledge of spreadsheet operations by introducing you to some common spreadsheet tasks. Specifically, this assignment will require you to:
Use the functions Sum, Min, Max, and Average.
Perform text, cell, and image formatting.
Learn absolute and relative cell addressing.
· Create and format a chart that visually depicts a worksheet data.
· Add worksheet protection to prevent changes to the worksheet
FILE YOU WILL NEED :
SalesReport.xlsx
ASSIGNMENT :
1. If you are not doing this assignment at Mt. SAC make sure you are using Microsoft Excel 2016. Although you can use previous versions of Excel to complete this assignment, some of the assignment instructions will not match your older versions—which may make it difficult to complete the assignment. You cannot use versions of Excel before 2007 to do this assignment. If you are doing this assignment in the lab, remember that you need a blue print card to use the lab printer.
2. To access Microsoft Excel:
Click the Start button on the bottom left corner of the Taskbar.
Select "All Programs" on the Start menu, then select the "Microsoft Office 2016" folder. Next, select the Excel 2016 application.
3. An Excel search window will be displayed. Before starting this assignment, you must first save the SalesReport file to a flash drive or to your computer. Assuming you have already done so, let’s retrieve the SalesReport file from storage.
Click on the Open Other Workbooks link in the bottom left corner of the screen.
Click on the Computer word. Next, click on the Browse button. The Open dialog box will open. Use the icons displayed at the left side of the “Open” dialog box to find the location where you saved the SalesReport file. One popular location is your computer’s Desktop folder.
To open the file, find the SalesReport file and double click on it. Or, move the mouse pointer to the OPEN button on the bottom right side of the window and click on it. The SalesReport spreadsheet should now be brought into the document window.
4. About the SalesReport worksheet.
The SalesReport worksheet reports quarterly data about sales representatives for Mountie Enterprises. The basic structure of the worksheet has already been created. Your job is to complete the worksheet as instructed in the following pages.
5. Formatting the header.
Make sure the Home tab is selected at the top of the Ribbon.
To format an area of a worksheet you must first select the area to be formatted. Click on
cell B2 to select it.
Right align the text by clicking on the Align Right button (located in the Alignment
section on the Ribbon).
Increase its font size by clicking on the Font Size down arrow button (located in the Font section), and then selecting font size 14 from the drop-down list box.
Change the text style to bold and italics by selecting the Bold and Italic buttons (also
within the Font section).
6. Formatting cell width.
To increase the cell width of column A so that the full name of each sales rep is displayed, move the cursor to the gray line separating cells A and B at the very top of the worksheet. When you are positioned on the gray line, the cursor should change to a dark bold cross sign. When the cross appears, double-click. Column A expands just enough to fit the widest field in the column.
7. Calculating Gross Profit.
Gross profit is the difference between the revenue received from selling a product and the costs incurred to either purchase or manufacture it. Cell B17 indicates that the profit margin for items sold by Mountie Enterprise’s sales reps is 35%.
Select C6. Calculate the gross profit of George Bush’s sales of by typing the following formula into C6: = B6*B17. Press Enter and then move the cursor back to cell C6. You will see the value of 15,400 in C6 and the formula displayed in the formula bar.
8. Relative versus Absolute addresses.
When Excel copies a formula to another cell, it uses relative addressing. Relative addressing is a feature in which Excel automatically changes the column and row addresses in a formula to correspond to the new location of the formula. For example, the grade worksheet pictured below contains students’ grades for the first two exams. The formula in D2 adds the first two exam grades. If Excel did not utilize relative addressing and the formula in D2 was copied to D3, D3 would still contain the formula = B2+C2, and Sue Briggs would incorrectly be given the same total score as Bob Smith. Excel prevents this mistake from happening by automatically changing cell addresses in formulas to correspond to the formula’s new location. In this example, if the formula in D2 was copied to D3, Excel would recognize that the new location of the formula is one row below the original location, and change the formula to = B3+C3.
|
|
A |
B |
C |
D |
|
1 |
Name |
Exam 1 |
Exam 2 |
Total |
|
2 |
Bob Smith |
86 |
94 |
= B2+C2 |
|
3 |
Sue Briggs |
70 |
66 |
|
|
4 |
Jim North |
98 |
94 |
|
However, there are situations when you do not want Excel to use a relative reference. For example, within the SalesReport worksheet, copy the formula in C6 to C7 by selecting C6 and then dragging the small black square in the bottom right corner of C6 down to C7.
Select C7. The value of 10,800 is incorrect. Look at the formula bar. Due to relative addressing, the new formula multiplies Bill Clinton’s sales by the commission percentage of 10%. Since the copied formula’s cell location is one row below the original formula’s cell location, Excel changed the row address of both operands in the original formula to one row below their original address. Although it was correct for Excel to change the address of B6 to B7, we do not want to change the address of B17 to B18. Rather, we want to multiply each sales figure in B6 through B9 by the same gross margin value in B17. To do so, we have to change the original formula to indicate that B17 is an absolute address.
An absolute address does not change when a formula is copied to another cell. An absolute address is indicated by placing a $ sign to the left of the column and row address ($B$17).
Select C6. Change the formula in the formula bar to indicate an absolute address for B17. Next, copy the formula to cells C7 through C9. Select each of the cells from C6 through C9 to observe that Excel used relative addresses for the first cell operand and absolute addresses for the second cell operand.
9. Calculate Net Profits.
Net profit is the difference between gross profit and expenses. To calculate George Bush’s net profit for this quarter, select E6 and enter the following formula:
= C6 - D6. Press Enter and then move the cursor back to cell E6. You should see the value of 13,960 in E6 and the formula displayed in the formula bar
Now copy the formula to cells E7 through E9. Note that we want to use relative addresses for both of the formula’s operands in this situation.
10. Formatting cell color.
Since net profit is a very important category for Mountie Enterprises, Michelle Obama, the regional sales manager, wants to highlight the net profit column by giving it a colored background.
Select cells E6 through E9. Next, find the Fill Color button (located within the Font area). It looks like a bucket spilling paint. Select the down arrow to the right of the Fill Color button and select the color of your choice.
11. Calculate Commission.
Mountie Enterprises pays its sales reps a quarterly commission of 10% of the sales reps’ net profits. To calculate George Bush’s quarterly commission, select F6 and enter the following formula: = E6*$B$18. Note that we must use an absolute address for B18 so that the operand will not change when the formula is copied to other cells.
Copy the formula in F6 to cells F7 through F9. The result is the quarterly commission for each sales rep.
12. Calculate column totals.
Select B11 and then click the AutoSum button ( ∑ ) on the Ribbon (within the Editing
section). Excel automatically enters the function to sum the values of cells B6 through
B10. Press enter. The result is that B11 contains the total sales of all four sales reps.
Calculate the total gross profits, expenses, net profits, and commissions of the sales reps by copying the formula in B11 to cells C11 through F11.
Mrs. Obama wants the Total row to stand out. Format all of the cells in row 11 with boldface font by selecting the entire row and then clicking on the Bold button..
13. This would be a good time to save your worksheet. Click on the Save button ( ) located on the Quick Access Toolbar.
14. Mrs. Obama wants to know the average sales of the four sales reps.
Select cell B13, then select the Formulas tab located at the top of the Ribbon. Next,
select the Insert Function button located at the far left of the Ribbon.
Click the down arrow to the right of the “Or select a category” textbox and select
Statistical.
In the “Select a Function” textbox, select AVERAGE, then select OK.
Within the Function Arguments dialog box you will see the cell range B6:B12 displayed in the Number 1 textbox. We do not want to average this range, as it would erroneously include cell B11—which contains the total of all four sales reps. Edit the formula so that
the range is B6:B9. Then click on the OK button. The average sales of the four sales
reps is now displayed in B13.
Calculate the average gross profits, expenses, net profits and commissions of the sales reps by copying the formula in B13 to cells C13 through F13.
15. Mrs. Obama also wants the worksheet to provide the highest and lowest values in each
category. The Excel built-in functions Max and Min provide the highest and lowest values,
respectively, for a specified range of cells.
To determine the lowest value in the sales column select B14, then select the Insert
Function button. Within the “Select a function” listbox scroll down and select the MIN
function, then select OK.
The textbox to the right of the Number 1 label contains the address B13. Change the specified range in this textbox to B6:B9, then click on the OK button. The lowest sales value within the range B6 to B9 is now displayed in B14.
Determine the lowest values for gross profit, expenses, net profits and commission by copying the formula within B14 to cells C14 through F14.
Use the same steps given above to place into cells B15 through F15 the highest values for each of the five categories. Hint: You should use the same approach as you did to determine the lowest values, except you will use the MAX function rather than the MIN function.
16. Mrs. Obama wants the cells with currency data to be formatted with a leading dollar sign.
Select cells B6 through F15, then select the Home tab. Next, select the currency ($) button (located within the Number area of the Ribbon)
Eliminate the decimal points by pressing the Decrease Decimal (.00) button (also in the Number area) twice. This will get rid of the decimal part of the numbers. All of the numbers should now appear as whole numbers with dollar signs.
17. Enter your first and last name into cell A20, then save your worksheet.
18. Create a pie chart to visually depict the amount of net profit each sales rep contributed to the company’s quarterly total.
Because the Excel charting feature assumes continuous data, we must delete the row 5
line separator. Right click on the number 5 at the far left end of row 5. Then select the
Delete option in the shortcut box.
Select cells A4 through A8. Next, push down on the Control (Ctrl) key and select cells
E4 through E8.
Select the Insert tab and then select the Insert Pie or Doughnut Chart button. Select the 2-D pie chart in the top-left corner or the chart type window. This creates a pie chart depicting the net profits of each sales rep. Notice that Excel automatically created a chart title indicating the type of data being graphed and a legend indicating the sales reps being represented by each piece of the pie.
Add the data values to the chart by right-clicking within the pie chart and selecting the Add Data Labels option.
By default the chart is placed next to the data it represents. Move the chart so that the top of the chart window is in row 22 and the left side of the chart is at the far left of the worksheet. To move the chart click in the white area outside the pie chart (but still within the chart window) and, with the mouse still pressed down, drag it to the desired location.
19. Sometimes it is helpful to place textboxes on a worksheet to comment upon or explain specific data. Mrs. Obama wants to note that George Bush’s relatively low sales total is due to his being out of work for six weeks during the quarter.
Select the Text Box button (located toward the right side of the Ribbon corresponding to the Insert tab). Move the cursor to the top left corner of cell H5. Create a textbox by pushing down on the mouse and dragging the cursor to the bottom right corner of J8. Release the mouse button. The textbox should cover the range H5 through J8.
Click the top left corner inside the textbox and type the following text: George missed six weeks this quarter due to a back injury.
Next, select the Shapes button (located toward the left side of the Ribbon corresponding to the Insert tab). Within the Lines row, select the second line from the left. It has an arrow pointing downward.
Move the cursor to the far right end of G5 (so that it is touching the far left side of the textbox). Push down on the mouse and drag the cursor across cell G5 until it touches the far left side of cell G5, then release the mouse button. There should now be a line with an arrow pointing from the textbox to cell F5.
20. One of the primary advantages of a spreadsheet is the ability to conduct what-if analysis. If a
cell’s value is determined by a formula (such as Gross Profit), then whenever the data of a cell within that formula is modified (such as profit margin), Excel automatically recalculates the new value of the formula and places the new value in the cell containing the formula. For example, Mrs. Obama wants to know the amount that total gross profits, net profits, and commissions for her territory would increase if the profit margin was increased to 37%.
Note the current worksheet values for cells C10, E10 and F10 as stated below:
Gross Profits: $110, 775
Net Profits: $106,285
Commissions: $10, 629
Also note that the data values on the pie chart match the data values in the Net Profits column. Now, change the profit margin in B16 from 35% to 37% and press enter. Note the new values in cells C10, E10 and F10 are:
Gross Profits: $117,105
Net Profits: $112,615
Commissions: $11,262
Because all three of the sales categories listed above are calculated with formulas that relate, in some way, to profit margin and sales, Excel will recalculate each of these values whenever a profit margin or sales value is changed. In this case, increasing the profit margin by 2% would increase gross profits and net profits by approximately $6000 each and commissions by about $600 (assuming Sales stayed the same). Also note that the values within the pie chart also changed to reflect the new values in the spreadsheet.
21. Add Protection to the worksheet to prevent accidental or deliberate changes to the data.
Anyone who opens your workbook file can easily change the information that it contains. Sometimes you may even make accidental changes if you bump the keys on the keyboard. Applying worksheet protection prevents the worksheet contents from being altered. It does not prevent users from viewing information or formulas. It should not be considered as a method for securing confidential information.
To protect your worksheet, go to the Review tab and click Protect Sheet. Do not enter a password and do not make any changes to the default settings. Click Ok.
The Protect Sheet dialog box is shown here. Only use the password feature if you want to prevent others from unprotecting your sheet. Change the checkbox settings when you want to allow users to make specific types of changes to your workbook. The default settings allow users to select locked and unlocked cells. This means that they can click in a cell, but they cannot change the contents.
Try typing in cell E2. You should receive this message:
Notice that the Ribbon now shows Unprotect Sheet instead of Protect Sheet. Select Unprotect Sheet so that the sheet is no longer protected.
22. Unlock a cell to allow the user to type in a protected worksheet.
When you protect a worksheet all cells in a worksheet are locked by default. When worksheet protection is applied, you can’t type anywhere! Fortunately, Excel allows you to unlock some of the cells before you add worksheet protection. You will unlock cell B16 so that users can change the profit margin.
Click in cell B16. Click on Allow Users to Edit Ranges. It is found on the Review tab in the Changes group. (If this option is grayed out and not available, make sure that you unprotected your worksheet.)
Click on the New button. Click on the OK button in the New Range dialog box. Verify that Range1 is $B$16 and then press the Protect Sheet button, then press the OK button. Test cell B16 to see if it allows changes. Test the other cells.
23. Printing the worksheet. (Before printing, verify that cell B16 displays 37%.)
First, let’s view what the worksheet would look like if you printed it. Select the down arrow to the right of the Quick Access Toolbar (located above the Insert and Layout tabs). In the shortcut box, select Print Preview and Print. This will add the Print Preview and Print button to the Quick Access Toolbar and close the shortcut box.
Select the Print Preview and Print button. You will see a small image of what your spreadsheet will look like if printed. Notice that, in the default portrait mode, the textbox explaining George Bush’s low numbers is cut off. To view the entire worksheet, we need to print it in landscape mode.
By default, the spreadsheet is Portrait Orientation. Select the down arrow to the far right of the Page Orientation area (see below image). Change to Landscape Orientation. The entire worksheet will now appear on the printout.
Page Orientation options
Return to the data view by pressing “Ctrl” and “~”. Adjust the column widths once again so that the data fits into each column.
Save your work. Upload the finished spreadsheet to Canvas.
Close Excel by clicking on the Close button at the top right corner of the Window. If you used a flash drive for this assignment, remove it before leaving.
Excel 2016 Part 2 Instructions Page 1 Microsoft Excel 2016
Page 9 Microsoft Excel 2016
