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...

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
XXXXXXXXXX
· 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
May 16, 2021

Submit New Assignment

Copy and Paste Your Assignment Here