Please answer only the questions that are blank. please provide formulas

Instructions: Select the Questions tab below to begin the assessment. There are 42 questions in this assessment. Place your answers directly in the spreadsheet. Warning: sharing this file with any person, company, entity, or posting it on a Website is a violation of UMUC Policy 150.25. Giving or receiving answers to the questions in this assessment is also a violation of Policy 150.25. All graduate accounting professors are required to inform the Dean's office if they suspect a student has violated Policy 150.25. The Dean's office handles the suspected student's case from that point on. Excel Assessment I. Multiple Choice Questions 1 point each 1 Onscreen text that appears when you position the mouse pointer over certain objects, such as the objects on the taskbar or a toolbar button. ScreenTips tell you the purpose or function of the object to which you are pointing. a) Screen Tip b) Sort c) Point d) Cut 2 Say that you want to paste a formula result but not the underlying formula to another cell. You would copy the cell with the formula, then place the insertion point in the cell you want to copy to. What next? a) Click the Paste button on the Standard toolbar. b) Click the arrow on the Paste button on the Standard toolbar, then click Formulas. c) Click the arrow on the Paste button on the Standard toolbar, then click Paste Special and select d) ClickValues. the arrow on the Paste button on the Standard toolbar, then click Values. 3 A color option that uses the Windows default text and background color values. a) Automatic color b) Standard color c) Mini toolbar d) Total row Excel Assessment 4 A fast way to add up this column of numbers is to click in the cell below the numbers and then: a) Click Subtotals on the Data menu. b) View the sum in the formula bar. c) Click the Toolbox and then press SUMIF+D29 d) Click the AutoSum button on the Standard toolbar, then press ENTER. 5 Which of the following is an absolute cell reference? a) G5 b) #J#9 c) B:2 d) $A$8 6 To remove data from a cell and place it on the Office Clipboard. a) Copy b) Cut c) Link d) Cell 7 On an Excel sheet the active cell is indicated by ____. a) A darker dotted border b) A darker blinking border c) A darker wide border d) None of the above Excel Assessment 8 How do you change column width to fit the contents? a) Single-click the boundary to the left of the column heading. b) Click the boundary to the left of the column heading. c) Press ALT and single-click anywhere in the column. d) Double-click the boundary to the right of the column heading. 9 ###### means: a) The cell is not wide enough to fit the number. b) You've entered a number wrong. c) You've misspelled something. d) You entered an incorrect formula. 10 Which key do you press to group two or more nonadjacent worksheets? a) CTRL b) SHIFT c) ALT d) F5 11 In order to multiply items in Excel you would use which symbol: a) ^ b) * c) x d) # Excel Assessment 12 A user wishes to remove a spreadsheet from a workbook. Which is the correct sequence of events that will do this ? a) Go to FILE - SAVE AS - SAVE AS TYPE - Excel 4.0 Work Sheet b) Right click on the spreadsheet and select INSERT - ENTIRE COLUMN c) Right click on the spreadsheet tab and select DELETE d) Right click on the spreadsheet tab and press the X key. 13 Which formula can add the all the numeric values in a range of cells, ignoring those which are not numeric, and place the result in a different cell ? a) Count b) Average c) Max d) Sum 14 __________ orientation is a worksheet whereby the page is wider than it is tall. Answer: Landscape orientation a) Diagonal b) Portrait c) Landscape d) Vertical 15 The _____________ identifies each row by a different number. a) Row headings b) Row totals c) Row averages d) Row title Excel Assessment 16 To protect a sheet, you must also: a) Insert gridlines b) Sort the first column c) Lock all or some cells d) Title each column 17 Another name for a formula in Excel is: a) Arithmetic b) Function c) Math d) Equation 18 Excel spreadsheets be used as the "data source" for: a) Adding new rows b) Adding new columns c) Mail Merge d) Filtering 19 To hide sub-rows, you would use a) Auto sum function b) Group function c) Conditional formatting d) Data consolidation 20 To create drop down lists within a cell, you would use: a) A pivot table b) Validate function Excel Assessment c) Permissions d) Page breaks Excel Assessment II. Demonstrate your Excel Skills Points vary by question 21 Create the formula needed in the yellow highlighted cell below to add the number of hours worked by all employees. Employee #: Hours Worked: 128367 40 153823 45 134879 36 173054 40 156398 42 199843 42 364921 47 292.00 Total Hours Worked Possible points = 2 Points you earned = Excel Assessment 22 Create the formula needed in the yellow highlighted cell below to compute the average of number of hours worked by all employees. Employee #: Hours Worked: 128367 40 153823 45 134879 49 173054 47 156398 37 199843 32 364921 44 42.00 Average Hours Worked Possible points = 2 Points you earned = Excel Assessment 23 Assuming 80% is the lowest passing grade, write the formula in the yellow highlighted cells below to indicate whether the student Passed or Failed. Hint: the resulting cell contents should read "Passed" or "Failed." Student Grades Earned: Passed or Failed? Alice Jade 80 PASS Answer Dan Rouge 72 FAIL Answer Steve Plum 82 PASS Answer Brittany White 68 FAIL Answer Possible points = 5 Points you earned = Excel Assessment 24 Create the formula used to compute the Total Percentage Weights in the yellow highlighted cell below. Alice Jade's Grades Percentage Weights Assignment Grades Earned % Grade Letter Grade Quizzes 20% Quiz 1 80 90-100% A Midterm Exam Project A Project B Final Exam Answer: 30% 5% 15% 30% Quiz 2 Quiz 3 Midterm Quiz 4 Project A Quiz 5 Quiz 6 Final Exam Project B 82 90 77 75 82 76 84 79 84 80-89% 70-79% Below 70% B C F Possible points = 2 Points you earned = Excel Assessment 25 Using the information in the previous question, create the formula in the yellow highlighted cell below to compute Alice Jades' final percentage grade. HINT: your answer will be a percentage grade. Answer: Possible points = 5 Points you earned = 26 Using the information in the previous question, create a VLookUp table in the blue highlighted cells and a V-Look Up formula in the yellow highlighted cell below to compute Alice Jades' final letter grade. HINT: your answer will be a letter grade that corresponds to the percentage grade in the previous question. Use UMUC Graduate Grades where A = 90-100%, B = 80-89%, C = 70-79%, F = grades below 70%. Answer: VLookUp Table Possible points = 5 Pointshighlighted you earned = below to derive a 27 Create the formula for 82 minus 32 divided by 8 times 2 plus 5 into the yellow cell value. Answer: Possible points = 2 Points you earned = Excel Assessment III. Tables and Graphs 28 Create a column chart using the data in Table A below. Group similar assignments into 3 groups in this orders: Quizzes, Exams, and Projects. Fill the Quiz bars as follows: Quizzes = Red; Projects = Purple, and Exams = Navy Blue Title the chart: Grades by Assignment Category. Set the Y axis scale from 0 to 100 in 10s and bold the font. The X axis should be grouped by assignment type, and bold the font. Assignment Table A Due Date Grade Quiz 1 Quiz 2 Quiz 3 Quiz 4 Quiz 5 Quiz 6 Project A Project B Midterm Final Exam Jan. 8 Jan. 15 Jan. 23 Jan. 30 Feb. 7 Feb. 10 Feb. 14 Feb. 21 Feb. 25 Feb. 28 82 81 90 75 75 77 84 15 72 70 Excel Assessment Answer: Possible points = 5 Points you earned = Excel Assessment Table A: Revenue by Department per Month (in Thousands) Department: Accessories Clothing Jewelry January February $18,943.43 16,302.03 35,944.34 March April May $17,223.65 $21,945.78 $25,943.93 19,443.43 21,884.73 17,990.69 32,680.64 41,640.64 49,226.84 The following two charts were created from the table above. Which Excel function can change Graph I to Graph II with one key stroke? Answer: Graph I Graph II $1 $1 $1 $1 $1 $1 $1 $1 $0 $0 $0 $0 $0 1 $0 1 Possible points = 2 Points you earned = $28,520.55 18,396.49 54,116.70 Excel Assessment Excel Assessment 30 How would you describe the areas filled in the screenshot below: Orange fill = COLUMN Answer Yellow fill = ROW Answer Blue fill = CELL Answer Possible points = 2 Points you earned = Excel Assessment 31 Currently, the two tables below are identical. Use the filter function to change the table on the right to display Accessories and Shoes only. Complete Inventory List Edit & Rename this Table Inventory # Type Details Inventory # Type Details 1300 Accessories Belts 1300 Accessories Belts 1500 Accessories Hats 1500 Accessories Hats 1600 Accessories Scarves 1600 Accessories Scarves 1800 Shoes Children's shoes 1800 Shoes Children's shoes 2700 Shoes Women's shoes 2700 Shoes Women's shoes Possible points = 4 Points you earned = 32 Use the Sort function to create two additional tables to the right of the Complete Inventory List Table below. Table 1: Inventory Sorted by Type Table 2: Inventory Sorted by Details Of the three tables, which one provides the least usefulness for inventory control purposes? Why? Complete Inventory List Inventory # Type Details 1100 Women's blouses 1200 Garnet rings 1300 Belts 1400 Children's shorts 1500 Hats 1600 Scarves 1700 Necklaces 1800 Children's shoes Inventory Sorted by Type Accessories Accessories Accessories Clothing Clothing Clothing Clothing Clothing Jewelry Excel Assessment 1900 2000 2100 2200 2300 2400 2500 2600 2700 Answer Emerald rings Men's shorts Men's bands Men's dress shirts Silver rings Women's bands Women's skirts Diamond necklaces Women's shoes Jewelry Jewelry Jewelry Jewelry Jewelry Jewelry Shoes Shoes Possible points = 4 Points you earned = Excel Assessment 33 Describe what would cause Table B to report Inventory #s as #VALUE! for the first 5 items. Answer v-lookup or function is incorrect Possible points = 3 Points you earned = Excel Assessment 34 Which Excel function was used to create the formatted table below? Revenue Reported by Manager per Month Manager January February March April May Apple, Bridget $30,309.49 $27,557.84 $35,113.25 $41,510.29 $45,632.88 Pear, Derrick 18,943.43 17,223.65 21,945.78 25,943.93 28,520.55 Banana, Kathy 33,151.00 30,141.39 38,405.12 45,401.88 49,910.96 Plum, Florence 36,371.39 33,069.41 42,135.90 49,812.35 54,759.46 Grape, Sharon 85,927.40 78,126.48 99,546.06 117,681.67 129,369.21 Peach, Ron 137,483.84 125,002.36 159,273.69 188,290.67 206,990.74 Grapefruit, Anne 35,944.34 32,680.64 41,640.64 49,226.84 54,116.70 Totals $378,130.88 $343,801.77 $438,060.43 $517,867.62 $569,300.51 Answer Possible points = 3 Points you earned = Excel Assessment 35 Which Excel feature was used to create the lower table from the top table? Answer Possible points = 2 Points you earned = Excel Assessment 36 Which Excel feature was used to create the table below? Answer Possible points = 2 Points you earned = Excel Assessment 37 Using the invoice template below, compute the total invoice amount assuming a tax rate of 7% and a discount of 4%. HINT: use formulas to compute green cell in column E. Catering Invoice Invoice #: 2175 Date: 10/15/18 Manckowitz Jewish Deli & Catering 1858 Pastrami Lane Baltimore, MD 21209 MENU ITEM Matzo Ball Soup Corned beef sandwiches UNIT PRICE $3.25 $5.99 QUANTITY 17 15 LINE TOTAL $55.25 $89.85 Whitefish salad sandwiches $5.25 20 $105.00 $2.25 45 SUB-TOTAL BEFORE DISCOUNT DISCOUNT Total TAX TOTAL $101.25 $351.35 $14.05 $337.30 $23.61 $360.91 Potato Latkes TOTAL INVOICE AMOUNT = $ 360.91 Answer Possible points = 5 Points you earned = Excel Assessment 38 Create 4 collapsible groups on this spreadsheet for the Multiple Choice, True/False, Creating Formulas, and Tables and Graphs questions. Possible points = 5 Points you earned = 39 Create a drop down list of the following items in the highlighted cells below. FAR = Financial Accounting and Reporting AUD = Auditing FAR = Financial Answer AUD = Auditing Answer REG = Taxation and Business Law BEC Business and Environmental Concepts REG = Taxation Answer BEC Business aAnswer Possible points = 5 Points you earned = 40 Add 5 additional rows above this question. Possible points = 3 Points you earned = Excel Assessment 41 Create a table similar to the one in question #24 above for this course. Enter the formula to compute your (predicted) final course percentage grade. For purposes of this assignment, enter fictitious grades for assignments in the future. Discussions Homework Excel Case Study Exams Percentage Weights Assignment 14% 11% 10% 20% 45% 100% Discussion 1 Discussion 2 Discussion 3 Discussion 4 Discussion 5 Discussion 6 Discussion 7 Homework 1 Homework 2 Homework 3 Homework 4 Homework 5 Homework 6 Homework 7 Homework 8 Homework 9 Homework 10 Homework 11 Excel Case Study Exam I Exam II Grades YOU Anticipate Earning 81.00 86.00 88.00 75.00 78.00 83.00 79.00 84.00 77.00 79.00 85.00 90.00 91.00 88.00 81.00 87.00 90.00 83.00 80.00 91.00 79.00 83.00 % Grade Letter Grade 90-100% A 80-89% B 70-79% C Below 70% F Excel Assessment Exam III Answer Your Final Course Grade = % 85.00 Possible points = 7 Points you earned = 42 Create a new Excel Assessment question (and the solution) for consideration by your professor in future semesters. Possible points = 5 Points you earned = End of Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment Excel Assessment