Background: Taffy Gordon owns a resale shop in Geelong, Victoria. She sells various items such as children’s toys, clothes, jewelleries and books. For this matter, you are tasked to create a simple...

See Attached files just want a quote please


Background: Taffy Gordon owns a resale shop in Geelong, Victoria. She sells various items such as children’s toys, clothes, jewelleries and books. For this matter, you are tasked to create a simple spreadsheet to help her with electronic billing and invoicing. Instructions: 1. Open a new blank document. Save it as Excel Assessment.xlsm (macro-enabled workbook) 2. Rename the worksheet as Documentation; apply green tab color · A1 MS Excel Assessment · A3Date · B3> · A4Author · B4> · A5Student ID · B5> · A6Purpose · B6To generate a sales receipt using standardized information for Item Groups and Age Groups ; format cell to wrap text · Apply autofit column width for columns A and B · Apply 50pt row height for Row 1 · Apply 25pt row height for rows 3 to 6 · A1:B1Merge and Center; bold font, 28pt font size, Calibri Light font type, white font color, blue accent 1 darker 25% background fill; thick bottom border with blue accent 5 lighter 40% color · A3:A6Bold, white font color, blue accent 1 lighter 40% background fill · A3:B6Left-aligned, middle-align · A3:B6Apply all-borders, with thick outside border · Insert a column before column A, with column width of 2 3. Insert additional worksheets, Invoice and Item Information 4. In the Item Information worksheet: · Apply yellow tab color · Import the data from the Item Info CSV file (open the embedded file below) · Convert the range in columns A and B to a table; ensure the headers are used · Change the table name to Groups; resize the columns, accordingly · Convert the range in columns D to G into a table; ensure the headers are used · Change the table name to Price; resize the columns, accordingly · Create pivot table using the price table to new worksheet · Rename the pivot table as PricePivot · Rename the worksheet as Pivot Table · Use orange tab color · Create a pie chart using the pivot table · Filter the chart to only show Teen and Youth age groups · Filter the chart to only show Coats, Dresses, Pants, Shirts and Shoes · Show data labels, using percentage (not value), on outside end · Change the chart title to Teen and Youth Clothing · Place the chart at F3:M20 · Move the pivot table worksheet after the item information worksheet 5. In the invoice worksheet, re-create the invoice shown in the sample document. Use defined names for customer name, street, address, city, associate, subtotal, sales tax and total. · Row 160pt row height · Row 544pt row height · Rows 6 – 2434pt row height · Column A,HColumn width 2 · Column BColumn width 12 · Column CColumn width 30 · Column DColumn width 19 · Column EColumn width 19 · Column FColumn width 19 · Column GColumn width 19 · B1:G1Blue Accent 1 Darker 25% background fill; thick bottom border with blue accent 5 lighter 40% color · B1type Geelong Resale Shop - Invoice Format as follows: Calibri Light, Bold, 25 pt font size, white font color · F1:G1Merged, align right, middle align, white font color, 10pt font; enter data below: Boundary Rd, Geelong VIC 555-5555 · B2Bill To: · B3:B4Merge, top align, left align, then type “Address:” (no quotations) · C3:C4Merge, top align, left align · F2Invoice #: · F3Invoice Date: · G3use a formula to show the current date · B5Middle align, left align, then type “Associate:” (no quotations) · B2:G511pt font size, Calibri Light, Blue Accent 1 Darker 50% font color · B6Item# · C6Item Group · D6Age Group · E6Unit Price · F6Quantity · G6Price · B6:G614pt font size, Calibri, Blue Accent 1 Darker 25% font color; center align, middle align · B6:G6Apply Blue Accent 1 outside border · B7:B21Enter numbers from 1 to 15 · B7:G21Apply all borders; blue accent 1 color; center align, middle align · F22:G27Apply all borders; blue accent 1 color; center align, middle align · B7:G7, B9:G9, B11:G11, B13:G13, B15:G15, B17:G17, B19:G19, B21:G21, F23:G23, F25:G25 – apply Blue Accent 1 Lighter 80% background fill · Insert the image theresale.gif; resize to 1.35in height x 3.68in width; place it between C23:D25 · I7Enter the formula =D7&" "&C7 · I8:I21Copy the formula from I7 and paste it to the specified range · J7:J21use Vlookup to get the price from the Price table in Item Information worksheet by looking up at the value in column I at Invoice worksheet · Apply data validation rule to the following cells that shows an input message of · C2Enter customer’s name · C3Enter the billing address · C5Enter associate’s name · G2Enter the invoice number · C7:C21Apply data validation rule to only allow values from the list of item groups in the groups table at the item information worksheet · D7:D21Apply data validation rule to only allow values from the list of age groups in the groups table at the item information worksheet · E7:E21Use a formula such that IF there is a value in column D, then get the value from column J, otherwise the cell is blank Format in currency Hint #1: cell<>”” means cell not blank Hint #2: “” means blank Note: Be careful not to change the cell formatting (fill, borders, etc) · G7:G21Use a formula such that IF there is a value in column D, then calculate the product of unit price and quantity otherwise, enter zero. Use accounting format Note: Be careful not to change the cell formatting (fill, borders, etc) · Columns I, JTest first if your formulas work from rows 7 to 21 After successful test, hide columns I and J · F22typeInvoice Subtotal; accounting format · G22Use a formula that will get the total price · F23typeTax Rate · G23enter 10%; right align · F24typeSales Tax; accounting format · G24Use a formula that will get the product of invoice subtotal and tax · F25typeDiscount · G25type 0.00; format in red font color; use accounting format · F26typeOthers · G26type0.00; use accounting format · F27type Total · G27Use a formula that will add the invoice subtotal with the sales tax and others, but less the discount; use accounting format · F27: G2714pt font size, bold, white font color, blue accent 1 darker 25% background fill color · B27:E27Merge and center; apply Blue Accent 1 Lighter 80% background fill Type*** Make all checks payable to Geelong Resale Shop *** Center aligned, middle align 6. Set the page layout as follows: · Manage the page breaks so that page 1 is from A1:H28 · Customize the margins so the page is centered horizontally 7. Create a macro named PrintInvoice that exports the invoice to PDF. · Assign the shortcut key:ctrl + shift + P · Name the PDF as printout.pdf · Create a macro button and assign the macro to it; place it in F5, named Print 8. Create a macro named Clearinput that deletes the values from the range C2:C5, G2, C7:D21, F7:F21, G25:G26 · Assign the shortcut key:ctrl + shift + C · Create a macro button and assign the macro to it; place it in D5, named Clear 9. Protect the worksheet so a user can enter customer data ONLY in the following ranges: C2:C5, G2, C7:D21, F7:F21, G25:G26 · Use “excel” as the password 10. Enter the data below then run your macros. By now, you should have the following: Excel Assessment.xlsm Printout.pdf Item Info.csv Item Info Item GroupAge GroupItemAge GroupItem GroupPrice ToysBabyBaby ToysBabyToys2 CoatsToddlerToddler ToysToddlerToys4 ShoesYouthYouth ToysYouthToys6 ShirtsTeenTeen ToysTeenToys8 PantsAdultAdult ToysAdultToys10 DressesBaby CoatsBabyCoats5 PursesToddler CoatsToddlerCoats10 TowelsYouth CoatsYouthCoats15 JewelryTeen CoatsTeenCoats20 BooksAdult CoatsAdultCoats25 Baby ShoesBabyShoes3 Toddler ShoesToddlerShoes6 Youth ShoesYouthShoes9 Teen ShoesTeenShoes12 Adult ShoesAdultShoes15 Baby ShirtsBabyShirts3 Toddler ShirtsToddlerShirts6 Youth ShirtsYouthShirts9 Teen ShirtsTeenShirts12 Adult ShirtsAdultShirts15 Baby PantsBabyPants5 Toddler PantsToddlerPants10 Youth PantsYouthPants15 Teen PantsTeenPants20 Adult PantsAdultPants25 Baby DressesBabyDresses10 Toddler DressesToddlerDresses20 Youth DressesYouthDresses30 Teen DressesTeenDresses40 Adult DressesAdultDresses50 Baby PursesBabyPurses2 Toddler PursesToddlerPurses4 Youth PursesYouthPurses6 Teen PursesTeenPurses8 Adult PursesAdultPurses10 Baby TowelsBabyTowels1 Toddler TowelsToddlerTowels2 Youth TowelsYouthTowels3 Teen TowelsTeenTowels4 Adult TowelsAdultTowels5 Baby JewelryBabyJewelry10 Toddler JewelryToddlerJewelry20 Youth JewelryYouthJewelry30 Teen JewelryTeenJewelry40 Adult JewelryAdultJewelry50 Baby BooksBabyBooks5 Toddler BooksToddlerBooks10 Youth BooksYouthBooks15 Teen BooksTeenBooks20 Adult BooksAdultBooks25 1 2 3 4 5 6 7 8 9 A B C D E Item Group Age Group Item Age Group Toys Baby Baby Toys Baby Coats Toddler Toddler Toys Toddler Shoes Youth Youth Toys Youth Shirts Teen Teen Toys Teen Pants Adult Adult Toys Adult Dresses Baby Coats Baby Purses Toddler Coats Toddler Towels Youth Coats Youth Documentation Date 4/26/2019 Author Student Full Name Student ID Student ID number Purpose To generate a sales receipt using standardized information for Item Groups and Age Groups MS Excel Assessment Invoice Geelong Resale Shop - Invoice Bill To: Invoice #: Invoice Date: 4/26/2019 Associate: Item# Item Group Age Group Unit Price Quantity Price 1 -$ 2 -$ 3 -$ 4 -$ 5 -$ 6 -$ 7 -$ 8 -$ 9 -$ 10 -$ 11 -$ 12 -$