Microsoft Excel INTRODUCTION After you complete all of the assigned readings, activities, and exercises in the Microsoft Office Excel study unit, you’ll be ready to complete your final graded project. The project requires you to use your Excel skills to create a simple worksheet to calculate the cost of office supplies and to track their increase or decrease in cost over a two month period. Throughout this project, you’ll input specific information in an exact location, ensuring that the formulas and functions reference the proper information. You’ll then create a graph to visually display the results. You must enter all functions and formulas using cell references. If at any point in the project you simply enter a value into the cell instead of the proper function or formula, or use a value rather than cell references in a formula or function, that part of the project will be considered incorrect and points will be deducted. In working through the graded project, you’ll use many of the same functions and layouts you’ve already worked with in the study unit exercises. After completing the project you’ll send the file to the school for grading. 1 PROJECT SCENARIO The office where you work would like to track the cost of office supplies used for a two-month period. As office manager, you’re asked to compare two months’ worth of inventory and prepare a graphical representation of the comparison to show the increase or decrease in supply use. You’re given the amounts by the purchasing department and must prepare an Excel spreadsheet to be sent to the Chief Financial Officer of your company. CREATING THE WORKSHEET To begin, launch Excel and create the worksheet shown in Figure 1. Please be sure to include the shading in cell ranges A17:F17 and H17:K17. (The color choice is yours.) Save your file as Office Supplies. Highlight the range of cells A1:F1, merge the cells, and center the text. Format “January” in Arial, 12 point, bold font. Highlight the range of cells H1:K1, merge the cells, and center the text. Format “February” in Arial, 12 point, bold font. Click in cell A3. Format the text in Arial, 10 point, bold font. Format all the remaining text in row 3 in Arial, 10 point, bold font. Right align the text in the cells. Your worksheet should now look like Figure 2. Graded Project FIGURE 2—Worksheet with Data Entered In column E (cell ranges E5:E16), use an appropriate formula to calculate the total cost for each type of office supply. Format the results in the Currency style. (Note: You’ll need to create your own formulas. There are several formulas you can use, but the ones you choose must produce the correct results and you must use cell references in your formulas.) In cell E18, use an appropriate formula to calculate the grand total of column E. Format the result in the Currency style. In column F (cell ranges F5:F16), use an appropriate formula to calculate the percentage of the grand total for each type of office supply. Format the results in the Percentage style. If necessary, decrease the decimal places to round the results to the nearest whole percent. In cell F18, use an appropriate formula to calculate the total percentage. Format the result in the Percentage style and decrease the decimal places if necessary to round the result to the nearest whole percent. (Obviously, the result should equal 100%.) Repeat these procedures for column J (Total Cost) and column K (% of Grand Total) for the month of February. (Place the grand total of column J in cell J18 and the grand total of column K in cell K18.) Again, remember to use formulas and cell references. Sort the office supplies alphabetically in ascending order (A–Z). Remember: To make sure all your calculations remain correct, you must select all information in cell ranges A5:K16 before doing the sort. Format columns C and H in the Currency style if you haven’t done so yet. Type “% Change from January to February” in cell A21. In cell D21, type the formula to calculate the % change from January to February: =(J18-E18)/ABS(E18). Format the result in the Percentage style and decrease the decimal places if necessary to round the result to the nearest whole percent. Your worksheet will now look like Figure 3.
You'll get a 17.3KB .XLSX file.