Create an excel file as required

Anonymous
timer Asked: Nov 3rd, 2018
account_balance_wallet $20

Question Description

Please read and follow the instructions attached below. All necessary files are attached. A video is also attached, use this video to compare the final product to.

Unformatted Attachment Preview

Item Code FH6002 HQ4028 HQ4030 HQ4033 HQ5002 RQ5010 TQ2003 TT6002 RQ5011 TU6002 TU6003 TU6004 TU6005 Department Lotions Lotions Lotions Lotions Lotions Lotions Toothpaste & Toothbrushes Toothpaste & Toothbrushes Lotions Toothpaste & Toothbrushes Toothpaste & Toothbrushes Toothpaste & Toothbrushes Toothpaste & Toothbrushes TB4021 Lotions BU6017 Hair Care BU6018 Hair Care Body Wash & Soaps Body Wash & Soaps Body Wash & Soaps BR5007 BQ2001 BR5008 Item Name Alba Botanica Hawaiian Sunscreen Spray SPF 50 Amlactin Moisturizing Lotion Aveeno Moisturizing Lotion Cetaphil Moisturizing Cream Cetaphil Moisturizing Lotion Cetaphil Skin Cleanser Colgate Advanced Optic White Toothbrush Colgate Total Advanced Toothpaste Coppertone Sport Sunscreen SPF 50 Spray & Lotion Crest 3D White Advanced Mouthwash Crest 3D White Advancted Toothpaste Crest 3D White White Strips Crest ProHealth Mouthwash Daily Roc Retinol Correxion Moisturizer or Night Cream Dove Advance Moisture Conditioner Dove Advanced Moisture Shampoo Dove Deep Moisture Body Wash Dove Moisturizing Bar Dove Sensitive Skin Bar Soap Cost Selling Price 2018 Sold To Date 2018 Sales Goal 2017 Sales Q1 2017 Sales Q2 2017 Sales Q3 2017 Sales Q4 $5.60 $15.61 $14.99 $12.06 $18.24 $5.30 $15.99 $17.89 $15.61 $15.99 $18.99 $19.99 2822 2436 3875 2126 3484 2605 1578 2256 268 255 513 433 522 563 386 459 285 631 426 583 244 673 564 499 554 379 475 358 655 274 442 437 $5.20 $14.99 2473 2121 525 633 369 318 $5.40 $12.99 1154 2700 485 665 617 581 $5.10 $16.99 2927 1782 289 441 503 317 $5.40 $9.99 1339 2300 392 481 619 508 $7.08 $14.99 2507 2012 483 398 457 412 $19.00 $39.99 3280 2395 623 580 302 578 $9.49 1283 2140 412 643 291 515 $24.08 $29.99 2894 2222 262 550 608 513 $8.99 $17.49 2735 1612 386 314 465 237 $8.99 $26.59 1081 1997 319 463 369 586 $5.40 $14.99 2299 1551 245 227 301 576 $9.22 $15.99 1695 2341 670 438 428 500 $14.19 $15.99 1803 2573 407 540 604 687 $3.25 2006 2319 2112 2235 TB4024 Razors & Refills TP1011 Razors & Refills TP1010 Razors & Refills TP1014 Razors & Refills TP2004 Razors & Refills TP2006 Razors & Refills TP2009 Razors & Refills Toothpaste & Toothbrushes TU6006 BU6019 RQ5012 BR5009 Hair Care Lotions Body Wash & Soaps BR5010 Hair Care Toothpaste & Toothbrushes Body Wash & Soaps BQ1001 Beauty EU5020 Hair Care EU5024 BT5005 RQ5017 Hair Care Body Wash & Soaps Lotions RQ5018 Lotions EU5018 TU6007 Gillette Custom Plus 3 Disposable Razors Gillette Fusion ProGlide 2-in-1 Shave Gel Gillette Fusion ProGlide Power Cartridges Gillette Fusion ProShield Razor + 9 Cartridges Gillette Mach 3 Signature Razor + 16 Cartridges Gillette Venus Embrace Snap Razor + 11 Cartridges Gillette Venus Spa Razor + 12 Cartridges Glide Pro-health Advanced Floss Head & Shoulders Complete Scalp Care Hydroxatone Anti-Aging BB Cream Irish Spring Bar Soap Kirkland Signature 5% Minoxidil Foam For Men Kirkland Signature Antiseptic Mouth Rinse Kirkland Signature Body Soap Kirkland Signature Daily Facial Towelettes Kirkland Signature Moisture Conditioner Kirkland Signature Moisture Shampoo Kirkland Signature Natural Citrus Body Wash Lubriderm Moisture Lotion Daily Neutrogena Beach Defense SPF 70 Spray & Lotion $9.22 $25.99 2192 1634 248 606 337 230 $5.50 $11.49 1978 1677 578 417 262 202 $20.00 $44.99 1850 1784 235 317 536 464 $24.48 $34.97 1987 2465 638 606 223 677 $9.93 $31.99 1220 2419 594 244 609 657 $10.94 $32.99 1994 1473 206 324 545 206 $13.06 $29.99 3768 1852 226 279 523 583 $3.25 $12.99 3340 2134 545 624 473 214 $3.25 $11.99 $10.94 $19.99 3245 2619 2060 1948 496 511 501 392 456 565 339 226 $8.99 2948 1846 468 468 263 407 $18.97 $44.99 1260 1948 433 242 378 641 $5.30 $3.25 $7.89 1265 2592 648 523 625 458 $3.25 $9.99 3789 2229 536 495 217 691 $5.60 $11.99 1601 2204 433 527 428 529 $7.49 $13.86 2342 2475 576 635 448 494 $9.99 $23.35 3091 2019 591 284 576 305 $9.99 $20.99 $8.51 $13.49 2042 1914 2035 2083 403 675 460 498 319 245 588 394 $7.09 $14.99 3137 1964 598 362 399 349 BQ1002 BT5006 Beauty Body Wash & Soaps TB4010 Lotions TB4012 EU5025 EU5026 BU6015 Lotions Hair Care Hair Care Body Wash & Soaps TB4015 TB4018 Lotions Lotions TB4019 TU6009 Lotions Toothpaste & Toothbrushes Toothpaste & Toothbrushes EU5027 Hair Care EU5028 Hair Care FH5041 FH5042 Hair Care Hair Care Toothpaste & Toothbrushes Beauty Toothpaste & Toothbrushes Body Wash & Soaps TU6008 TU6010 BQ1003 TU6011 BU6016 BQ1004 Beauty Neutrogena Make-Up Remover Facial Towelettes Neutrogena Rain Bath Shower Gel Neutrogena Rapid Wrinkle Repair Cream Neutrogena Ultra Sheer Lotion SPF55 + Lip Balm Nexxus Humectress Conditioner Nexxus Therappe Shampoo Olay Moisture Body Wash Olay Regenerist Micro-Sculpting Cream Olay Regenerist Serum Olay Total Effects SPF 15 Fragrance Free Oral B 3D White Pulsar Battery Toothbrush Oral B Prohealth Cross Action Toothbrush Orlando Pita Argan Gloss Conditioner Orlando Pita Argan Gloss Shampoo with Travel Argan Oil Pantene Advanced Care Conditioner Pantene Advanced Care Shampoo Pronamel Gentle Whitening Toothpaste Q-Tips Cotton Swabs Sensodyne Extra Whitening Toothpaste Softsoap Advanced Clean Soap Swisspers Premium Hypoallergenic Cotton Rounds $5.20 $14.99 1776 2517 649 503 535 502 $12.97 $20.99 1515 2029 451 554 341 419 $18.00 $36.99 3233 1711 347 411 305 425 $7.09 $13.99 $11.67 $24.99 $5.20 $16.99 1266 1294 3540 2586 2173 544 402 700 604 608 293 438 539 466 663 536 431 $11.99 $20.99 3074 2433 401 458 586 671 $19.00 $39.99 $11.67 $37.99 2153 3544 1713 2609 274 665 555 443 362 660 299 501 $13.99 $29.99 2439 2158 541 633 265 438 $5.40 $14.99 1312 2402 684 322 473 610 $10.64 $13.99 3701 1761 354 330 342 506 $11.67 $14.99 3511 1923 264 684 501 224 $12.06 $14.99 1843 2167 613 211 513 548 $3.25 $9.99 $9.99 $10.94 1160 3353 2106 593 363 211 229 428 436 600 521 $11.35 $21.99 $5.60 $8.99 3131 3681 2651 2776 498 577 690 548 489 698 629 591 $10.64 $21.99 1250 1883 534 317 444 343 $8.99 $31.49 1139 2719 647 443 593 682 $3.25 $12.99 2560 1729 261 668 296 279 2397 1781 FH6001 TQ2001 Hair Care Razors & Refills Tigi Bed Head Shampoo & Conditioner Venus Disposable Razor $3.25 $9.97 $6.74 $24.99 1651 2363 2155 1840 588 245 643 401 342 421 301 533 Part 1 – Create / Download • Create a blank workbook. Name it using your Last name followed by your initials and then underscore 2EX. For example: WarnerBL_2EX.xlsx. • Copy/paste the data from Excel2_Data_F18.docx , into the 2nd spreadsheet in your workbook. Name the tab Items. • Adjust the formatting and set the column widths so that any column headings with more than 1 word have each word wrapped on a separate line within the one cell (see below). These should stay wrapped even if the column width is increased. • Sort the data (do not sort or remove the title/heading rows) by Item Code in ascending order. Check the sort to be sure all is correct. • Add conditional formatting to this Detail worksheet that highlights any sales in the 2017 Q1 through Q4 with sales greater than 600. Use an attractive fill color. If the sales number is then changed to a number less or equal to 600, the formatting should change automatically. The column header should not change color. • Using the named range feature of Excel, name all of the cells in this worksheet, ItemData. • No additional data/formulas should be added to the spreadsheet. Part 2 – Set up your Look Up spreadsheet • Name the tab for the first spreadsheet, LookUp. To the right is a sample of how I set up my spreadsheet. Use your own color scheme for your project – but include borders and backgrounds and include all of the components, as shown below. Component #1 – Title • • Include a title with your name and any other information you think is appropriate. Merge and center it across all columns with data. Add a colored border to the title row(s), not black/dark blue, and be sure the border is visible on all 4 sides (you can add a blank row above the title and a blank column to the left of the title so the whole border can be seen). Include a background color and font color (besides black/dark blue). Add a comment (using Excel’s comment feature) to your title and in the comment insert your name and email address. Component #2 – Input Area • Add an area to enter an Item Code. Try to make it obvious to the user that this is the data entry area. Use placement, borders, and/or background colors to distinguish it from the rest of the spreadsheet. • Include an arrow in this section; make it a color other than black/dark blue and use Excel’s SHAPE feature (don’t make an arrow from the keyboard). Component #3 –Look up Information • • • Use the VLOOKUP function/formula and search the Detail spreadsheet for the Item Code that the user entered in Component 2. Display information from the Detail sheet for the Item Code selected – use the same layout as in the example above. Correctly use your named range (ItemData) and absolute cell referencing in your VLOOKUP formulas Component #4 – Calculations • Some fields require formulas /calculations. Calculate and display the following in the LookUp spreadsheet. Don’t add any new formulas to the Detail spreadsheet. o Profit (per unit) o Markup % (per unit) - how much each unit is marked up based on the cost o Total for 2017 Sales (cell below the chart area) o Needed to Meet Sales Goal – Use an IF function to be sure that a negative number is not displayed if the goal has been met. o Sales Goal Met message – use an IF statement to display a message if the Sales Goal has been met. o Add a spark line cell to show the quarterly trend for the past year’s sales (cell i19 in my example above) o Display the Look up information & calculations in the same order as the example above. If you’re unsure of which cells should be VLOOKUPs and which should be formulas / calculations you create, see the overview video. Component #5 – Graph/Chart • Create the column chart displayed above: o Select only the data needed for the chart. Do not display any additional fields. o Display the data values for each column o The title should include the Item Name and should change each time new Item information is displayed. It should also be a larger font (greater than 12) and be a color other than blue or black. o Place the chart on your LookUp spreadsheet. o Use a 2-color (or more) gradient to format the columns. If this option isn’t available, you may have to change the shape of your columns to a shape that supports gradients. o Include colored fonts. Do not use dark blue/black for these colors. Component #6 – Protect Your Worksheet / Use IFERROR • Use the IFERROR function and if a user enters an Item Code that doesn’t exist, display ‘Item Code Not In Data’ for the title and blanks for the rest of the below. You may have to be creative with some cells that don’t easily disappear with the IFERROR function (think about IF functions in general for those). • Protect the LookUp spreadsheet so that the only change a user can make is to enter a different Item Code. They shouldn’t be able to change any other cells in the spreadsheet. Don’t use a password, just leave that blank. Test it when you’re done to be sure we can open the spreadsheet and enter a new Item Code and be sure we can’t change any other cells in the spreadsheet Part 3 – Pivot worksheets • Using the data in the Detail worksheet, create 2 pivot worksheets o The first should display the pivot table shown (right). The items sold should have commas and no decimal places. Add a title and format it so it looks nice. Name the worksheet tab Pivot 1. o The second should display the pivot table and chart seen below. The Sales numbers should have commas and no decimal places. Add a title and format it so it looks nice. Name the worksheet tab Pivot 2. Part 4 - Filtering • Create 3 worksheets and name their tabs Filter 1, Filter 2 and Filter 3. • Copy the Detail worksheet data into each of the filter worksheets. • For Filter 1, show all of the items in Lotions and Hair Care that have a Cost between $15 and $20 (‘between’ is one of your filtering options) . This should work even if additional data is added to the data set. • For Filter 2, show all of the items in Quarter 4 that are already highlighted for large sales. To do this, use the filter by color option. • For Filter 3, show some interesting analytics. Add a textbox to the worksheet to explain what you are showing. Show something that can’t be easily seen in the full data set (unfiltered). • Notes: 1) If you select a whole column and set / use the status bar at the bottom of your worksheet, the status bar can display the average, max, min, and sum of that column (nifty?). 2) Shortcut: Ctrl+Shift+L turns off and on Filtering. Part 5 – Finishing Up / Formatting / Checking **Note: you will need to unprotect your worksheet to make changes. • Your worksheets should be in the following order: LookUp, Items, your 2 pivots and then your 3 filter worksheets. • Once a user enters a new Item Code in the LookUp worksheet, all of the data and chart should automatically change. • Check your worksheet and be sure there are no errors or error symbols in your finished worksheet. If you don’t have this feature come into the lab to do this step. • Check your formatting – currency should have a $ and 2 decimal places, percents should be formatted with a % sign and 1 decimal place. • Check your formulas, be sure they are correct and make sense. For example, if you are subtracting 2 numbers don’t use the SUM formulas (sum is for adding). Excel may figure out what you mean, but we want the formulas to be used correctly (show that you understand how to use them). Important Instructions / Notes: • Please go through the instructions before you submit & be sure you have done each one correctly so you don’t miss out on points. Compare your solution to the project overview. • Remember to leave all of the internal file properties intact for your project, if they are modified or deleted, you project won’t be accepted (see syllabus for more on this). ...
Purchase answer to see full attachment

Tutor Answer

andythewxman
School: Cornell University

Here are my solutions. Please review them and let me know if any changes are needed....

flag Report DMCA
Review

Anonymous
Thank you! Reasonably priced given the quality not just of the tutors but the moderators too. They were helpful and accommodating given my needs.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors