Help with starter's OPIM

User Generated

ybhvfro11

Business Finance

Description

Hello,

Struggling to figure out the last of my OPIM homework. Please fill out the excel sheet and I attached the last of my questions in screenshots. Make sure to look at the page numbers so that they are in order of the screenshots.

Thanks!

Unformatted Attachment Preview

Reid Furniture Store Monthly Transactions: Down Payment Requirement: March 2018 25% Trans_No Operator Sales_First Sales_Last Date Department 2018-001 2018-002 2018-003 2018-004 2018-005 2018-006 2018-006 2018-007 2018-008 2018-009 2018-010 2018-011 2018-012 2018-013 2018-014 2018-015 2018-016 2018-017 2018-018 2018-018 2018-019 2018-020 2018-021 2018-022 2018-023 2018-024 2018-025 2018-026 2018-027 2018-028 2018-029 2018-029 2018-030 2018-031 2018-032 2018-033 2018-034 2018-035 2018-036 Gruenewald Gruenewald Gallagher Gallagher Gallagher Sardelis Sardelis Gallagher Desmarais Gruenewald Gallagher Desmarais Sardelis Desmarais Gallagher Desmarais Gallagher Gallagher Gruenewald Gruenewald Gallagher Gallagher Gallagher Sardelis Gallagher Gallagher Gallagher Gruenewald Desmarais Gallagher Gruenewald Gruenewald Gallagher Gruenewald Desmarais Sardelis Gruenewald Desmarais Gruenewald 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/1/2018 3/2/2018 3/2/2018 3/2/2018 3/3/2018 3/3/2018 3/3/2018 3/4/2018 3/4/2018 3/4/2018 3/4/2018 3/5/2018 3/5/2018 3/5/2018 3/6/2018 3/6/2018 3/6/2018 3/7/2018 3/7/2018 3/8/2018 3/8/2018 3/9/2018 3/9/2018 3/9/2018 3/10/2018 3/10/2018 3/11/2018 3/11/2018 3/12/2018 3/12/2018 Bedroom Bedroom Living Room Living Room Appliances Living Room Living Room Dining Room Dining Room Appliances Dining Room Dining Room Appliances Living Room Dining Room Living Room Appliances Appliances Appliances Appliances Living Room Living Room Dining Room Dining Room Living Room Dining Room Dining Room Living Room Dining Room Appliances Appliances Appliances Living Room Dining Room Dining Room Appliances Appliances Appliances Appliances KRM RKM MAP MAP MAP COK COK MAP COK KRM MAP COK KRM COK COK MAP MAP MAP KRM KRM RHB RHB RHB KRM RMP MRP COK KRM COK COK KRM KRM MAP KRM COK RHB RHB COK COK Sebastian Sebastian Jade Jade Jade Ambrose Ambrose Jade Chantalle Sebastian Jade Chantalle Ambrose Chantalle Jade Chantalle Jade Jade Sebastian Sebastian Jade Jade Jade Ambrose Jade Jade Jade Sebastian Chantalle Jade Sebastian Sebastian Jade Sebastian Chantalle Ambrose Sebastian Chantalle Sebastian Reid Furniture Store Monthly Transactions: Down Payment Requirement: Trans_No Operator Furniture Pay_Type Trans_Type 2018-001 2018-002 2018-003 2018-004 2018-005 2018-006 2018-006 2018-007 2018-008 2018-009 2018-010 2018-011 2018-012 2018-013 2018-014 2018-015 2018-016 2018-017 2018-018 2018-018 2018-019 2018-020 2018-021 2018-022 2018-023 2018-024 2018-025 2018-026 2018-027 2018-028 2018-029 2018-029 2018-030 2018-031 2018-032 2018-033 2018-034 2018-035 2018-036 Finance Finance Finance Finance Finance Finance Finance Finance Finance Finance Finance Finance Paid in Full Finance Paid in Full Finance Paid in Full Finance Finance Finance Paid in Full Paid in Full Paid in Full Finance Paid in Full Finance Finance Paid in Full Finance Finance Finance Finance Finance Finance Paid in Full Paid in Full Paid in Full Finance Paid in Full Promotion Promotion Promotion Promotion Promotion Promotion Promotion Promotion Promotion Promotion Standard Standard Promotion Standard Standard Standard Standard Promotion Promotion Promotion Standard Standard Standard Standard Standard Standard Promotion Standard Standard Promotion Promotion Promotion Promotion Promotion Promotion Standard Promotion Standard Promotion KRM RKM MAP MAP MAP COK COK MAP COK KRM MAP COK KRM COK COK MAP MAP MAP KRM KRM RHB RHB RHB KRM RMP MRP COK KRM COK COK KRM KRM MAP KRM COK RHB RHB COK COK Mattress Mattress Sofa, Loveseat, Chair Package End Tables Washer and Dryer Sofa, Loveseat, Chair Package Sofa, Loveseat, Chair Package Dining Room Table Dining Room Table Washer and Dryer Dining Room Table and Chairs Dining Room Table and Chairs Washer Recliners Dining Room Table and Chairs Sofa, Loveseat, Chair Package Dishwasher Refrigerator, Oven, Microwave Combo Refrigerator, Oven, Microwave Combo Refrigerator, Oven, Microwave Combo Sofa End Tables Bar Stools Dining Room Table and Chairs Sofa Dining Room Table and Chairs China Hutch Sofa, Loveseat Dining Room Table and Chairs Refrigerator Dryer Dryer Sofa, Loveseat Dining Room Table and Chairs China Hutch Dishwasher Refrigerator Washer and Dryer Microwave Amount 2,788 3,245 10,000 1,000 2,750 12,000 12,000 3,240 4,080 2,750 6,780 10,000 1,100 2,430 4,550 6,784 640 8,490 6,780 6,780 2,500 950 425 10,000 1,732 8,560 3,240 7,690 5,000 1,574 1,624 1,624 7,500 5,000 1,225 640 1,574 3,100 155 Trans_No Operator Sales_First Sales_Last Date Department 2018-037 2018-038 2018-039 2018-040 2018-041 2018-042 2018-043 2018-044 2018-045 2018-046 2018-046 2018-047 2018-047 2018-048 2018-049 2018-050 2018-051 2018-052 2018-053 2018-054 2018-055 2018-056 2018-057 2018-058 2018-059 2018-060 2018-061 2018-062 2018-063 2018-064 2018-065 2018-066 2018-067 2018-069 2018-070 2018-071 2018-072 2018-073 2018-074 2018-075 2018-076 2018-077 2018-078 2018-079 2018-080 Gallagher Gallagher Gallagher Desmarais Sardelis Sardelis Gruenewald Sardelis Sardelis Gallagher Gallagher Sardelis Sardelis Gruenewald Gruenewald Gruenewald Gallagher Gruenewald Gallagher Gallagher Desmarais Desmarais Sardelis Gruenewald Gallagher Gruenewald Desmarais Gallagher Desmarais Gruenewald Sardelis Gruenewald Gallagher Desmarais Sardelis Sardelis Sardelis Desmarais Gallagher Sardelis Gruenewald Desmarais Desmarais Sardelis Desmarais 3/13/2018 3/13/2018 3/13/2018 3/13/2018 3/14/2018 3/14/2018 3/14/2018 3/15/2018 3/15/2018 3/15/2018 3/15/2018 3/16/2018 3/16/2018 3/16/2018 3/17/2018 3/17/2018 3/18/2018 3/18/2018 3/19/2018 3/19/2018 3/19/2018 3/19/2018 3/20/2018 3/20/2018 3/20/2018 3/20/2018 3/21/2018 3/21/2018 3/21/2018 3/21/2018 3/22/2018 3/22/2018 3/22/2018 3/23/2018 3/23/2018 3/23/2018 3/23/2018 3/24/2018 3/24/2018 3/24/2018 3/24/2018 3/24/2018 3/24/2018 3/24/2018 3/25/2018 Dining Room Dining Room Appliances Appliances Dining Room Living Room Dining Room Living Room Appliances Dining Room Dining Room Living Room Living Room Appliances Appliances Dining Room Dining Room Appliances Dining Room Appliances Dining Room Living Room Dining Room Appliances Dining Room Dining Room Appliances Dining Room Dining Room Appliances Living Room Appliances Living Room Dining Room Living Room Appliances Bedroom Living Room Living Room Living Room Bedroom Bedroom Bedroom Bedroom Living Room MAP KRM KRM RHB COK COK KRM KRM KRM COK COK RHB RHB RHB RHB RHB COK COK MAP MAP COK COK KRM KRM MAP KRM COK COK MAP MAP KRM KRM RHB KRM KRM KRM KRM COK MAP MAP COK RHB RHB COK MAP Jade Jade Jade Chantalle Ambrose Ambrose Sebastian Ambrose Ambrose Jade Jade Ambrose Ambrose Sebastian Sebastian Sebastian Jade Sebastian Jade Jade Chantalle Chantalle Ambrose Sebastian Jade Sebastian Chantalle Jade Chantalle Sebastian Ambrose Sebastian Jade Chantalle Ambrose Ambrose Ambrose Chantalle Jade Ambrose Sebastian Chantalle Chantalle Ambrose Chantalle Trans_No Operator Furniture Pay_Type Trans_Type 2018-037 2018-038 2018-039 2018-040 2018-041 2018-042 2018-043 2018-044 2018-045 2018-046 2018-046 2018-047 2018-047 2018-048 2018-049 2018-050 2018-051 2018-052 2018-053 2018-054 2018-055 2018-056 2018-057 2018-058 2018-059 2018-060 2018-061 2018-062 2018-063 2018-064 2018-065 2018-066 2018-067 2018-069 2018-070 2018-071 2018-072 2018-073 2018-074 2018-075 2018-076 2018-077 2018-078 2018-079 2018-080 Paid in Full Paid in Full Finance Paid in Full Finance Finance Finance Paid in Full Paid in Full Finance Finance Paid in Full Paid in Full Finance Finance Paid in Full Paid in Full Paid in Full Paid in Full Paid in Full Paid in Full Finance Paid in Full Paid in Full Finance Finance Finance Finance Finance Finance Finance Paid in Full Finance Finance Finance Paid in Full Finance Finance Paid in Full Paid in Full Finance Finance Paid in Full Finance Finance Standard Standard Standard Standard Promotion Promotion Standard Promotion Promotion Standard Standard Promotion Promotion Standard Standard Standard Standard Standard Standard Standard Promotion Standard Standard Standard Promotion Standard Promotion Promotion Promotion Promotion Standard Standard Standard Promotion Promotion Standard Promotion Standard Promotion Standard Promotion Promotion Promotion Promotion Standard MAP KRM KRM RHB COK COK KRM KRM KRM COK COK RHB RHB RHB RHB RHB COK COK MAP MAP COK COK KRM KRM MAP KRM COK COK MAP MAP KRM KRM RHB KRM KRM KRM KRM COK MAP MAP COK RHB RHB COK MAP Bar Stools Kitchen Table and Chairs Refrigerator, Oven, Microwave Combo Dishwasher Dining Room Table and Chairs Recliners China Hutch Recliners Microwave Dining Room Table and Chairs Dining Room Table and Chairs End Tables End Tables Dishwasher Refrigerator, Oven Combo Bar Stools Kitchen Table and Chairs Washer and Dryer China Hutch Refrigerator, Oven Combo Kitchen Table and Chairs Sofa, Loveseat, Chair Package Kitchen Table and Chairs Microwave Dining Room Table and Chairs Dining Room Table and Chairs Refrigerator, Oven, Microwave Combo Dining Room Table and Chairs China Hutch Refrigerator, Oven, Microwave Combo Sofa Refrigerator Sofa, Loveseat, Chair Package Kitchen Table and Chairs Sofa, Loveseat, Chair Package Dishwasher Bedroom Furniture Set Sofa, Loveseat, Chair Package Loveseat Sofa, Loveseat Bedroom Furniture Set Bedroom Furniture Set Bedroom Furniture Set Bedroom Furniture Set Recliners Amount 900 1,424 6,700 625 12,458 3,240 2,545 3,240 225 7,500 7,500 1,424 1,424 450 3,280 495 875 3,140 1,824 3,280 1,172 12,500 975 125 7,540 9,430 7,500 6,000 3,125 10,000 3,450 2,575 8,400 1,345 12,500 450 12,150 17,500 1,255 4,275 7,525 4,200 4,200 3,240 2,424 Trans_No Operator Sales_First Sales_Last Date Department 2018-081 2018-082 2018-083 2018-084 2018-085 2018-086 2018-087 2018-088 2018-089 2018-090 2018-091 2018-092 2018-093 2018-094 2018-095 2018-096 2018-097 2018-098 2018-099 2018-100 2018-101 2018-102 2018-103 Gallagher Gruenewald Sardelis Gallagher Gruenewald Sardelis Gallagher Desmarais Sardelis Gallagher Sardelis Desmarais Sardelis Sardelis Gruenewald Gruenewald Gruenewald Gallagher Gallagher Desmarais Gruenewald Desmarais Gruenewald 3/25/2018 3/25/2018 3/25/2018 3/25/2018 3/26/2018 3/26/2018 3/26/2018 3/26/2018 3/26/2018 3/27/2018 3/27/2018 3/28/2018 3/28/2018 3/28/2018 3/29/2018 3/29/2018 3/29/2018 3/30/2018 3/30/2018 3/30/2018 3/31/2018 3/31/2018 3/31/2018 Bedroom Bedroom Bedroom Bedroom Dining Room Bedroom Bedroom Bedroom Bedroom Dining Room Bedroom Living Room Bedroom Bedroom Living Room Bedroom Bedroom Dining Room Bedroom Bedroom Bedroom Bedroom Living Room MAP KRM KRM MAP RHB KRM RHB RHB KRM MAP COK MAP MAP MAP COK COK COK RHB RHB COK KRM MAP KRM Jade Sebastian Ambrose Jade Sebastian Ambrose Jade Chantalle Ambrose Jade Ambrose Chantalle Ambrose Ambrose Sebastian Sebastian Sebastian Jade Jade Chantalle Sebastian Chantalle Sebastian Trans_No Operator Furniture Pay_Type Trans_Type 2018-081 2018-082 2018-083 2018-084 2018-085 2018-086 2018-087 2018-088 2018-089 2018-090 2018-091 2018-092 2018-093 2018-094 2018-095 2018-096 2018-097 2018-098 2018-099 2018-100 2018-101 2018-102 2018-103 Finance Finance Finance Finance Paid in Full Paid in Full Paid in Full Paid in Full Paid in Full Paid in Full Finance Finance Paid in Full Paid in Full Finance Finance Finance Finance Finance Finance Finance Paid in Full Finance Standard Standard Promotion Promotion Standard Standard Promotion Standard Standard Standard Standard Standard Standard Promotion Standard Promotion Standard Promotion Standard Promotion Promotion Promotion Standard MAP KRM KRM MAP RHB KRM RHB RHB KRM MAP COK MAP MAP MAP COK COK COK RHB RHB COK KRM MAP KRM Mattress Bedroom Furniture Set Bedroom Furniture Set Mattress China Hutch Bedroom Furniture Set Mattress Bedroom Furniture Set Bedroom Furniture Set Kitchen Table and Chairs Bedroom Furniture Set Loveseat Mattress Mattress Sofa, Loveseat, Chair Package Bedroom Furniture Set Bedroom Furniture Set China Hutch Mattress Bedroom Furniture Set Bedroom Furniture Set Mattress End Tables Amount 1,200 4,211 3,211 3,240 3,275 8,340 1,425 4,125 11,972 975 8,340 2,325 2,000 3,245 14,275 3,285 14,321 2,480 1,425 11,234 5,773 2,000 2,505 Reid Furniture Monthly Transactions: Down Payment Requirement: March 2018 25% Trans_No Date Sales_First Sales_Last Department 2018-024 2018-059 2018-046 2018-010 2018-062 2018-014 2018-007 2018-025 2018-098 2018-053 2018-038 2018-090 2018-037 2018-051 2018-021 3/6/2018 3/20/2018 3/15/2018 3/2/2018 3/21/2018 3/3/2018 3/1/2018 3/7/2018 3/30/2018 3/19/2018 3/13/2018 3/27/2018 3/13/2018 3/18/2018 3/5/2018 Jade Jade Jade Jade Jade Jade Jade Jade Jade Jade Jade Jade Jade Jade Jade Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Furniture Pay_Type Trans_Type Dining Room Table and Chairs Dining Room Table and Chairs Dining Room Table and Chairs Dining Room Table and Chairs Dining Room Table and Chairs Dining Room Table and Chairs Dining Room Table China Hutch China Hutch China Hutch Kitchen Table and Chairs Kitchen Table and Chairs Bar Stools Kitchen Table and Chairs Bar Stools Finance Finance Finance Finance Finance Paid in Full Finance Finance Finance Paid in Full Paid in Full Paid in Full Paid in Full Paid in Full Paid in Full Standard Promotion Standard Standard Promotion Standard Promotion Promotion Promotion Standard Standard Standard Standard Standard Standard Amount Down_Pay 8,560 7,540 7,500 6,780 6,000 4,550 3,240 3,240 2,480 1,824 1,424 975 900 875 425 2,140.00 1,885.00 1,875.00 1,695.00 1,500.00 4,550.00 810.00 810.00 620.00 1,824.00 1,424.00 975.00 900.00 875.00 425.00 Owed 6,420.00 5,655.00 5,625.00 5,085.00 4,500.00 2,430.00 2,430.00 1,860.00 - Kindle File Edit View Go Tools Help Sun 3:52 PM Q Dorothy's Kindle for Mac - Exploring Microsoft Office Excel 2016 Comprehensive (Exploring for Office 2016 Series) Home Data Review Vil Library < Back 128% Page 283 of 826 Header Table Name: Jiep u. Design up Tell me what you want to do... Formulas Data Review View Design Insert Page Layout Formulas in Summarize with PivotTable F. Remove Duplicates Insert Convert to Range Slicer ✓ fx Owed Total Ro Table of Contents ✓ Header Row First Column ✓ Filter Button Totals Refresh File Home Insert Page Layout Table Name Summarize with Pivot Table Totals - Remove Duplicates Resize Table Convert to Range Properties Tools Properties LG Open in Browser Total Row Last Column Banded Insert Slicer Export Refresh c5 Unlink ✓ Banded Rows Banded Columns Q Chapter Three Charts: Depicting Data Visually K5 External Table Data Table Style Options B25 : Steps a-b: Table Name box A B с D E Chapter Four Datasets and Tables: Managing ... B с D E F G H 1 J K MI 1 Reid Furniture 2 Monthly Transactions: 3 Down Payment Requirement: A 1 Reid Furniture Store 2 Monthly Transactions: 3 Down Payment Requirement: Excel 2016, Windows 10, Microsoft Corporation March 2018 25% Case Study Reid Furniture Store March 2018 25% 4 4 Large Datasets 5 Trans_No Operator Sales_Fir: Sales_Las Date Departme 6 2018-001 KRM Sebastian Gruenewald 3/1/2018 Bedroom 7 2018-002 RKM Sebastian Gruenewald 3/1/2018 Bedroom Furniture Mattress Mattress PayType Trans Tyr Finance Promotion Finance Promotion Amou 2,788 3,245 Hands-On Exercises 1 Large Datasets FIGURE 4.18 Rename the Table Excel Tables Hands-On Exercises 2 Excel Tables a. Click the Design tab and click the Table Name box in the Properties group. b. Type Totals in the Table Name box and press Enter. When a table is created, Excel assigns the default name "table” and a sequential number based on the number of tables in the document. For example, if there were two tables in the document the default name for the second table would be "Table2.” In this step you have added a custom name that will be used throughout the rest of the project. Table Manipulation Hands-On Exercises 3 Table Manipulation STEP 3 » ADD AND DELETE FIELDS The original range included a column for the data entry operators' initials. You will delete this column because you do not need it for your analysis. In addition, you want to add a field to display down payment amounts in the future. Refer to Figure 4.19 as you complete Step 3. Table Aggregation and Conditional Formatt... Hands-On Exercises 4 Table Aggregation and C... X Die Reid untistice Design Time what you wantede Data View 5 Trans_No 6 2018-001 7 2018-002 8 2018-003 9 2018-004 10 2018-005 11 2018-006 12 2018-007 13 2018-008 14 2018-009 15 2018-010 16 2018-011 17 2018-012 18 2018-013 19 2018-014 20 2018-015 21 2018-016 22 2018-017 23 2018-018 24 2018-019 25 2018-020 26 2018-021 27 2018-022 28 2018-023 29 2018-024 30 2018-025 31 2018-026 32 2018-027 33 2018-028 34 2018-029 35 2018-030 36 2018-031 37 2018-032 38 2018-033 39 2018-034 40 2018-035 41 2018-036 42 2018-037 43 2018-038 44 2018-039 45 2018-040 46 2018-041 47 2018-042 Date Sales_First 3/1/18 Sebastian 3/1/18 Sebastian 3/1/18 Jade 3/1/18 Jade 3/1/18 Jade 3/1/18 Ambrose 3/1/18 Jade 3/1/18 Chantalle 3/1/18 Sebastian 3/2/18 Jade 3/2/18 Chantalle 3/2/18 Ambrose 3/3/18 Chantalle 3/3/18 Jade 3/3/18 Chantalle 3/4/18 Jade 3/4/18 Jade 3/4/18 Sebastian 3/5/18 Jade 3/5/18 Jade 3/5/18 Jade 3/6/18 Ambrose 3/6/18 Jade 3/6/18 Jade 3/7/18 Jade 3/7/18 Sebastian 3/8/18 Chantalle 3/8/18 Jade 3/9/18 Sebastian 3/9/18 Jade 3/10/18 Sebastian 3/10/18 Chantalle 3/11/18 Ambrose 3/11/18 Sebastian 3/12/18 Chantalle 3/12/18 Sebastian 3/13/18 Jade 3/13/18 Jade 3/13/18 Jade 3/13/18 Chantalle 3/14/18 Ambrose 3/14/18 Ambrose Sales_Last Gruenewald Gruenewald Gallagher Gallagher Gallagher Sardelis Gallagher Desmarais Gruenewald Gallagher Desmarais Sardelis Desmarais Gallagher Desmarais Gallagher Gallagher Gruenewald Gallagher Gallagher Gallagher Sardelis Gallagher Gallagher Gallagher Gruenewald Desmarais Gallagher Gruenewald Gallagher Gruenewald Desmarais Sardelis Gruenewald Desmarais Gruenewald Gallagher Gallagher Gallagher Desmarais Sardelis Sardelis Department Bedroom Bedroom Living Room Living Room Appliances Living Room Dining Room Dining Room Appliances Dining Room Dining Room Appliances Living Room Dining Room Living Room Appliances Appliances Appliances Living Room Living Room Dining Room Dining Room Living Room Dining Room Dining Room Living Room Dining Room Appliances Appliances Living Room Dining Room Dining Room Appliances Appliances Appliances Appliances Dining Room Dining Room Appliances Appliances Dining Room Living Room Furniture Mattress Mattress Sofa, Loveseat, Chi End Tables Washer and Dryer Sofa, Loveseat, Chi Dining Room Table Dining Room Table Washer and Dryer Dining Room Table Dining Room Table Washer Recliners Dining Room Table Sofa, Loveseat, Ch Dishwasher Refrigerator, Oven Refrigerator, Oven Sofa End Tables Bar Stools Dining Room Table Sofa Dining Room Table China Hutch Sofa, Loveseat Dining Room Table Refrigerator Dryer Sofa, Loveseat Dining Room Table China Hutch Dishwasher Refrigerator Washer and Dryer Microwave Bar Stools Kitchen Table and Refrigerator, Oven Dishwasher Dining Room Table Recliners Autism Er Wap Tor Gene Chapter Objectives Review Home Insert Page Layout Formulas хса Cli 11 . Pe Copy Format Painter BIU.S.A. Clipboard Exploring Series Share 47 Sort & Find & Filter Select Paste 5. Mentor $$ Key Terms Matching A23 2018-017 D E Conditional Formats Call Ingat Delta Fomat Formatting Table Styles Cle Styles Deletes Step b: Select to delete Ex Delete Sheet Bous Delete Sheet Columns Operator field 3x D Table Rows * Delete Table Colon Step d: Down_Pay Delete Sheet field added M N Reid Furniture Store 2 Monthly Transactions: Down Payment Requirement: Excel 2016, Windows 10, Microsoft Corporation Multiple Choice March 2013 Step f: Click to apply formats from cell 15 to 35:K5 25% Amo Down Step e: Owed field added Practice Exercises Trans_NoSales_FiSales La Date Department Furniture 6 2018-001 Sebastian Gruenewald 3/1/2013 Bedroom Mattress ► 2018-002 Sebastian Gruenewald 3/1/2018 Bedroom Mattress 2015-003 Jade Gallagher 3/1/2018 Living Room Sofa, Loveseat. Chair Package PayType Trans_TY Finance Promotion Finance Promotion Finance Promotion 2,783 3.245 10,000 Mid-Level Exercises FIGURE 4.19 Newly Created Fields Beyond the Classroom a. Click any cell containing a value in the Operator column. You need to make a cell active in the field you want to remove. b. Click the Home tab, click the Delete arrow in the Cells group, and then select Delete Table Columns. Excel deletes the Operator column and may adjust the width of other columns. Capstone Exercise Chapter Five Subtotals, PivotTables, and Pivot... Hands-On Exercise 2 283 March Totals March Individual + touc:.. TAL-LICA.I.... II-:- Edit 36% Page 283 of 826 70 OCT 164 15 HIJ TUI 21 .... Lr Х all XLSX Kindle File Edit View Go Tools Help Sun 4:11 PM Dorothy's Kindle for Mac - Exploring Microsoft Office Excel 2016 Comprehensive (Exploring for Office 2016 Series) Home Insert Page Layout Formulas Data Review Vi Library < Back 131% Page 314 of 826 > Connections À 2t Az Clear Properties Table of Contents Get External DN Sort Filter Refresh All Advanced Data Edit Links STEP 3 fx Q H114 Chapter Three Charts: Depicting Data Visually » SPECIFY TOP/BOTTOM RULES Vicki is now interested in identifying the highest three sales transactions in March. Instead of sorting the records, you will use the Top/Bottom Rules conditional formatting. Refer to Figure 4.49 as you complete Step 3. A B C D E E Chapter Four Datasets and Tables: Managing ... 1 Reid Furniture 2 Monthly Transactions: 3 Down Payment Requirement: 4 March 2018 25% Case Study Reid Furniture Store Large Datasets ū Hands-On Exercises 1 Large Datasets Excel Tables 5 Trans_No 28 2018-024 31 2018-059 33 2018-046 34 2018-010 36 2018-062 37 2018-014 40 2018-007 41 2018-025 44 2018-098 45 2018-053 50 2018-038 54 2018-090 56 2018-037 57 2018-051 59 2018-021 110 111 Date Sales_First 3/6/18 Jade 3/20/18 Jade 3/15/18 Jade 3/2/18 Jade 3/21/18 Jade 3/3/18 Jade 3/1/18 Jade 3/7/18 Jade 3/30/18 Jade 3/19/18 Jade 3/13/18 Jade 3/27/18 Jade 3/13/18 Jade 3/18/18 Jade 3/5/18 Jade Sales _Last Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Department Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room 1 Furniture Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl China Hutch China Hutch China Hutch Kitchen Table and Kitchen Table and Bar Stools Kitchen Table and Bar Stools 1100 14275 Step a: Select Amount values Trans No Date Sales First Sales last Department Furniture Pay_Jype Trans_Type Amolnt Down Pay Owed 89 2018-084 3/25/2018 Jade Gallagher Bedroom Mattress Finance Promotion 3,240 810.00 2.430.00 90 2018-085 3/26/2018 Sebastian Gruenewald Dining Roor Edit Formatting Rule X dard 3,275 3,275.00 91 2018-086 3/26/2018 Ambrose Sardelis Bedroom dard 8,340 8,340.00 92 2018-087 3/26/2018 Jade Gallagher Bedroom Select a Rule Type motion 1,425 1.425.00 93 2018-088 3/26/2018 Chantalle Desmarais Bedroom Formataill cells based on their values hdard 4,128 4,125.00 94 2018-089 3/26/2018 Ambrose Sardelis Bedroom Format only cells that contain ndard 11.972 11.972.00 95 2018-090 3/27/2013 Jade Gallagher Dining Roon - Porast only top or bottom inte value dard 975 - Format only values that are above or below average 975.00 96 2018-091 3/27/2018 Ambrose Sardelis Bedroom - Format only unique or duplicate values dard 8,340 2.085.00 6.255.00 97 2018-092 3/28/2018 Chantalle Desmarais Living Room - Use a formula to determine which cells to format hdard 2.325 581.25 1.743.75 98 2018-093 3/28/2018 Ambrose Sardelis Bedroom hdard 2.000 2.000.00 99 2018-094 3/28/2018 Ambrose Sardelis Bedroom Edit the Rule Description: motion 3,245 3.245.00 100 2018-095 3/29/2018 Sebastian Gruenewald Living Room Format values thatrank in the hdard 14.275 3,568.75 10,706.25 101 2018-096 3/29/2018 Sebastian Gruenewald Bedroom Top 3 % of the selected range motion 3.285 821.25 2,463.75 102 2018-097 3/29/2018 Sebastian Gruenewald Bedroom ndard 14,321 3.580 25 10.740.75 1103 2018-098 3/30/2018 Jade Gallagher Dining Room motion 2.480 620.00 1.860.00 104 2018-099 3/30/2018 Jade Gallagher Bedroom 1.425 Preview 356.25 1,068.75 nos 2018-100 3/30/2018 Chantalle Desmarais format Аавьсету Bedroom motion 11.234 2.808.50 8.425.50 106 2018-101 3/31/2018 Sebastian Gruenewald Bedroom motion 5.773 1.443.25 4.329.75 107 2018-202 OK 3/31/2013 Chantalle Desmarais Bedroom Cancel motion 2.000 2.000.00 108 2018-103 3/31/2018 Sebastian Gruenewald Uving Room End Tables Finance Standard 2.50s 62625 1.878.75 109 2018-104 3/31/2018 Ambrose Sardelis Appliances Refrigerator Paid in Full Standard 1.500 1.500.00 110 Total Step c: Set number 190,602.50 278,656.50 of top values Step d: Conditional 1112 formatting applied 113 March Totals March Individual Hands-On Exercises 2 Excel Tables dard Table Manipulation Hands-On Exercises 3 Table Manipulation Excel 2016, Windows 10, Microsoft Corporation Table Aggregation and Conditional Formatt... Hands-On Exercises 4 Table Aggregation and C... Ready 112 113 114 115 116 FIGURE 4.49 Top 3 Amounts Conditionally Formatted Chapter Objectives Review 117 118 119 Key Terms Matching 120 a. Select the range 16:1109, the range containing the amounts. b. Click Conditional Formatting in the Styles group, point to Top/Bottom Rules, and then select Top 10 Items. The Top 10 Items dialog box opens. c. Click the arrow to display 3 and click OK. d. Scroll through the worksheet to see the top three amounts. Save the workbook. Multiple Choice Practice Exercises Mid-Level Exercises 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 Beyond the Classroom Capstone Exercise Chapter Five Subtotals, PivotTables, and Pivot... March Totals March Individual + --C!.. TAL-LILA-..-:-. II-:--..:.:. 40% Ready Page 314 of 826 70 OCT 165 15 UTUDI :) 21 .... Lr Х (ll C XLSX Kindle File Edit View Go Tools Help Sun 4:10 PM Q Dorothy's Kindle for Mac - Exploring Microsoft Office Excel 2016 Comprehensive (Exploring for Office 2016 Series) Home Insert Page Layout Formulas Data Review Vi Library < Back 130% Page 300 of 826 > Connections 28 AZ Clear Properties Table of Contents Sort Z A Filter Get External Data Advanced Refresh All Edit Links Х 1114 fx Q Chapter Three Charts: Depicting Data Visually STEP 6 » APPLY A NUMBER FILTER Vicki is considering giving a bonus to employees who sold high-end dining room furniture during a specific time period (3/16/2018 to 3/31/2018). You want to determine if Jade Gallagher qualifies for this bonus. In particular, you are interested in how much gross revenue she generated for dining room furniture that cost at least $5,000 or more. Refer to Figure 4.36 as you complete Step 6. A B с D E E Chapter Four Datasets and Tables: Managing ... 1 Reid Furniture 2 Monthly Transactions: 3 Down Payment Requirement: March 2018 25% Case Study Reid Furniture Store Reid Furniture Store Monthly Transactions: Down Payment Requirement: March 2013 235 Steps b-d: Amount greater than or equal to $5,000 4 Step b: Click to set filter Large Datasets ū Pay Type Trans_ Promotion X ? Promotion Standard Standard Standard Ambie Down 7500 1.885 00 6.000 1 500 00 3.560 2.140 00 7.500 1 875 00 6,730 1.695.00 Ow 5,655 00 4.500.00 6.420.00 5 625 00 5.085.00 Hands-On Exercises 1 Large Datasets Excel Tables Step d: Enter value Cancel Date Sales_First 3/6/18 Jade 3/20/18 Jade 3/15/18 Jade 3/2/18 Jade 3/21/18 Jade 3/3/18 Jade 3/1/18 Jade 3/7/18 Jade 3/30/18 Jade 3/19/18 Jade 3/13/18 Jade 3/27/18 Jade 3/13/18 Jade 3/18/18 Jade 3/5/18 Trans_NoSales_Fit Sales Later Date Department Furniture 14 2015-059 Jade Gallagher 3/20/2015 Dining Custom AutoFilter 19 2018-052 Jade Gallagher 3/21/2018 Dining 48 2018-024 Jade Gallagher 3/6/2015 Dining Show tows where 51 2018-046 Jade Gallagher 3/15/2018 Dining Amount S3 2015-010 Jade Gallagher 3/2/2018 Dining is greater than or equal to 000 110 ind og 111 112 113 Use to represent any single chade 114 Unts represent series of character 115 OX 116 5 Trans_No 28 2018-024 31 2018-059 33 2018-046 34 2018-010 36 2018-062 37 2018-014 40 2018-007 41 2018-025 44 2018-098 45 2018-053 50 2018-038 54 2018-090 56 2018-037 57 2018-051 59 2018-021 110 111 112 113 114 115 Sales_Last Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Department Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Furniture Dining Room Table Dining Room Table Dining Room Table Dining Room Table Dining Room Table Dining Room Table Dining Room Table China Hutch China Hutch China Hutch Kitchen Table and Kitchen Table and Bar Stools Kitchen Table and Bar Stools Hands-On Exercises 2 Excel Tables Excel 2016, Windows 10, Microsoft Corporation Table Manipulation 110 119 120 123 122 Step d: Indicates number of records found March Totals March individual Hands-On Exercises 3 Table Manipulation Sef 104 beds found Jade FIGURE 4.36 Filtered to Amounts Greater Than or Equal to $5,000 Table Aggregation and Conditional Formatt... a. Select the range 114:1108 of the filtered list and then view the status bar. The average transaction amount is $3,754 with 15 transactions (i.e., 15 filtered records). Hands-On Exercises 4 Table Aggregation and C... b. Click the Amount filter arrow. 116 Chapter Objectives Review 117 118 119 Key Terms Matching c. Point to Number Filters and select Greater Than Or Equal To. The Custom AutoFilter dialog box opens. d. Type 5000 in the box to the right of is greater than or equal to and click OK. Save the workbook. When typing numbers, you can type raw numbers such as 5000 or formatted numbers such as $5,000. Out of Gallagher's original 15 dining room transactions, only 5 transactions (one-third of her sales) were valued at $5,000 or more. 120 121 122 123 124 125 Multiple Choice Practice Exercises 126 127 Mid-Level Exercises 128 Beyond the Classroom 129 130 131 132 133 134 135 Capstone Exercise Chapter Five Subtotals, PivotTables, and Pivot... 136 March Totals March Individual + touc:.. TAL-LICA.I.... II-:- Ready 38% Page 300 of 826 70 OCT 165 15 HUTUDI 21 ... Lr Х all C XLSX Kindle File Edit View Go Tools Help Sun 4:10 PM Q Dorothy's Kindle for Mac - Exploring Microsoft Office Excel 2016 Comprehensive (Exploring for Office 2016 Series) Home Insert Page Layout Formulas Data Review Vi Library < Back 131% Page 301 of 826 > Connections A + AZ Clear Properties Table of Contents Sort Z A Filter Get External Data Advanced Refresh All STEP 7 Edit Links » APPLY A DATE FILTER Finally, you want to study Jade Gallagher's sales records for the last half of the month. You will add a date filter to identify those sales records. Refer to Figure 4.37 as you complete Step 7. Х Q H114 fx Chapter Three Charts: Depicting Data Visually A B с D E II Chapter Four Datasets and Tables: Managing ... 7500 1 Reid Furniture 2 Monthly Transactions: 3 Down Payment Requirement: E G H K M N O March 2018 25% Case Study Reid Furniture Store C Reid Furniture Store Monthly Transactions Down Payment Requirement: 4 March 2018 25% Step a: Click to set filter Large Datasets ū PayType Trans_TY Finance Promotion Finance Promotion Amod Payme 7,540 1.385.00 6.000 1.500.00 Ow 5,655.00 4_500.00 Hands-On Exercises 1 Large Datasets 2x Excel Tables Step c: Enter start date Date Sales_First 3/6/18 Jade 3/20/18 Jade 3/15/18 Jade 3/2/18 Jade 3/21/18 Jade 3/3/18 Jade 3/1/18 Jade 3/7/18 Jade 3/30/18 Jade 3/19/18 Jade 3/13/18 Jade 3/27/18 Jade 3/13/18 Jade 3/18/18 Jade 3/5/18 Jade 5 Trans_No 28 2018-024 31 2018-059 33 2018-046 34 2018-010 36 2018-062 37 2018-014 40 2018-007 41 2018-025 44 2018-098 45 2018-053 50 2018-038 54 2018-090 56 2018-037 57 2018-051 59 2018-021 110 111 112 113 Sales_Last Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Department Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Furniture Dining Room Table Dining Room Table Dining Room Table Dining Room Table Dining Room Table Dining Room Table Dining Room Table China Hutch China Hutch China Hutch Kitchen Table and Kitchen Table and Bar Stools Kitchen Table and Bar Stools Trans No Sales FlSales La Date Department IT Furniture 14 2018-059 Jade Gallagher 3/20/2018 Dining Room Dining Room Table and Chairs 1912012-062 Jade Gallagher 1/21/2013 Dining Room Dining Room Table and Chairs 110 Custom Autofilter 112 Steps b-d: Dates between Show rows where 113 3/16/2018 and Date 114 3/31/2018 after or equal to 1/2018 115 And og 116 is before or equal to 331/2010 117 113 Use to represent any single chwader 119 Use to represent series of character 1120 121 122 123 124 Step d: Two records found Hands-On Exercises 2 Excel Tables Step d: Enter end date Excel 2016, Windows 10, Microsoft Corporation 0 Cancel Table Manipulation March Totals March individual Hands-On Exercises 3 Table Manipulation Rady of 104 cds found utage Count FIGURE 4.37 Filtered by Dates Between 3/16/2018 and 3/31/2018 Table Aggregation and Conditional Formatt... a. Click the Date filter arrow. Hands-On Exercises 4 Table Aggregation and C... b. Point to Date Filters and select Between. 114 115 116 Chapter Objectives Review 117 118 119 Key Terms Matching The Custom AutoFilter dialog box opens. The default comparisons are is after or equal to and is before or equal to, ready for you to enter the date specifications. c. Type 3/16/2018 in the box on the right side of is after or equal to. You specified the starting date of the range of dates to include. You will keep the And option selected. d. Type 3/31/2018 in the box on the right side of is before or equal to. Click OK. Gallagher had only two dining room sales greater than $5,000 during the last half of March. Multiple Choice 120 121 122 123 124 125 Practice Exercises 126 e. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel. 127 Mid-Level Exercises 128 Beyond the Classroom 129 130 131 132 133 134 135 Capstone Exercise Chapter Five Subtotals, PivotTables, and Pivot... 136 March Totals March Individual + touc:.. TAL-LICA.I.... II-:- Ready 38% Page 301 of 826 70 OCT 165 15 UTUDI 21 .... Lr X all C XLSX Kindle File Edit View Go Tools Help Sun 4:11 PM Dorothy's Kindle for Mac - Exploring Microsoft Office Excel 2016 Comprehensive (Exploring for Office 2016 Series) Home Insert Page Layout Formulas Data Review Vi Library < Back 131% Page 315 of 826 > Connections À + AZ Clear Properties Table of Contents Get External DN Sort Filter Refresh All Advanced Data Edit Links fx Q H114 Chapter Three Charts: Depicting Data Visually STEP 4 > DISPLAY DATA BARS Vicki wants to compare all of the down payments. Data bars would add a nice visual element as she compares down payment amounts. Refer to Figure 4.50 as you complete Step 4. A B C D E E Chapter Four Datasets and Tables: Managing ... 1 Reid Furniture 2 Monthly Transactions: 3 Down Payment Requirement: 4 Share March 2018 25% Case Study Reid Furniture Store Dild 47 Large Datasets ū O- Hands-On Exercises 1 Large Datasets Excel Tables 5 Trans_No 28 2018-024 31 2018-059 33 2018-046 34 2018-010 36 2018-062 37 2018-014 40 2018-007 41 2018-025 44 2018-098 45 2018-053 50 2018-038 54 2018-090 56 2018-037 57 2018-051 59 2018-021 110 111 Date Sales_First 3/6/18 Jade 3/20/18 Jade 3/15/18 Jade 3/2/18 Jade 3/21/18 Jade 3/3/18 Jade 3/1/18 Jade 3/7/18 Jade 3/30/18 Jade 3/19/18 Jade 3/13/18 Jade 3/27/18 Jade 3/13/18 Jade 3/18/18 Jade 3/5/18 Jade Sales _Last Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Gallagher Department Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room Dining Room 1 Furniture Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl Dining Room Tabl China Hutch China Hutch China Hutch Kitchen Table and Kitchen Table and Bar Stools Kitchen Table and Bar Stools wo theid stitch Home Insert Page Layout Formu Data Review View Design Tell me what you want to do Exploring Series -11 AA Wrap Tot Autosum- Accounting Copy . Vegeu Format Paint 5.%. Conditional Format Cell Insert Delete Poemet Sort & Find a Thule Styles Clear Select Font Coin Highlight Colle Roles F{PayTypelepaid in Full[Amount).Amount"50531 Trans. No Date Sales. First Sales last Department furniture PayType Low Bottom Rules own PayOwed M N 2018-032 3/25/2018 Sebastian Gruenewald Bedroom Bedroom Furniture Set Finance Gradient 3.25 2018-083 3/25/2018 Ambrose Sardelis Bedroom Bedroom Furniture Set Finance Dets Bars 3.25 19 2015-084 3/25/2018 Jade Gallagher Bedroom Mattress Finance 0.00 90 2018-085 3/26/2018 Sebastian Gruenewald Dining Room China Hutch Paid in Full Color Scales Step b: Select 91 2015-086 3/26/2018 Ambrose Sardella Bedroom Bedroom Furniture Set Paid in Full this data bar 92 2018-087 3/26/2018 Jade Gallagher Bedroom Mattress Paid in Full Icon Sets 93 2015-088 3/26/2018 Chantalle Desmarais Bedroom Bedroom Furniture Set Paid in Full Maple Data 94 2015-039 1/26/2018 Ambrose Sardelis Bedroom Bedroom Furniture Set Paid in Full Bewut Add a colore te bar to represent 95 2015-090 3/27/2015 Jade Gallagher Dining Room Kitchen Table and Chairs Pald in Full Show Rules the vic. The higher the Walk the longer the 96 2016-091 3/27/2018 Ambrose Sardells Bedroom Bedroom Furniture Set Finance @ Manage Bet 97 2015-092 3/28/2013 Chantalla Desmarais Living Room Loveseat Finance 3.75 90 2018-093 3/28/2018 Ambrose Sardelis Bedroom Mattress Paid in Full Standard 2.000 More Rules 99 2013-094 3/28/2018 Ambrose Sardelis Bedroom Mattress Paid in Full Promotion 3.245 3.245.00 100 2015-098 3/29/2018 Sebastian Gruenewald Living Room Sofa, Loveseat, Chair Package Finance Standard 14.275 3,568.75 10,706 25 101 2018-096 3/29/2018 Sebastian Gruenewald Bedroom Bedroom Furniture Set Finance Promotion 3,255 $21.25 2.463.75 103 2018-097 3/29/2015 Sebastian Gruenewald Bedroom Bedroom Furniture Set Finance Standard 14,121 3.580.25 10.740.75 10 2013-098 3/30/2018 Jade Gallagher Dining Room China Hutch Finance Promotion 2.480 620.00 1.860.00 104 2015-099 3/30/2015 Jade Gallagher Bedroom Mattress Finance Standard 1425 356 25 1.068.75 Step b: Data bars 105 2015-100 3/30/2018 Chantalle Desmarais Bedroom Bedroom Furniture Set Finance Promotion 11.294 2.808-30 $425.50 applied to values 106 2018-101 3/31/2013 Sebastian Gruenewald Bedroom Bedroom Furniture Set Finance Promotion 5,773 1.443.25 4.329.75 107 2018-102 3/31/2018 Chantalle Desmarais Bedroom Mattress Paid in Full Promotion 2,000 2,000.00 108 2015-103 3/31/2013 Sebastian Gruenewald Living Room End Tables Finance Standard 2.SOS 626 25 1,878.75 109 2015-104 3/31/2018 Ambrose Sardelis Appliances Refrigerator Paid in Full Standard 1.500 1.500.00 110 Total 469,259 190,602.50 278,656.50 Hands-On Exercises 2 Excel Tables Table Manipulation Hands-On Exercises 3 Table Manipulation Excel 2016, Windows 10, Microsoft Corporation Table Aggregation and Conditional Formatt... Hands-On Exercises 4 Table Aggregation and C... March Total March individual 112 113 114 115 116 Ready Iar 189272 Count 04 FIGURE 4.50 Data Bars Conditional Formatting Chapter Objectives Review 117 118 119 Key Terms Matching 120 Multiple Choice a. Select the range J6:J109, which contains the down payment amounts. b. Click Conditional Formatting in the Styles group, point to Data Bars, and then select Purple Data Bar in the Gradient Fill section. Scroll through the list and save the workbook. Excel displays data bars in each cell. The larger bar widths help Vicki quickly identify the largest down payments. However, the largest down payments are identical to the original amounts when the customers pay in full. This result illustrates that you should not accept the results at face value. Doing so would provide you with an inaccurate analysis. Practice Exercises Mid-Level Exercises 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 Beyond the Classroom Capstone Exercise Chapter Five Subtotals, PivotTables, and Pivot... March Totals March Individual + --C!.. TAL-LILA-..-:-. II-:--..:.:. 40% Ready Page 315 of 826 70 OCT 165 15 UTUDI :) 21 .... Lr Х all C XLSX
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Content

Related Tags