I need to solve this excel question
Paste Merge & Cantre v $ % Cell Format B U y YA Conditional Format Insert Delete Format Clear Sort & Firk Formatting as Table Styles Filter Selo NS X L M AD YUME INTERNATIONAL PVT LTD Costs & Revenue Information Sheet 1/18714 uracil Thepad setd shipping Lenpara Cheer Che Chende 34303 Tara Ledida CHT Cooke HITE KI 30307 RIM. 20345 21325 20325 21345 Husmila 21359 Che Creak OHEVEN Got hisping LemparkN5 x v fx B C D E F G H Yume International is the New Zealand largest food & beverage wholesaler to cafes and convenience stores in Australia. 2 You are provided with the information related to its customer details, product details, and costs and revenue information sheet. 3 Yume International has partnered with three shipping companies who ships the products to its Australian customers on time. 4 You are a junior accountant and your Senior Accounts Manager wants you to summarise this data in a meaningful way. 5 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 10 ifi. Calculate sales revenue and gross profit earned by each product using Pivot Table and illustrate it via 100% stacked line graph 11 12 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. 13 15 Customer and Product Details 16 Customer Details Product Details 17 Customer ID Customer Name Contact Name Customer Address Customer Contact Product Name Category Cost / crate Mark- up Promotional Discount 18 20055 Pies and Rolls Cafe Cooper Jesse 25 Gloria Orouit, Ballarat VIC 3090 +61 424 111 569 Chocolate Biscuits Mix Baked Goods & Mixes 55.0 20% 20023 Coffee Shop Xavier Matteo 15 Judy Street, Albury NSW 2000 +61 488 828 287 Coffee Beverages $ 65.0 15% 2%% 20 2008- Chai Coffee Parker Dean 25 Joseph Street, Wollongong NSW 2000 +61 457 753 694 Dried Plums Dried Fruit & Nuts $ 65.0 20% 2%% 20145 The Black Kettle Roman Zayden 9 Pamela Cresent, Hervey Bay OLD 4019 +61 478 236 315 Long Grain Rice Grains S 66.0 20007 Harrison Put itd lasan Preston 12 Katherine Drive, Perth WA 6010 +61 446 733 724 Curry Sauce Sauces S 67.0 20160 Yumm Cafe Santiago August 35 Kelly Street, Geelong VIC 3000 #61 420 730 309 Syrup Condiments 68.0 10% 24 20103 Seedling Cafe Chase Oscar 35 Samantha Street, Canberra ACT 2609 +61 440 833 544 Fruit Cocktail Fruit & Veg 53.0 49% 25 20120 Campbell Candy Sawyer Jeremy 17 Jesse Drive, Albury NSW 2000 +61 413 338 154 Gnotch Pasta S 51.0 20096 Mitlada Beer Co Gavin Alejandro 30 Gabriel Street, Adelaide SA 5090 +61 463 165 450 Chocolate Candy 51.0 27 20166 Pies and Rolls Cafe Leonardo Marcus 25 Gloria Orcult, Ballarat VIC 3030 461 468 338 295 Crab Meat Canned Meat 58.0 0% 28 20179 Pioneer Bakers Kayden Dawson 17 Diane Lane, Sydney NSW 2000 +61 482 312 571 |Scones Baked Goods & Mixer 58.0 10% 29 20145 The Good Cafe Zion Lorenza 25 Jeffrey Lane, Adelaide SA 5090 +61 460 771 110 Clam Chowder Soup 23.0 30 20023 Sorento Cafe Maximus Messiah 33 Christian Street, Canberra ACT 2609 +61 476 609 674 Beer Beverages 65.0 1 1% 39% 20048 Grain Store Ezekiel Rhett 12 Sean Circuit, Hervey Bay OLD 4018 461 449 174 290 Almonds Dried Fruit & Nuts 53.0 10% 20073 Third Wave Cafe Carson Avery 33 Sandra Circuit, Sydney NSW 2000 +61 417 644 717 Olive Oil Oil 55.0 13% 39% 20200 Third Wave Cafe Evan Steven 7 Larry Lane, Wollongong NSW 2000 +61 460 123 704 Marmalade Jam, Preserves 67.0 14% 2%% 20023 Good Meat Maverick Graham 6 Beverly Lane, Hobart TA$ 4018 +61 477 132 358 Green Tea Beverages 55.0 16% 3% 20068 Mitalda Beer Co Bryson Kaleb 30 Gabriel Street, Adelaide SA 5090 461 474 789 711 Boysenberry Spread Jams, Preserves 59.0 17% 49% Mozzarella Dairy Products 51.0 18% Chai Beverages 54.0 15% Cajun Seasoning Condiments 56.0 13% Dried Pears Dried Fruit & Nuts 59,0 14% 4% Ravioli Pasta 51.0 11% 3% Dried Apples Dried Fruit & Nuts 43.0 9% 3%%146 X v fx B C D G H 14 15 Customer and Product Details 16 Customer Details Product Details Customer ID Customer Name Contact Name Customer Address Customer Contact Product Name Category Cost / crate Mark- up Promotional Discount 20055 Pies and Rolls Cafe Cooper Jesse 25 Gona Circuit, Ballarat VIC 3090 +61 424 111 569 Chocolate Biscuits Mix Baked Goods & Mixes 55.0 20% 256 20022 Coffee Shop Xavier Matteo 15 Judy Street, Albury NSW 2000 +61 488 828 287 Coffee Beverages 65.0 15%% 2% 20084 Chai Coffee Parker Dean 25 Joseph Street, Wallongong NSW 2000 +61 457 753 694 Dried Plums Dried Fruit & Nuts 65.0 20% 2% 20145 The Black Kettle Roman Zayder Pamela Cresent, Hervey Day QLD 4019 +61 478 236 315 Long Grain Rice Grains 66.0 4% 20007 Harrison Put Ltd Jason Preston 12 Katherine Drive, Perth WA 6010 +61 446 733 724 Curry Sauce Sauces 67.0 5% 3%% 20160 Yumm Cafe Santiago August 35 Kelly Street, Geelong VIC 3000 +61 420 730 309 Syrup Condiments 68.0 10% 4% 20103 Seedling Cafe Chase Oscar 35 Samantha Street, Canberra ACT 2609 +61 440 833 54 Fruit Cocktail Fruit & Vee 53.0 5% 4% 20120 Campbell Candy Sawyer Jeremy 17 Jesse Drive, Albury NSW 2000 +61 413 338 154 Gnocchi Pasta 51.0 6% 0% 20096 Mitlada Beer Co Gavin Alejandro 30 Gabriel Street, Adelaide SA 5090 +61 463 165 450 Chocolate Candy 51.0 0% 20166 Pies and Rolls Cafe Leonardo Marcus 25 Gloria Circuit, Ballarat VIC 3090 +61 468 338 296 Crab Meat Canned Meat 58.0 056 20179 Pioneer Bakers Kayden Dawson 17 Diane Lane, Sydney NSW 2000 461 482 312 571 Scones Baked Goods & Mixes 58.0 10% 0% 20145 The Good Cafe Zion Lorenzo 25 Jeffrey Lane, Adelaide SA 5090 #61 460 771 110 Clam Chowder Soups 23.0 4% 056 20023 Sorento Cafe Maximus Messiah 33 Christian Street, Canberra ACT 2609 +61 476 609 67 Beer Beverages 65.0 119 3% 20048 Grain Store Ezekiel Rhett 12 Sean Circuit, Hervey Bay QLD 4018 +61 449 174 290 Almonds Dried Fruit & Nuts $3.0 10% 3% 20073 Third Wave Cafe Carson Avery 33 Sandra Circuit, Sydney NSW 2000 +61 417 644 717 Olive Oil Oil 55.0 13% 20200 Third Wave Cafe Evan Steven 7Larry Lane, Wollongong NSW 2000 +61 460 123 704 Marmalade Jams, Preserve 67.0 14% 25% 20023 Good Meat Maverick Graham 6 Beverly Lane, Hobart TAS 4018 461 477 132 358 Green Tea Beverages 55.0 16% 3%% 20068 Mitalda Beer Co Bryson Kaleb 30 Gabriel Street, Adelaide SA 5090 161 474 789 711 Boysenberry Spread Jams, Preserves 59.0 17% 456 Mozzarella Dairy Products 51.0 189 45% Cha Beverages 54.0 15% 5% Cajun Seasoning Condiments 56.0 139 5% Dried Pears Dried Fruit & Nuts 59.0 14% 4% Raviol Pasta $1.0 11% 3% Dried Apples Dried Fruit & Nuts 43.0 9% 3% Payment Details Customer 10 Payment Type 1005 Credit Card 20022 Cheque 20084 Credit Care 20145 Credit Card 20007 Credit Card 20160 Credit Card 2010 Cheque 20120 Cheque 10096 Credit Card 20166 Credit Card 20175 Credit Card 20145 Cheque 20023 Credit Card 20048 Cheque 20073 Cheque 20200 Cheque 20023 Credit Card 20068 ChequeHome Insert Draw Page Layout Formulas Data Review View Tell me S K Cut Copy Calibri (Body) 11 YA Wrap Text v General _ Auto-sum * Fill y Pasta Format BIUV AY Merge & Centre v $ % 7 Conditional Format Call Insert Delata Format Find & -ormatting as lable Styles Clear Sort & Filter Select NS fx LOVE Cha LOVE Dan Hol w TLOVE TLOVE TLOVE LOUIS dur chowder 37717 LOVE LOVE 3712 Man Mars :3200 "LOVE RLOVE Dun Hole EYLE TLOVE TLOVE
