1. Identify errors and other issues in the sales and purchase transaction files using appropriate formulas.

• Using Excel formulas (e.g. vlookup) and the master file worksheets (assuming they contain accurate information) - determine which information is incorrect in the purchase and sales transactions.

Use Excel formulas to correct the transactions - the formulas must be left in your corrected file.

• Identify other potential issues regarding the transactions affecting consistency, data quality etc.
• You must use the Excel sheet given for all your workings but can create new worksheets as needed.

When submitting your Excel file, ensure you have clearly identified all of yourworksheets.

A) sales transaction follow instructions above to solve

a1

A B C D E F 1 Sales Invoice Numbe Sales Date Customer Number Customer Name Address line 1 Address line 2 2 51653 22/01/2022 ADN Adventure Now 1666 Wynnum Road Tingalpa W 51654 2022/1/1 CLB Cycle Lab 20 Preston Road 4 51655 2/02/2022 BHB Bike Hub 12 Station Street Nundah 51656 6/02/2022 CLB Cycle Lab 20 Preston Road Manly West 6 51656 6/02/2022 CLB Cycle Lab 20 Preston Road Manly West 51657 28/02/2022 PKC Peak Cycles 26 Rode Road Wavell Heights 00 51658 1/03/2022 BHM Bike Home 290 Tingal Road Wynnum 51659 1/03/2022 PKC Peak Cycles 26 Rode Road Wavell Heights 10 51660 12/03/2022 ADN Peak Cycles 1666 Wynnum Road Tingalpa 11 31/03/2022 BHM Bike Home 290 Tingal Road Wynnum 12 51662 4/04/2022 CLB Cycle Lab 20 Preston Road Manly West 13 51662 10/04/2022 PKC Bike Home 26 Rode Road Wavell Heights 14 51663 12/04/2022 CLB Cycle Lab 20 Preston Road Manly West 15 51664 16/04/2022 ADN Adventure Now 1666 Wynnum Road Tingalpa 16 51665 5/05/2022 BHM Bike Home 1666 Wynnum Road Tingalpa 17 51667 30 May PKC Adventure Now 26 Rode Road Wavell Heights 18 51668 13/06/2022 PKC Peak Cycles 26 Rode Road Wavell Heights 19 51669 13/06/2022 ADN Adventure Now 1666 Wynnum Road Tingalpa 20 20/06/2022 ADN Adventure Now 1666 Wynnum Road Tingalpa 21 51671 21/06/2022 BHM Bike Home Wynnum 22 51672 30/06/2022 CLB Cycle Lab 20 Preston Road Manly West 23G H K M Email Inventory Number Inventory Description Quantity Unit Price Total Sales Authorising Employe ID adventurenow@com.au K-BIKE Kids Bike 2 $500.00 $ 746.00 20 clab@com.au MM-BIKE Men's Mountain Bike 5 $1,326.00 $ 6,630.00 20 bikehub @com.au WM-BIKE Women's Mountain Bike 10 $1,326.00 $13,260.00 20 clab@com.au WM-BIKE Women's Mountain Bike 6 $1,326.00 20 clab@com.au WM-BIKE Women's Mountain Bike 6 $1,326.00 20 pcycles@com.au B-PARTS Bike Parts 1 $60.00 $ 60.00 20 B-PARTS Bike Parts 5 $60.00 $ 300.00 20 pcycles@com.au WM-BIKE Women's Mountain Bike 2 $ 2,652.00 20 adventurenow@com.au MM-BIKE Men's Mountain Bike 4 $1,326.00 $ 5,304.00 20 bikehome@com.au B-PARTS Bike Parts 15 $60.00 $ 900.00 30 clab@com.au K-HELMET Kids Helmet 7 $50.00 $ 350.00 20 K-BIKE Kids Helmet 8 $50.00 $ 400.00 20 clab@com.au WR-BIKE Women's Road Bike 1 $825.00 $ 825.00 20 adventurenow@com.au MM-BIKE Men's Mountain Bike 2 $1,326.00 $ 2,652.00 10 adventurenow@com.au W-HELMET Women's Helmet 4 $60.00 $ 240.00 20 pcycles@com.au M-HELMET Men's Helmet N $80.00 $ 160.00 20 pcycles@com.au MR-BIKE Men's Road Bike 1 $825.00 $ 825.00 20 adventurenow@com.au B-PARTS Bike Parts UT $60.00 $ 300.00 20 adventurenow@com.au WM-BIKE Men's Road Bike 2 $60.00 $ 120.00 40 bikehome@com.au MR-BIKE Men's Road Bike A $825.00 20 clab@com.au WR-BIKE Women's Road Bike 1 $825.00 $ 825.00 20A B C D E F 1 Purchase Invoice Numbe Purchase Date Supplier Numb Supplier Name Address line 1 Address line 2 13489 23/05/2022 OTB Outdoor Bikes PO Box 66 Gladstone QLD 4680 W 13490 23/05/2022 OTB Outdoor Bikes PO Box 66 Gladstone QLD 4680 13491 23/05/2022 OTB Outdoor Bikes PO Box 66 Gladstone QLD 4680 1/02/2022 CYS Cycle Smart 16 Brunswick Street Fortitude Valley QLD 4006 6 13491 19 Jan BPA Bike Parts and Accessories GPO Box 1228 Brisbane QLD 4000 13492 12/04/2022 MBS Mountain Biking Specialists 25 Montpelier Road Bowen Hills QLD 4006 13493 31/01/2022 HTO Mountain Biking Specialists Pinkenba QLD 4008 9 13493 31/01/2022 HTO Mountain Biking Specialists Pinkenba QLD 4008 10 13494 22/03/2022 HTO Helmets Online 16 Lavarak Avenue Pinkenba QLD 4008 11 13495 7/04/2022 MBS Mountain Biking Specialists 25 Montpelier Road Bowen Hills QLD 4006 12 13497 2/05/2022 CYS Cycle Smart 16 Brunswick Street Fortitude Valley QLD 4006 13 13498 1/06/2022 BPA Bike Parts and Accessories GPO Box 1228 Brisbane QLD 4000 14 13499 13/06/2022 BPA Cycle Smart 16 Brunswick Street Fortitude Valley QLD 4006 15 13500 25/03/2022 JBS Johnson Business Services 320 Adelaide Street Brisbane QLD 4000 16 13501 4/04/2022 MBS Mountain Biking Specialists 25 Montpelier Road Bowen Hills QLD 4006 17 16/01/2022 CYS Cycle Smart 16 Brunswick Street Fortitude Valley QLD 4006 18 13503 5/05/2022 JBS Johnson Business Services 320 Adelaide Street Brisbane QLD 4000 19 13504 17/02/2022 HTO Helmets Online 16 Lavarak Avenue Pinkenba QLD 4008 20 13504 17/02/2022 MBS Mountain Biking Specialists 25 Montpelier Road Bowen Hills QLD 4006 21 13505 18/02/2022 HTO Helmets Online PO Box 66 Gladstone QLD 4680 22 13506 31/01/2022 CYS Cycle Smart 16 Brunswick Street Fortitude Valley QLD 4006G H K L M Email Inventory Number Inventory Description Quantity Unit Price Total Purchase Price Authorising Employee ID outdoorbikes@com.au K-BIKE Kids Bike 2 $560.00 $ 370.00 10 outdoorbikes@com.au MM-BIKE Men's Mountain Bike 3 $800.00 $ 2,400.00 10 WM-BIKE Women's Mountain $800.00 Bike 800.00 20 outdoorbikes@com.au WR-BIKE Women's Road Bike 1 $560.00 $ 56.00 10 bikepartsandaccessories@com.au B-PARTS Bike Parts 5 $20.00 $ 100.00 10 WM-BIKE Women's Mountain mbspecialists@com.au $800.00 Bike 2 S 1,600.00 10 helmetsonline@com.au K-HELMET Kids Helmet 10 $20.00 $ 200.00 10 helmetsonline@com.au K-HELMET Kids Helmet 10 $20.00 $ 200.00 10 helmetsonline@com.au W-HELMET Kids Helmet 5 $40.00 $ 200.00 40 mbspecialists@com.au MM-BIKE Men's Mountain Bike 1 $800.00 $ 800.00 10 csmart@com.au MR-BIKE Men's Road Bike 3 $560.00 $ 200.00 10 bikepartsandaccessories@com.au B-PARTS Bike Parts 2 $20.00 $ 40.00 10 csmart@com.au B-PARTS Bike Parts 1 $200.00 $ 20.00 10 johnsonbusinessservices@com.au 1 $600.00 $ 600.00 10 mbspecialists@com.au MM-BIKE Men's Mountain Bike 2 $800.00 $ 1,600.00 10 WR-BIKE Men's Mountain Bike 2 $560.00 $ 1,120.00 10 johnsonbusinessservices@com.au 1 $1,500.00 $ 1,500.00 10 helmetsonline@com.au K-HELMET Kids Helmet 5 $20.00 10 mbspecialists@com.au MM-BIKE Men's Mountain Bike 2 $800.00 $ 1,600.00 10 helmetsonline@com.au M-HELMET Men's Helmet 10 $40.00 $ 400.00 30 csmart@com.au MR-BIKE Kids Bike 2 $560.00 $ 1,120.00 10