ACC202
Accounting Information Systems
Missing Answer for Part C
(Part A and B done!!) only part C
PART C.
Discuss, justify and illustrate the following features that can be included, to help with strategic decision making. (10 marks)
- SUMIF
- COUNTIF
- Forecasting
- Index Match Function
- IF with And/OR
Use suitable data for the purpose of illustration, on separate sheets. Feel free to use data from this question / other sources.
Yume International is the New Zealand largest food & beverage wholesaler to cafes and convenience stores in Australia. You are provided with the information related to its customer details, product details, and costs and revenue information sheet. Yume International has partnered with three shipping companies who ships the products to its Australian customers on time. You are a junior accountant and your Senior Accounts Manager wants you to summarise this data in a meaningful way. Required: PART- A: Complete missing information in costs and revenue information sheet (highlighted in yellow colour) (5 marks) PART B: Pivot Table and Recommended Charts (5 marks) i. Calculate gross profit for each customer along with product name and payment type using Pivot Table; categorising the columns by salesperson ii. Calculate sales revenue by each salesperson using Pivot Table and illustrate it via Clustered Bar Chart iii. Calculate sales revenue and gross profit earned by each product using Pivot Table and illustrate it via 100%% stacked line graph For Pivot Tables and charts, please use a new worksheet and label it as (Pivot and Charts). You must use relevant Excel functions for the tasks assigned. Do not calculate it manually. PART C. Discuss, justify and illustrate the following features that can be included, to help with strategic decision making. (10 marks) SUMIF COUNTIF Forecasting Index Match Function IF with And/OR Use suitable data for the purpose of illustration, on separate sheets. Feel free to use data from this question / other sources. Professional presentation is expected all throughCustomer and Product Details Customer Details Product Details Customer ID Customer Name Contact Name Customer Address Customer Contact Product Name Category Cost / crate Mark up Promotional Discount 2005 Pies and Rolls Cafe Cooper Jesse 25 Gloria Circuit, Ballarat VIC 1090 161 424 111 569 Chocolate Biscuits Mix Baked Goods & Mixes 'S 51.0 20% 20022 Coffee Shop Xavier Matteo 15 Judy Street, Albury NSW 2000 461 488 828 287 Coffee Beverages 65.0 20084 Thai Coffee Parker Dean 25 Joseph Street, Wollongong NSW 2000 +61 457 751 694 Dried Plums Dried Fruit & Nuts 65.0 20% 20146 The Black Kettle Roman Zayden 8 Pamela Cresent, Hervey Bay QLD 4019 461 478 216 315 Long Grain Rice Grains 65.0 20007 Harrison Put Lid Jason Preston 12 Katherine Drive, Perth WA 6010 161 446 711 724 Curry Sauce Sauces 67.0 20160 umm Cafe Santiago August 15 Kelly Street, Geelong VIC 1000 461 420 710 109 Syrup Condiments 680 10% 20103 Seedling Cafe Chase Oscar 15 Samantha Street, Canberra ACT 2609 +61 440 831 544 Fruit Cocktail Fruit & Veg S 53.0 59 20120 Campbell Candy Sawyer Jeremy 17 Jesse Drive, Albury NSW 2000 461 413 138 154 finocchi Past 510 20095 Miilada Beer Co Gavin Alejandro 10 Gabriel Street, Adelaide SA 5090 +61 461 165 450 Chocolate Candy 51.0 20166 Pies and Rolls Cafe Leonardo Marcus 25 Gloria Circuit, Ballarat VIC 3090 461 468 118 296 Crab Meat Canned Meat 58.0 20179 Pioneer Bakers Kayden Dawson 17 Diane Lane, Sydney NSW 2000 461 482 312 571 Scones Baked Goods & Mixes 58.0 10% 20145 The Good Cafe Zion Lorenzo 25 Jeffrey Lame, Adelaide SA 5090 461 460 771 110 Clam Chowder Soups 210 2002 Sorento Cafe Maximas Messiah 13 Christian Street, Canberra ACT 2609 461 476 609 674 Beer Beverages 65.0 20048 Grain Store Ezekiel Rhet 12 Scan Circuit, Hervey Bay OLD 4018 461 449 174 290 Almonds Dried Fruit & Nuts $1.0 10% 20071 Third Wave Cafe Carson Avery 13 Sandra Circuit, Sydney NSW 2000 461 417 644 717 Olive Oi Di 55.0 20200 Third Wave Cafe Evan Steven 7 Larry Lane, Wollongong NSW 2000 461 460 121 704 Marmalade Jams, Preserves 670 1492 29% 20027 Good Meat Maverick Graham 6 Beverly Lane, Hobart TAS 4018 +61 477 132 158 Green Tea Beverages $5.0 16% 20068 Mitalda Beer Co Bryson Kaleb 30 Gabriel Street, Adelaide SA 5090 461 474 789 711 Boysenberry Spread Jams, Preserves 59.0 17%% 495 Mozzarella Dairy Products 51.0 18%% Chai Beverages Cajun Seasoning Condiments 56.0 13% Dried Pears Dried Fruit & Nuts 59.0 14% 4% Ravioli Pasta 51.0 11% Dried Apple Dried Fruit & Nut 45 0 9% 19% Payment Details Customer II Payment Type 20055 Credit Card 20027 Cheque 20084 Credit Card 20145 Cheque 20007 Credit Card 20160 Credit Card 20101 Cheque 20120 Cheque 20096 Credit Card 20166 Credit Card 20179 Credit Card 20145 Cheque 20027 Credit Card 20048 Cheque 20071 Theque 20200 Cheque 20023 Credit Card 20068YUME INTERNATIONAL PVT LTD Costs & Revenue Information Sheet Nancy Frochaker 20166 5/10/19 Global Shipping Company Coffee Beverages 16 565 00 5 1.146 00 Andrew Concini 4/10/19 Taurus Logistics Almonds 23 553 0075 1219 00 109: Andrew Cencini 20145 6/10/19 Global Shipping Company Olive Of 1.045 00 Laura Giurwant Good Meat 5/10/19 Global Shipping Company Dried Apple 14 $45 00 5 636.70 Harrison Put Lod Javon Preston 12 Katherine Drive, Pesch WA 6010 4/10/19 Taurus Logistics 20160 Yum Cafe Santiago August 35 Kelly Sweet, Geelong VK 3010 61 420 730 30 1/1 0719 4/10/19 Global Shipping Company Clam Chowder 49% 20103 Chase Oscar 35 Samantha Street, Canbera ACT 260 -61 440 833 5 1/10/19 24 $51 10 5 -61 413 338 1 1/10719 3/10/19 Evergreen Shipping Compa Beck 21 565 00 5 1.365 00 1 19: Anne Laran 20146 Mitlads Beer Co Garvin Alejandro 30 Gabriel Sweet, Adelaide SA 5020 61 463 165 4 1/10/19 3/10/19 Global Shipping Company Clam Chowder 23 523 00 5 550.16 Anne Laren 20166 Pies and Rolls Ca Leonardo Marcus 25 Gloria Circuit, Ballast VK 3020 61 468 338 25 1/10/19 6/10/19 Global Shipping Company |Coffee Beverages 12 565 10 5 730.00 15 5% Anne Larsen 20179 Pioneer Hlakers Kayden Dawson 17 Dime lane, Sydney NEW 2010 -61 482 312 57 1/10/19 5/10/19 Global Shipping Company |Dried Plum 1.365 00 2062 1.638 00 The Good Cafe Tion Lorenzo 1/10/19 1242 00 6/10/19 Taurus Logistics 13 565 00 5 Andrew Cencini Frckiel Rhett 2 Sew Circuit, Hervey Hay OLD 401 61 449 174 26 1/10/19 4/10/19 Taurus Logistics 20 559 00 Andrew Cencini 21073 Third Wave Cake Carson Avery 33 Sandra Circuit, Sydney NOW 2030 .61 417 644 7 1/10/14 5/10/19 Taurus Logistics Boysenberry Spread Jama, Preserves 20 $59 00 5 Andrew Cencini Thind Wave Cake Evan Steven 7 Lamy Lane, Wollongong NEW 2060 #61 460 123 70 1/10/14 6/10/19 Evergreen Shipping Compa Chocolate Biscuits M Baked Goods & Mi 1 272 00 1.526.40 Nancy Frechaker Maverick Geaham & Beverly Lane, Hobart TAG 4018 61 477 132 3 1/10/14 5/10/14 Evergreen Shipping Compa Chocolate 20 $51 00 5 Nancy Frechafer 20068 Micalda Beer Co Bryson Kaleb 30 Gabriel Sweet, Adelaide SA 5020 61 474 789 7 1/10/14 5/10/19 Taurus Logistics Dried Pears Dried Fruit & Nuts 20055 Pies and Rolls C Cooper Jesse 25 Gloria Circuit, Ballsat VK 3020 61 424 111 54 1/10/14 3/10/19 Taurus Logistics Than Nolan Xavier Mates 15 Judy Street, Albury NEW 2060 61 498 828 2 1/10/19 6/10/19 Global Shipping Company |Curry Sauce 844 20 Phan Nolan Parker Dean 6/10/14 Global Shipping Company Olive Oil 20 85 5 00 1 39: Nancy Frechafer 20145 The Good Cake Fion Levenso Harison Put Led Javon Preston 12 Katherine Drive, Perch WA 6010 61 446 733 75 2/10/19 6/10/19 Evergreen Shipping Compa Marmalade 20007 5/10/19 Evergreen Shipping Compa Chocolate Biscuits M Baked Goods & Mi Andrew Cencini 20160 Yuma Cake Santiago August 35 Kelly Sweet, Geelong VI 3060 61 420 730 36 2/10/14 4/10/19 Evergreen Shipping Compa Chocolate Biscuits M Baked Goods & Mix 24 553 00 5 1.272 00 954.80 936 00 1.526.40 20103 Seedling Cafe 5/10/19 Global Shipping Company Crab Most Fthan Nolan 20166 Pies and Rell Ca Leonardo Meout 25 Gloria Circuit, Balleat VK 3040 61 468 338 26 2/10/19 7/10/19 Evergreen Shipping Compa Chocolate 43.35 20174 Pioneer Bakers Kayden Dawson 17 Dime Lane, Sydney NEW 2010 61 482 312 5 2/10/19 7/10/14 Taurus Logistics Long Grain Rice 1 254.00 Lara Giuswant 20145 The Good Cake Zion Levenzo 25 Jeffrey Lane, Adelaide SA 5040 -61 460 771 1 2/10719 7/10/19 Global Shipping Company |Dried Plums Dried Fruit & Nuts 14 565 60 21023 Good Most Maverick Graham & Beverly Lane, Hobart TAS 4018 -61 477 132 3 2/10719 13 55500 315 80 20084 Chai Coffee Parker Dean Dairy Product Anne Lamen 20145 The Good Cafe Fion Levenzo 25 Jeffrey Lane, Adelaide SA 5040 61 460 771 1 2/10/19 5/10/14 Global Shipping Company Syrup Condiments Anne Laren 20007 Harison Put Led Jason Preston 12 Katherine Drive, Perch WA 6010 61 446 733 7 2/10/19 7/10/19 Global Shipping Company |Curry Sauce Andrew Cencini 20160 Yumm Cafe Santiago August 35 Kelly Street, Geelong VK 3060 61 420 730 36 2/10/14 4/10/19 Taurus Logistics 770.00 823 20 20103 7/10/19 Evergreen Shipping Compa Chocolate Biscuits M Baked Goods & Mix 2092 329 20 20120 Campbell Candy Sawyer Jeremy 17 Jesse Drive, Albury NEW 2060 -61 413 338 1 2/10/14 6/10/19 Global Shipping Company Dried Plum Andrew Cencini 20046 Mielada Beer Co Gavin Alejandre 30 Gabriel Sweet, Adelaide SA 5040 61 463 165 4 2/10/19 5/10/14 Global Shipping Company |Dried Plum 2092 Andrew Cencini 20166 Pies and Rolls Ca Leonardo Mwest 25 Gloria Circuit, Ballarat VX 3040 61 468 338 26 2/10/19 5/10/14 Evergreen Shipping Compa Chocolate 356 30 Nancy Frechafer 20174 Pioneer Bakers Kayden Dawson 17 Dime lanc, Sydney NEW 2010 -61 482 312 57 2/10/19 Beverages 23 565 00 Nancy Frechaker -61 460 771 1 2/10/19 5/10/19 Everprom Shipping Compa Crab Meat Canned Meat Maverick Graham & Beverly Lane, Hobart TAG 4018 61 477 132 3 3/10/19 6/10/19 Taurus Logistics 1.495 00 1.712.25 Grain Score Frckiel Rhett 12 Sem Circuit, Hervey Bay OLD 401 61 449 174 2 3/10/19 3/10/19 Global Shipping Company Green Tea Beverages Than Nolan 20073 Thind Wave Cafe Carson Avery 33 Sandra Circuit, Sydney NOW 2060 61 417 644 7 3/10/14 9/10/19 Evergreen Shipping Compa Coffee Beverages 1040.00 159: 1.403 60 Third Wave Cafe Evan Steven 7 lamy Lane. Wellengong NEW 2060 .61 460 123 76 3/10/14 8/10/19 Global Shipping Company Crab Meat 928 00 Andrew Cencini 16 551 00 5 79% 3/10/14 Global Shipping Company Mozzarella Dairy Product 816 00 20068 Micalda Beer Co Bryson Kale 30 Gabriel Sweet, Adelaide SA 5040 61 474 789 7 3/10/14 5/10/19 Global Shipping Company Clam Chowder Laura Giuruni 20055 Pies and Rolls Ca Cooper Jesse 25 Cloris Circuit, Ballast VI 3040 61 424 111 54 3/10/14 23 566 00 1 1.578-72 Bthan Nolan 20022 Coffee Shop Xavier Matter 15 Judy Street, Albury NEW 2060 61 498 828 2: 3/10/14 Caffee Beverages 1.560.00 15%% 20084 Chai Coffee Parker Dean 25 Joseph Sweet, Wollongong NOW 2 61 457 753 6 3/10/19 5/10/19 Taurus Logistics Chocolate 642.60 Laura Giuxuani 20145 The Good Cake Fion Lorenzo -61 460 71 1 3/10/19 9/10/19 Taurus Logistics 1 235.00 20007 Harison Put Led Javon Preston 12 Katherine Drive, Perch WA 6010 61 446 733 7: 3/10/19 8/10/19 Global Shipping Company |Boysenberry Spread Jams, Presence 22 852 00 20160 61 420 730 30 3/10/19 20103 Chase Over 35 Samantha Street, Canbera ACT 260 -61 440 833 5. 3/10/14
