Fluent Essays

All calculations are written correctly and efficiently.Value(s) are not directly entered when cell reference(s) available. Using appropriate type cell references in calculations so that calculations need only be entered once and then copied whenever possible. Appropriate use of 3-D cell references.Applying formatting in a consistent and appropriate manner. Using cell styles for Title, subtitle, column titles and any total rows. Applying correct number formatting to values in sheet. Adjusting columns widths and row heights appropriately. Adjusting layout as directed.All the formula are written properly test_1_rubric.xlsx test_1_data_file.xlsx test_1_directions.docx Unformatted Attachment Preview Test 1 Grading Rubric 100 Content Area Criteria Value Part 1 Formula Writing All calculations are written correctly and efficiently. 10 Cell References Value(s) are not directly entered when cell reference(s) available. Using appropriate type cell references in calculations so that calculations need only be entered once and then copied whenever possible. Appropriate use of 3-D cell references. 7 Perform Calculations with commonly used Functions: Min, Max, Sum, Average Functions are set up properly with correct ranges used in arguments. 10 Formatting Cells/Worksheet; create new worksheet Applying formatting in a consistent and appropriate manner. Using cell styles for Title, subtitle, column titles and any total rows. Applying correct number formatting to values in sheet. Adjusting columns widths and row heights appropriately. Adjusting layout as directed. 15 Part 2 Using If Function Using CountIf Function Function set up and applied correctly with all arguments in correct order. Function set up and applied correctly with all arguments in correct order. 7 7 Part 3 PMT function Function set up and applied correctly with all arguments in correct order. 6 Function set up and applied correctly with all arguments in correct order. 7 Part 4 Use Vlookup function Part 5 Create Names Manage Names Use Names Set Page Breaks Set Print Titles Part 6 Adjust the View of a worksheet window – freeze, split Correct cell(s) are named as directed. Required edits are made to name(s) as directed and are correct as applied. (i.e. name updated and/or range being named is updated). Calculation(s) re-entered using names as directed. Page breaks set for the columns and row data as instructed Titles are set to print on each page of a multipage sheet. 5 Window is adjusted correctly. 2 Preview shows that all sheets have appropriate orientation and that scaling has been applied to meet the desired criteria. All sheets have a footer with required information. 8 5 5 3 3 Part 7 Print Prep Total Comments 0 Pts. Charlestown Appliance Parts Supply Sales Promotion Analysis Item Number Initial Item CostItem Sale Price Item Total SalesItem Total CostItem Profit 35.86 37.65 HOV23 22.49 25.19 HOV59 199.99 234.95 HOV72 17.99 18.65 HOV86 54.67 62.35 WH42 34.99 36.39 WH87 58.69 66.02 WH96 54.09 66.42 WH98 Total \% of Profit Pasadena Facility: Inventory Status of Plants As of September 30 Specialty Plant Types Quantity in Stock 95 37 58 78 93 27 78 28 24 26 56 24 49 65 175 18 28 19 56 135 45 36 52 49 23 150 42 32 52 Item 1123 1267 1843 1852 1862 2132 2157 2158 2176 2201 2202 2202 2219 2223 2266 2268 2278 2308 2317 2356 2364 2402 2404 2428 2437 2439 2543 2580 2582 Item Name Macadamia Nut Clematis Hank Fragrant Plum Banana Cream Minny Pearl September Raspberry Elite Gold Pineapple Salzia Glue Strawberry Sweet Dwarf Banana Top Hat Blueberry Magid Hybrid Sunshine Dream Purple Splash Wing Ding Tetrapleed Breeder Blanket Flower Cinnamon Tree Pope Benedict Black Pie Super Dwarf Tomato Dwarf Raspberry Hot Chocolate Elite Pineapple Cashew Tree Holiday Assortment Lavandula New Dimension Chandler Blueberry Retail Price Size 15.42 28.95 24.95 13.95 16.95 7.58 12.65 15.95 6.59 18.59 18.95 11.35 16.57 25.95 45.95 54.95 24.95 16.58 11.35 26.95 34.95 28.95 10.59 38.95 26.95 34.95 14.95 31.95 18.65 24 42 10 14 15 12 16 12 6 24 18 14 24 14 41 24 16 12 16 11 36 28 18 28 33 35 12 26 24 Status of Plants 30 Special Feature Fall Color Fall Color Showy Pollenizer Showy Fall Color Easy Care Fall Color Showy Easy Care Easy Care Pollenizer Fall Color Showy Fall Color Fall Color Easy Care Easy Care Pollenizer Fall Color Easy Care Easy Care Pollenizer Easy Care Easy Care Pollenizer Showy Pollenizer Easy Care Category Edibles Container Plants Bonsai Specialty Container Plants Edibles Specialty Specialty Container Plants Edibles Edibles Vine Roses Vine Roses Vine Roses Vine Roses Specialty Container Plants Container Plants Vine Roses Vine Roses Edibles Edibles Vine Roses Edibles Specialty Edibles Container Plants Specialty Container Plants Sale Price LOAN DATA Loan Years Annual Rate Monthly Payment $ 250,000.00 7 3.50\% Ski Factory Unlimited Product Inventory Item # 968952 658752 965888 799589 862785 779568 796689 796553 756998 796535 796895 777895 732568 795692 732256 758815 658322 995678 765325 796598 765998 735697 935623 672256 722965 995667 732569 779569 736599 622475 672758 775689 761598 789955 796699 765589 765936 980782 732655 765966 989655 795622 732589 723897 Item Name Adventure Hat Bandana Cotton Visor Explorer Hat Rainbreaker Sun Mask Sun Runner Cap Tan Sahara Vaporwick Endurance Country Hoodie Down Full Parka Foldup Rain Parka Granite Divide Iceland Windbreaker Mens Down Parka Micro Pullover Hoodie Micro Zip Hoodie Microlight Windbreaker Nylon Hoodie Salome Fast 4 Soft Shell Jacket Solar Vest Transition Sportop Vagabond Womens Deluxe Windbreaker Capri Light Desert Convertible Foldup Rain Pants Global Traveler Light 3/4 Length Mens Snow Pants Padded Bike Shorts Trailblazer Training Short Womens Snow Pants Beach Water Fast Trainer Fit and Fun Shoes Gel-Jump Mens Cross Trainers Mens Hightop Pedal Fast Super Nova Trail Runner I Category Hats Hats Hats Hats Hats Hats Hats Hats Hats Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Pants Pants Pants Pants Pants Pants Pants Pants Pants Pants Shoes Shoes Shoes Shoes Shoes Shoes Shoes Shoes Shoes Sport Quantity in Stock Retail Price Hiking 125 Hiking 35 Biking 58 Hiking 150 Hiking 75 Skiing 87 Fitness 96 Hiking 48 Fitness 56 Fitness 32 Hiking 66 Hiking 55 Skiing 62 Biking 36 Hiking 64 Fitness 46 Fitness 15 Biking 42 Biking 25 Hiking 87 Hiking 25 Hiking 18 Biking 66 Biking 36 Fitness 46 Fitness 85 Hiking 36 Hiking 62 Hiking 87 Biking 23 Skiing 10 Biking 45 Hiking 35 Fitness 44 Skiing 54 Fitness 46 Fitness 122 Fitness 95 Fitness 122 Fitness 41 Hiking 125 Biking 62 Fitness 145 Hiking 45 796897 711853 795779 778952 772569 789237 759658 793256 779635 635841 756899 769336 765332 756897 729567 956983 956897 965899 795689 769535 795635 735698 978223 796589 732956 729569 735699 732567 731195 765956 Velocity Cross-trainer Xtreme Janes Adventure Cargo Cargo Lights Fit in Motion Shorts French Tour Fundamental Woven Stretch Pacer Run Lux Select Gel Stretch Shorts Mini Crew Socks Ski Gloves Striped Crew Socks Sun Gloves Wicked Heavy Socks Womens Gripper Gloves Wool Mittens Base Layer Classic LTD Leno Hardcore Long Jersey Micro Mellon Polo Special San Diego Tee Sleeveless Tee V-Neck Tank Wicked Lite Wicked Tank Top Shoes Shoes Shorts Shorts Shorts Shorts Shorts Shorts Shorts Shorts Shorts Shorts Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Fitness Fitness Hiking Biking Biking Fitness Biking Fitness Fitness Fitness Biking Hiking Fitness Skiing Fitness Hiking Skiing Skiing Skiing Hiking Biking Hiking Biking Fitness Hiking Fitness Fitness Fitness Fitness Hiking 15 19 98 32 28 42 58 98 69 46 118 36 18 42 58 36 25 48 25 78 44 54 64 46 66 45 25 29 66 23 Total Quantity in Stock: Product Pricing Item # Retail Price 622475 49.95 635841 39.59 658322 29.95 658752 4.99 672256 110.95 672758 88.65 711853 65.89 722965 95.65 723897 89.95 729567 14.99 729569 26.33 731195 36.59 732256 179.95 732567 24.99 732568 168.35 732569 55.89 732589 110.95 732655 139.95 732956 89.45 735697 72.89 735698 65.75 735699 19.99 736599 65.89 756897 29.99 756899 29.92 756998 19.95 758815 69.95 759658 99.98 761598 44.59 765325 35.69 765332 6.45 765589 49.95 765936 115.95 765956 29.99 765966 99.99 765998 89.95 769336 49.22 769535 29.95 772569 43.95 775689 49.65 777895 29.95 778952 56.45 779568 24.98 779569 39.95 Category Pants Shorts Jackets Hats Jackets Pants Shoes Jackets Shoes Socks and Gloves Tops and Tees Tops and Tees Jackets Tops and Tees Jackets Pants Shoes Shoes Tops and Tees Jackets Tops and Tees Tops and Tees Pants Socks and Gloves Shorts Hats Jackets Shorts Pants Jackets Socks and Gloves Shoes Shoes Tops and Tees Shoes Jackets Shorts Tops and Tees Shorts Pants Jackets Shorts Hats Pants Sport Biking Fitness Fitness Hiking Biking Skiing Fitness Fitness Hiking Fitness Fitness Fitness Hiking Fitness Skiing Hiking Fitness Fitness Hiking Hiking Hiking Fitness Hiking Skiing Biking Fitness Fitness Biking Hiking Biking Fitness Fitness Fitness Hiking Fitness Hiking Hiking Hiking Biking Biking Hiking Biking Skiing Hiking 779635 789237 789955 793256 795622 795635 795689 795692 795779 796535 796553 796589 796598 796689 796699 796895 796897 799589 862785 935623 956897 956983 965888 965899 968952 978223 980782 989655 995667 995678 39.95 44.95 19.95 45.65 215.95 69.89 49.95 70.56 44.59 89.45 39.95 39.95 76.95 18.98 85.65 229.95 49.56 19.95 25.95 69.95 19.35 25.99 2.99 25.55 29.87 49.95 49.99 98.46 59.59 29.49 Shorts Shorts Pants Shorts Shoes Tops and Tees Socks and Gloves Jackets Shorts Jackets Hats Tops and Tees Jackets Hats Pants Jackets Shoes Hats Hats Jackets Socks and Gloves Socks and Gloves Hats Socks and Gloves Hats Tops and Tees Shoes Shoes Pants Jackets Fitness Fitness Fitness Fitness Biking Biking Skiing Biking Hiking Fitness Hiking Fitness Hiking Fitness Skiing Hiking Fitness Hiking Hiking Biking Skiing Hiking Biking Skiing Hiking Biking Fitness Hiking Fitness Biking Ski Factory Unlimited Item # 635841 756899 759658 769336 772569 778952 779635 789237 793256 795779 658322 672256 722965 732256 732568 735697 758815 765325 765998 777895 795692 796535 796598 796895 935623 995678 622475 672758 732569 736599 761598 775689 779569 789955 796699 995667 729569 731195 732567 732956 735698 735699 765956 769535 Item Name Run Lux Select Gel French Tour Stretch Shorts Cargo Lights Cargo Pacer Fit in Motion Shorts Fundamental Woven Stretch Adventure Micro Zip Hoodie Vagabond Womens Deluxe Windbreaker Mens Down Parka Granite Divide Solar Vest Micro Pullover Hoodie Nylon Hoodie Soft Shell Jacket Foldup Rain Parka Iceland Windbreaker Country Hoodie Salome Fast 4 Down Full Parka Transition Sportop Microlight Windbreaker Light 3/4 Length Mens Snow Pants Desert Convertible Global Traveler Trailblazer Padded Bike Shorts Foldup Rain Pants Training Short Womens Snow Pants Capri Light San Diego Tee Wicked Lite V-Neck Tank Polo Special Leno Hardcore Sleeveless Tee Wicked Tank Top Base Layer Category Shorts Shorts Shorts Shorts Shorts Shorts Shorts Shorts Shorts Shorts Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Jackets Pants Pants Pants Pants Pants Pants Pants Pants Pants Pants Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Tops and Tees Sport Fitness Biking Biking Hiking Biking Biking Fitness Fitness Fitness Hiking Fitness Biking Fitness Hiking Skiing Hiking Fitness Biking Hiking Hiking Biking Fitness Hiking Hiking Biking Biking Biking Skiing Hiking Hiking Hiking Biking Hiking Fitness Skiing Fitness Fitness Fitness Fitness Hiking Hiking Fitness Hiking Hiking 795635 796589 978223 729567 756897 765332 795689 956897 956983 965899 658752 756998 779568 796553 796689 799589 862785 965888 968952 711853 723897 732589 732655 765589 765936 765966 795622 796897 980782 989655 Classic LTD Micro Mellon Long Jersey Striped Crew Socks Ski Gloves Mini Crew Socks Wool Mittens Wicked Heavy Socks Sun Gloves Womens Gripper Gloves Bandana Vaporwick Endurance Sun Mask Tan Sahara Sun Runner Cap Explorer Hat Rainbreaker Cotton Visor Adventure Hat Xtreme Janes Trail Runner I Super Nova Gel-Jump Beach Water Fast Trainer Mens Cross Trainers Pedal Fast Velocity Cross-trainer Fit and Fun Shoes Mens Hightop Tops and Tees Tops and Tees Tops and Tees Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Socks and Gloves Hats Hats Hats Hats Hats Hats Hats Hats Hats Shoes Shoes Shoes Shoes Shoes Shoes Shoes Shoes Shoes Shoes Shoes Biking Fitness Biking Fitness Skiing Fitness Skiing Skiing Hiking Skiing Hiking Fitness Skiing Hiking Fitness Hiking Hiking Biking Hiking Fitness Hiking Fitness Fitness Fitness Fitness Fitness Biking Fitness Fitness Hiking Total Quantity in Stock 46 118 58 36 28 32 69 42 98 98 15 36 46 64 62 18 46 25 25 55 36 32 87 66 66 42 23 10 36 87 35 45 62 44 54 85 45 66 29 66 54 25 23 78 44 46 64 58 42 18 25 25 36 48 35 56 87 48 96 150 75 58 125 19 45 145 122 46 122 41 62 15 95 125 4186 Excel Hands On Test 1 Data File: FIT1040 Excel 2016 Test 1 data file Download and save the test data file on your desktop. Be sure to include your last name at the beginning of the file name. Example: Smith Excel Test 1 Once you have completed the exam, upload your spreadsheet to the Test 1 link in Ulearn. It is strongly recommended that you also save a back-up copy to your One Drive or storage media of your choice. Part 1 (chapters 1&2) Worksheet: Sales Promo Analysis Objectives: Formulas, functions, formatting -- be sure to format ALL worksheets appropriately. They should be professional looking, legible and print ready. You are an accountant for an appliance parts supply company. You have just completed a special promotion and need to analyze its success. You have been provided a file with a sheet named “Sales Promo Analysis” containing a list of items in the promotion along with each item’s cost and selling price. You now must update it with the quantity of items sold during the promotion and to determine the following calculations in steps 1 through 7. (Insert a column between the Item Numbers column and the Initial Item Cost column to contain the Quantity Sold data): Item Number HOV23 HOV59 HOV72 HOV86 WH42 WH87 WH96 WH98 Quantity Sold 44 80 67 28 55 204 103 39 Calculations needed to complete the sheet include (adjust column widths if necessary): 1. Item Total Sales (Quantity Sold times Item Sale Price). 2. Item Total Cost (Quantity Sold times Initial Item Cost). 3. Item Total Profit (Item Total Sales minus Item Total Cost). 4. Calculate the Totals for Item Total Sales, Item Total Cost and Item Profit. 5. Percent of Profit (Individual Item Profit divided by the Total Profit in cell G13) format as percent with 2 decimal places. Use absolute reference as necessary. 6. Insert a new worksheet ensuring it is the first worksheet in the workbook (to the left of the Sales Promo Analysis worksheet). 7. Label the new worksheet tab “Sales Promo Stats”. 8. Copy the worksheet title and sub-title from the Sales Promo Analysis worksheet to the new worksheet starting in cell A1. Document1 Page 1 of 3 Excel Hands On Test 1 9. Create columns using the column headings Quantity Sold, Item Total Sales, Item Total Costs, and Item Profit starting in cell B4. 10. Create row headings starting in cell A5 that use Average, Highest and Lowest as the row labels. 11. Using 3-D reference, create formulas to calculate the following based on values in the Sales Promo Analysis worksheet: o Average for all items in the Quantity Sold, Item Total Sales, Item Total Costs, and Item Profit columns. o Highest value for all items in the Quantity Sold, Item Total Sales, Item Total Costs, and Item Profit columns. o Lowest value for all items in the Quantity Sold, Item Total Sales, Item Total Costs, and Item Profit columns. Part 2 (chapter 2) Worksheet: Functions Objectives: COUNTIF, IF 1. In B4, use a function to count the number of plants in the Category “Specialty”. 2. In H8, use a logical function to determine the sale price. If the size is 24” or taller, then calculate the sale price at 30\% off (Retail Price * 70\%). Otherwise, enter “not on sale”. 3. Be sure to format your results appropriately. Part 3 (chapter 2) Worksheet: PMT Objective: PMT function 1. Calculate the monthly payment that will be needed to pay back the loan the business is considering. 2. Be sure that the payment amount calculates into a positive number. Part 4 (chapter 2) Worksheets: Product Inventory and Product Pricing sheets Objective: Lookup function Management would like the Retail Price from the Product Pricing sheet to be placed on the Product Inventory sheet. This way, if anything changes on the Product Pricing sheet, the Product Inventory sheet will be updated. 1. On the Product Inventory sheet, create a lookup function which will insert the retail price from the Product Pricing sheet based on the item #. Document1 Page 2 of 3 Excel Hands On Test 1 Part 5 (chapter 1) Worksheet: Product Inventory Objectives: Create Named Range Edit Named Range Create a formula using named ranges Set Page Break Set Print Titles • • • • • • Create a named range to include all the Quantity in Stock column data and name the range Quantity_in_Stock. Edit the named range Selling_Price to the new name of Retail_Price. DO NOT create a newly named range. In cell J1, use the appropriate function to get a total of all quantity in stock. Rather than using a cell range in your function you should be using the appropriate named range. Add a page break between the Pants and Shoes Categories. Set the print area so only columns A:G will print. Set Print Titles so row 2 will print on the top of each page. Part 6 (chapter 1) Worksheet: Split Workbook Objectives: Splitting a worksheet Because there is so much data/detail in your spreadsheet, it’s important for you to be able to split the worksheet so you can work with both Total Inventory (at the bottom of the sheet) and the top portion of your spreadsheet at the same time. • • Split the workbook at row 15 and then in the second (or bottom) window scroll until you can see the total row. Leave worksheet in this view for submission. Part 7 (chapter 1) Worksheet: All worksheets Objectives: Prepare the file for printing • • • Make sure each sheet is set so all COLUMNS print on one page. Include a footer with the filename in the left section, the current date in the center and the sheet name in the right section (HINT: Do not hard type this information). YOU DO NOT NEED TO PRINT. Upload to the Test Upload link in Ulearn. Document1 Page 3 of 3 ... Purchase answer to see full attachment