MGMT 2100 - Assignment #1: Spreadsheet Skills General Instructions: For this assignment you will complete a tutorial introduction to some important features of Microsoft Excel, and then search for and write short reviews on Excel tutorials or explanations on the internet. You will also create a spreadsheet demonstrating the skills for which you reviewed tutorials or explanations. Purposes of assignment: o Practice self learning/active learning o Learn and practice some key Excel concepts that we will use in this class Please submit your assignment files to Blackboard This is an individual assignment Evaluation: This assignment is worth 50 points. Grading criteria include completeness, clarity, and accuracy. Component Excel tutorial spreadsheet Reviews of internet resources Examples of skills for which internet resources were reviewed Points 14 16 20 Explanation 2 points each for first four tabs; 6 points for last tab 4 points for each of the four tutorial reviews 5 points for each of the four spreadsheet examples Assignment Tasks: Complete the Excel tutorial posted with this assignment. Select four of the skills/concepts from the list below, preferably ones which you have not yet mastered. For each, find and use Excel tutorials or explanations to learn the skill/concept. These may be text, interactive, or YouTube videos. Provide short reviews of the tutorial or explanation that you find to be the best for each skill that you select: 1) Using the COUNTIF function 2) Difference between absolute and relative references 3) Using the SUMIF function 4) Logical functions in Excel (AND function, OR function, IF function) 5) Creating one-dimensional and two-dimensional data tables in Excel 6) Different ways to paste (default, values only, formats only, etc.) 7) Using the IF function 8) Using the VLOOKUP function 9) Using the SUMPRODUCT function 10) Using the DEVSQ function So, you should have four short reviews in total. Each review should include the following: o URL of the tutorial (ex: http://www.teachmeexcel.com/vlookup_function.html). If you used a book or Excel Help, indicate that as well. o Length of tutorial (how long it took you to go through if text; length of video, etc.) o Topic/Skills covered (You can answer this in a few words, such as \"COUNTIF function,\" \"Data Tables,\" or \"Absolute and Relative References\") o A paragraph or two describing and evaluating the tutorial (i.e. What did it show you or ask you to do? How did it help you learn? Could it be improved? Create a spreadsheet with one example for each of the four skills/concepts for which you reviewed web resources. For example, if you learned the SUMIF function you would create a simple example showing how that function is used. Please put each example on a separate tab of the spreadsheet. Assignment Deliverables: You will be turning in three files to Blackboard. For each, please include your first and last name somewhere in the filename. For example, you might name your review document something like \"mark_haney_excel_assignment.docx\": 1. Completed Excel tutorial spreadsheet 2. Document with your four reviews 3. Spreadsheet with examples of the skills covered in the resources you reviewed This assignment is a short tutorial on some basic skills and concepts in Excel. Just follow the directions in the text boxes in each tab to progress through the tutorial. First, enter your name below. Name: 1. In Excel, you can enter formulas into a cell. After entering a formula into a cell, the result of the formula is displayed in the cell. You can see the formula in the formula bar under the ribbon when you click on the cell. Another way to see the formula is to double-click the cell. All formulas in Excel start with the equals sign. Now, enter =2+2 in cell C10 below, then hit enter. The result of the formula, 4, is displayed in the cell. Practice clicking on the cell once to see the formula in the formula bar, and double-clicking to display the formula in the cell. After you double-click on the cell, hit Enter or Esc to exit the cell. If you click on another cell the cell reference will be entered into the formula! 4 2. When entering basic formulas, use * for multiplication, and ^ to introduce an exponent. In cell C17 below enter the formula =6*6, and in cell F17 enter the formula =6^2, which squares the 6. Both cells should display 36. 3. Formulas can also include cell references. If a cell reference is included in a formula, the value that is in the referenced cell is substituted into the formula. For example, enter =B24+C24 into cell E24. The number 14 should be displayed, since the values in cell B24 (6) and C24 (8) are substituted into the formula. 6 8 4. Excel also has built-in functions for many common (and some not-so-common!) operations. For example, Excel has a function to calculate a sum, and a function to calculate an average. The functions take a cell or group of cells as input, and then display the result. To specify a group of cells, use the top left and bottom right cells of the range, separated by a colon. For example, enter =SUM(B10:D10) in cell F10, and enter =AVERAGE(B13:D14) in cell F13. Now, double-click on cell F10 or F13. Note how the formula is shown, and the input cells are delineated. 1 2 3 4 6 4 6 4 6 5. If you click on a cell or select a range of cells while entering a formula, the reference for that cell or range of cells will be entered into your formula. For example, in cell F20 type =sum( , then select cells B20 and C20 with your mouse to enter the reference into your formula. Then type the closing parenthesis ) then hit enter. 5 7 6. The cell references we have used so far in formulas, such as C24 or B10 are known as "relative references." This is because Excel remembers them as relative position from the cell in which the reference was entered. For example, if you enter =C10 in cell F10 the number 5 will be displayed. The way Excel actually interprets the reference C10 when it is entered in cell F10, however, is by remembering its relative position: The cell C10 is three columns to the left, and in the same row of the cell the formula was entered into. If that formula in cell F10 is copied into any other cell, whatever is in the same row and three columns to the left will show up. For example, click on cell F10, copy it through the menu or by hitting Ctrl+c, then click on cell F12 and paste through the menu or by hitting Ctrl+v. The relative reference in the formula automatically adjusts and the number 8 is displayed in the cell. 4 5 6 9 8 7 7. Relative references make it easy to copy formulas, saving much data-entry time. For example, enter a formula in cell D21 to calculate revenue by multiplying the units sold and the price/unit for that row. Next, click on cell D21 to select it. Then, drag and drop the cell down to cell D28 by clicking on the tiny box at the bottom right of selected cell D21, keeping the mouse button pressed, and dragging down to cell D28. When you release the mouse button the formula in cell D21 will be copied into cells D22 through D28. The formula always refers to cells in the same relative position. Units Sold Price/Unit Revenue 30 $3.00 10 $14.15 12 $22.35 45 $12.00 6 $45.00 25 $125.00 15 $2.40 40 $96.50 8. In the previous tab we learned about relative references. Cell references can be made absolute (fixed) by putting a $ before the part of the reference you want to fix. For example, enter the formula =$B$11 into cell F11. $B$11 refers to cell B11 absolutely, not by relative position, so no matter where the formula in cell F11 is copied, it still refers to cell B11. Try copying the formula in cell F11 to cells F13 and H11. The reference $B$11 doesn't change, and the number 1 is displayed in all three cells. References with $ before the column and row are called "absolute references". 1 4 7 2 5 8 3 6 9 9. References can also be "mixed". In mixed references either the row or column is fixed, but not both. For example, enter =B$20 into cell F20. The dollar sign is in front of the row number, so wherever this formula is copied it will always refer to row 20. The column part is a relative reference, however, so it can change. Try copying cell F20 into cells F21 and H21 to see what happens. 2 8 4 10 6 12 10. If you don't understand relative, absolute, and mixed references after going through these examples, look at Excel's help function, or search for "types of cell references in Excel" on Google and Youtube and study some of the tutorials and references you find. Understanding and being able to work with these reference types is absolutely crucial to being able to use Excel efficiently and effectively! 11. Put formulas in the gray cells to calculate this year's revenue (units sold * unit price), and next year's projected units sold and revenue, assuming that unit prices do not change, and each product's unit sales increase by the percentage displayed in cell E9. Then, copy the formulas down to fill out the rest of the table. Remember to use relative references for the information in the table, but use an absolute reference to the projected increase percentage in cell E9. Finally, calculate the total revenue for this year and next in cells E21 and E23. When you are done you should be able to change the projected percentage increase in cell E9 and see how it affects next year's results. Projected increase in unit sales: Product A B C D E F G Units Sold This Yr 5% Unit Price 179 692 255 916 812 874 869 $12.00 $4.55 $100.00 $62.50 $19.99 $23.45 $12.79 Total revenue this year: Projected total revenue next year: This Yr Revenue Projected Units Sold Next Yr Next Yr Revenue