excel assignment

profileAlbertone
443345591.xlsm
Home>Business & Finance homework help>Accounting homework help>excel assignment

Information

ACCG2000 Excel Assignment 2020 Semester 2
Student Name: Zefeng ZHANG
Student Number: 44334559
TO PROCEED YOU MUST ENABLE MACROS AND ENTER YOUR STUDENT NUMBER WHEN PROMPTED
ASSIGNMENT OVERVIEW
Seasonal Delights Catering offer personalised catering services to corporate clients. To help cost their jobs they have recently implemented a Job Costing System. The head chef has allocated a costing for ingredients to each menu item to help quickly calculate how much they will spend on materials based onthe menu selected by the client. For each job casual staff are employed by the hour as required. You need to help finish off the spreadsheet template that they will use to cost job 4155 and all future jobs. Download the full instructions and a mark breakdown from iLearn.
PLEASE NOTE:
Do not use rounding functions unless specifically instructed
Do not change the values or structure of the workbook in any way, i.e. do not move/add/remove sheets, columns or rows.
Only the contents of the cells indicated should be changed (do not use other cells for interim workings)
M@QU@R1E2017
M@cquarie!

July Job List

July Job List
Job Number Date Client ID Client Name Revenue Fully Paid Client ID
4148 7/2/20 15928 Ebony Telecoms $7,335.00 Yes 4153
4149 7/5/20 15458 AHA Networks $10,571.00 Yes
4150 7/8/20 14051 Euro-M $5,488.00 Highest Revenue
4151 7/13/20 17769 Chirah Technologies $23,128.00 Yes $23,128.00
4152 7/17/20 20626 Epsilon Tech $16,456.00 Yes
4153 7/20/20 19488 Shaw Construction $17,585.00
4154 7/24/20 17464 $17,596.00 Yes
4155 7/26/20 13824 Wiz Labs $19,503.00
Use the data above to answer the following multiple choice questions. For each question change the correct option to TRUE (it will change to green) , leave the others FALSE.
1 How many rows in an excel worksheet?
A Infinite FALSE
B 1048576 TRUE
C 1024 FALSE
D 16384 FALSE
2 Which keyboard shortcut creates an absolute reference?
A Alt+F4 FALSE
B Ctrl+4 FALSE
C Shift+4 FALSE
D F4 TRUE
3 Numbers in Excel automatically align
A Top Right TRUE
B Top Left FALSE
C Bottom Right FALSE
D Bottom Left FALSE
4 The formula =COUNT(E6:F13) will return
A 15 FALSE
B 8 TRUE
C An error FALSE
D 16 FALSE
5 Which of the following would calculate the most recent job date?
A =MAX(C6:C13) TRUE
B =MINIFS(C6:C13,"<"&TODAY()) FALSE
C =DATE(C6:C13,0) FALSE
D =RECENT(C6:C13) FALSE
6 What is wrong with the following calculation =COUNTIFS(G6:G13,Yes)?
A Should be a SUMIFS TRUE
B Missing an absolute cell reference FALSE
C Missing quotes around the Yes FALSE
D Missing a check for empty cells FALSE
7 The formula =VLOOKUP(I9,B6:G13,1,FALSE) will return an error, why?
A Because it is an exact match lookup FALSE
B Because the lookup range has not been made absolute FALSE
C Because the lookup value is not in the first column FALSE
D Because it is a range lookup TRUE
8 The formula =IF(AND(F13>19000,G13<>"Yes"),"Call","No Call") will return
A No Call FALSE
B An error FALSE
C Yes FALSE
D Call TRUE

Cost Overview

Overhead and Staff Costs
Indirect Costs Monthly Annual Weekend Extra: 45.00%
Rent $2,900.00 $34,800.00
Wages for permanent staff $4,600.00 $55,200.00 Temporary Staff Standard Rate Weekend Rate
Insurance $270.00 $3,240.00 Temp Chef 68.64 30.888
Advertising $110.00 $1,320.00 Wait Staff 51.67 23.2515
Kitchen Equipment $105.00 $1,260.00 General Help 37.32 16.794
Total $7,985.00 $95,820.00 Cleaning Staff 24.69 11.1105
Services and Utilities Monthly Annual
Electriricty and Gas $145.00 $1,740.00
Internet and Phone $55.00 $660.00
POS Fees $30.00 $360.00
Total $230.00 $2,760.00
Supplies Monthly Annual
Cleaning Equipment $87.50 $1,050.00
General Kitchen SupplieS $191.67 $2,300.00
Stationery and Office Suppies $38.75 $465.00
Total $317.92 $3,815.00
TOTAL OVERHEADS $8,532.92 $102,395.00
Days worked in a year (estimate): 240
Overheads on a job are calculated as a percetage of these days

Client Database

Client Database
Client ID Organisation Join Date Client Contact Email Jobs New* Gift? Client_Status Job Range Status Clients Total Jobs
10130 Verisize 11/1/13 Brad Gorman [email protected] 24 1 Bronze
10195 LACNE 10/4/14 Kanji Bhodia [email protected] 14 5 Silver
10315 First Finance 12/30/18 SeyedAlireza Vaziri [email protected] 3 10 Gold
10540 Colot 9/25/19 Vasileios Giotsas [email protected] 6 20 Platinum
10639 CTX  4/20/16 Geoffrey Huston ghuston@ctx .com.au 12
10679 Steps IT Training 8/16/15 Benedikt Stockebrand [email protected] 18 New Start Date 1/2/20
10932 StepAhead 9/13/16 Petr Špaček pšpač[email protected] 4 Loyalty Date 1/1/14
11230 Duet 8/20/18 Tarek Fouad [email protected] 9 New Starters
11280 Axell Group 11/27/13 Steven Leander [email protected] 24 Free Gifts
11325 ByteSize 1/9/17 Kevin Pillay [email protected] 3 Total Catering Jobs 523
11344 Data Pro Sys 6/14/19 Miquel van Smoorenburg mvan [email protected] 6
11365 PicSure 12/14/17 Lukasz Janczura [email protected] 9
11584 Ripple Com 6/9/16 Christian Kaufmann [email protected] 10
11646 Pink Cloud Networks 7/23/14 Dennis Thomas [email protected] 14
11762 Cyber Data Processing 2/9/14 Jonathan Freeman [email protected] 21
11854 TQ Processe 1/23/15 Mehmet Tik [email protected] 18
11958 CTX 5/1/14 John Hill [email protected] 6
12136 HeatProof 4/21/18 Brian Nisbet [email protected] 6
12141 Collings University 8/8/13 Sepehr Ashoori [email protected] 7
12345 ASET PLC 1/14/15 Jordi Palet Martinez jpalet [email protected] 5
12443 DENIL 2/18/14 Steve Balon [email protected] 12
12714 Zim Sales 3/5/13 Orlin Tenchev [email protected] 24
12802 Intelligence Systems 7/19/13 Musallam Alfarsi [email protected] 21
12808 Respira Networks 5/31/19 Sebastian Becker [email protected] 4
12811 EYN 3/23/14 Ignas Bagdonas [email protected] 21
12838 Pilco Streambank 9/20/13 Noora Balouma [email protected] 21
13063 xLAN Internet Exchange 8/5/20 Christoph Dietzel [email protected] 3
13301 ICANT 5/29/13 Shahab Vahabzadeh [email protected] 14
13824 Wiz Labs 4/12/14 Zubair Bin Abdul Kadar Shaik [email protected] 6
13875 Parmis Technologies 8/24/15 Hannaneh Hajiseyedjavadi [email protected] 10
14051 Euro-M 7/21/17 Marius Gruen [email protected] 9
14504 TQ Processes 10/28/14 Ondřej Caletka [email protected] 14
14515 Oglev 12/23/13 Ulf Kieber [email protected] 8
15329 Zconnect, Inc 5/2/13 Ihor Baranovskyi ibaranovskyi@zconnect,inc.com.au 7
15458 AHA Networks 1/18/16 Kevin Pack [email protected] 5
15627 West Telco 4/12/17 Ernest Byaruhanga [email protected] 12
15928 Ebony Telecoms 8/15/15 Karolína Hlobilová khlobilová@ebonytelecoms.com.au 6
15957 WWT 7/16/18 Milad Afshari [email protected] 3
17091 Mojbal 12/29/15 Miles McCredie [email protected] 12
17464 Fzig Fibre 7/2/18 Ole Jacobsen [email protected] 2
17769 Chirah Technologies 12/25/14 Gery Van Emelen gvan [email protected] 18
18487 Ares 1/24/19 Sebastian Lohff [email protected] 6
18536 IPI Bucharest 4/21/14 Ionut Sandu [email protected] 12
19488 Shaw Construction 11/22/17 Knut A. Syed [email protected] 8
20626 Epsilon Tech 8/23/14 Piotr Strzyżewski pstrzyż[email protected] 18
20636 TatSan 8/8/20 Seyed Ahmad Mousavi [email protected] 1
22216 Qinisar 5/26/16 Uta Meier-Hahn [email protected] 10
22329 UON 10/22/14 Badar Al Mamari bal [email protected] 12
22740 NetaAssist 4/26/15 Paul Thornton [email protected] 5

Menu

Cost of Ingredients for Menu Options
Ingredient Costs
Menu Code Category Dish Medium Large Off Menu Category Dishes Available Average Price Medium Average Price Large
2627 Mains Roast Turkey Platter $63 $95 Y Mains 28.00 $51.25 $77.11 48
1151 Mains Whole Turkey Roast with Stuffing $78 $117 Y Finger Foods 44.00 $40.55 $61.07 73
1236 Mains Honey Maple Glazed Leg Ham $88 $132 Salads 15.00 $26.07 $39.33 78
3039 Mains Seasoned Roast Pork with Crackling $53 $80 Desserts 20.00 $33.85 $51.10 48
1261 Mains Traditional Lamb Roast $75 $113 60
1003 Mains Fruit Cake with Frosting and Decoration $50 $75 Highest Price $140.00 45
3341 Mains Lasagna Vegetarian $42 $63 Lowest Price $19.00 37
2948 Mains Gnocchi (Spinach and Ricotta) $45 $68 30
3821 Mains Cannelloni (Beef) $40 $60 30
1316 Mains Lasagna (the way that Mama makes) $42 $63 37
3053 Mains Tortellini a la Panna $44 $66 29
3992 Mains Ravioli Fresh (Spinach and Ricotta) $39 $59 29
3908 Mains Ravioli Fresh (Veal) $34 $51 29
2864 Mains Gnocchi Con Patate $35 $53 30
3868 Mains Spaghetti Bolognaise Siciliana $39 $59 24
3295 Mains Marinara $51 $77 Y 41
2697 Mains Garlic Prawns and Pasta $53 $80 48
1189 Mains Cannelloni (Spinach & Ricotta) $35 $53 30
2346 Mains Eggplant Parmigiana $35 $53 30
1770 Mains Pineapple Glazed Ham $93 $140 78
3924 Mains Italian Pot Roast $78 $117 63
1873 Mains Roast Vegetables $40 $60 25
2267 Mains Chicken Cacciatore $40 $60 30
2430 Mains Chicken Stroganoff $43 $65 33
2792 Mains BBQ Chicken Platter $42 $63 32
1780 Mains Herb Crusted Leg of Lamb $70 $105 60
1913 Mains Roast Chicken Roll Platter $42 $63 32
3750 Mains Chicken Platter Trio $46 $69 31
1660 Finger Foods Vegetable Kebabs $32 $48 27
3483 Finger Foods Curry Puffs $32 $48 27
3781 Finger Foods Fish, Prawns and Calamari Platter $58 $87 48
3523 Finger Foods Sushi $33 $50 28
1467 Finger Foods Arancini Platter - Baby Size $35 $53 30
1831 Finger Foods Arancini Platter - Baby Size Vegetarian $45 $68 30
1185 Finger Foods Vegetarian Platter Cooked $39 $59 29
2965 Finger Foods Mini Quiches $40 $60 30
3413 Finger Foods Mini Vegetarian Pies $45 $68 30
2150 Finger Foods Ribbon Sandwiches - Vegetarian $31 $47 26
1793 Finger Foods Foccacia Platter - Vegetarian $37 $56 27
3203 Finger Foods Meatball Platter - Italian Style $32 $48 27
3726 Finger Foods Pizza Platter (Vegetarian) $32 $48 22
2379 Finger Foods Pizza Platter $37 $56 22
3414 Finger Foods Cutlet pieces and Arancini Platter $47 $71 32
1042 Finger Foods Foccacia Platter $32 $48 27
2557 Finger Foods Crumbed Calamari Platter $45 $68 30
3776 Finger Foods Fresh Seafood Platter $58 $87 43
2841 Finger Foods Antipasto (Mixed Meat, Cheese and Olive Platter) $35 $53 30
3388 Finger Foods Cutlets $38 $57 28
3587 Finger Foods Cooked Prawn Platter $47 $71 42
2135 Finger Foods Lemon Chicken Fingers Platter $34 $51 29
2154 Finger Foods Prosciutto Ham with Melon $38 $57 23
3693 Finger Foods Vegetarian Platter $28 $42 Y 23
1702 Finger Foods Hors d'oeuvres $32 $48 22
2072 Finger Foods Ribbon Sandwiches $36 $54 26
3923 Finger Foods Mini Spring Rolls $38 $57 23
1575 Finger Foods Mini Samosas $30 $45 20
2518 Finger Foods Cheesy Tomato Basil Mussels $41 $62 36
2388 Finger Foods Scallop and Bacon Bites $60 $90 50
2190 Finger Foods Coconut Prawns with Mango Sauce $58 $87 53
3643 Finger Foods Smoked Salmon and Camembert Puffs $35 $53 Y 25
3244 Finger Foods Lemon Ginger Prawns $55 $83 50
1601 Finger Foods Baked Oysters with Garlic Herb Butter $55 $83 40
1532 Finger Foods Chicken Drummettes $37 $56 27
2800 Finger Foods Chicken Cheese Patties $31 $47 26
2992 Finger Foods Glazed Chicken Bites $42 $63 27
2909 Finger Foods Mini Size Maxi Taste Sausage Rolls $37 $56 27
3906 Finger Foods Steamed Chilli Prawns $60 $90 50
1216 Finger Foods Spinach & Ricotta Puffs $37 $56 27
3791 Finger Foods Party Platter $42 $63 27
1449 Finger Foods Chicken Yakitori $44 $66 29
1181 Finger Foods Cheese & Herb Potato Croquettes $39 $59 29
3595 Finger Foods Chicken Cutlets $45 $68 30
3092 Salads Spicy Egg 'n Bacon Salad Platter $29 $44 14
2960 Salads Julienne Vegetable Salad $30 $45 15
3262 Salads Bocconcini Tomato Skewers $31 $47 26
1539 Salads Tuna Salad $30 $45 15
3826 Salads Festive Chicken Salad $22 $33 17
3436 Salads Garden Green Salad $24 $36 14
3412 Salads Cubed Potato with Sour Cream $31 $47 16
2746 Salads Italian Rice Salad $30 $45 15
1540 Salads Tangy Coleslaw $19 $29 14
2750 Salads Club Salad $29 $44 14
3810 Salads Greek Salad $30 $45 15
1043 Salads Waldorf Salad $24 $36 Y 14
3721 Salads Caesar Salad Variation $19 $29 14
1545 Salads Pecan and Avocado Salad $19 $29 Y 14
2649 Salads Seasonal Salad $24 $36 Y 14
2068 Desserts Fresh Fruit and Cheese Platter $42 $63 Y 27
3836 Desserts Peach and Raspberry Tea Cake $32 $48 17
1172 Desserts Custard Cakes - Chocolate Sicilian Cannoli $23 $35 18
2786 Desserts Custard Cakes - Vanilla Sicilian Cannoli $22 $33 17
2728 Desserts Orange & Almond (Gluten Free) $27 $41 17
1089 Desserts Friands (Gluten Free) $19 $29 Y 9
1655 Desserts Black Forest Cake $32 $48 17
2874 Desserts Italian Fruit Torte $42 $63 Y 27
1721 Desserts Tiramisu $27 $41 22
3681 Desserts Papered Cup Cakes (Petite) $59 $89 44
3116 Desserts Bacci Bomb $43 $65 28
2393 Desserts Cookies and Cream Cake $29 $44 19
2471 Desserts Apple, Rhubarb and Raspberry Tart $26 $39 21
2441 Desserts Salted Caramel Nut Tart $27 $41 22
3361 Desserts Lemon and Lime Tart $27 $41 22
2570 Desserts Apple Pie $27 $41 22
1220 Desserts Lemon Meringue Pie $37 $56 22
2884 Desserts Danish Pastry Platter $41 $62 26
2047 Desserts Raspberry-dusted Chocolate Fudge Truffels $45 $68 30
1667 Desserts Home-Made Biscuits $50 $75 35

JOB_4155

Catering Job Costs
Client Parmis Technologies Job Number:
Contact 4155
Job Description Sales Conference 8/11/20
FOOD COSTS (MATERIALS)
Menu Code Quantity Size Description Category Cost/Unit Total Cost
3341 3 Large
2697 3 Medium
1873 2 Large
2267 1 Medium
1780 3 Large
1913 2 Medium
1660 2 Medium
3523 2 Large
2965 3 Medium
1793 3 Medium
2557 2 Medium
3776 2 Medium
2841 1 Medium
2135 2 Large
3693 1 Large
3923 3 Large
2518 2 Large
2992 1 Large
1181 2 Medium
3262 2 Large
3436 1 Large
3810 1 Large
2874 1 Large
1721 3 Medium
3361 1 Large
2570 1 Medium
TOTAL FOOD COSTS $ 0.00
LABOUR COSTS
Date Temp Staff Staff Number Details/Description Hours Day Cost
8/16/20 Temp Chef 1 Prepare Ingredients 5
8/17/20 Temp Chef 2 Preparation and Cooking 8
8/18/20 Temp Chef 2 Cooking 8
8/18/20 General Help 2 Set-up at venue 4
8/19/20 Temp Chef 2 Cooking and Plating 6
8/19/20 Wait Staff 3 Table Service 6
8/20/20 General Help 2 Pack up and clean-up 4
TOTAL LABOUR $ - 0
PRODUCTION OVERHEADS
Days Cost
TOTAL OVERHEADS
SUMMARY
TOTAL COST Quoted Price (before GST) $ 12,400.00
Gross Profit Margin (%)
Yung Zu Rayon
Camo Cotton Pattern cut main
Estoro Lining SX Pattern cut main & lining
Maestro Braid Machining and braid trim
Albury Stitchcloth Machining and piping
3 mm Red Piping Packing
Buttons Supervision and indirect
Thread
Packing

Journals

Journal Entries for JOB_4155
Account Debit Credit Available Journal Accounts
Accounts Payable
Purchase of Direct Materials (on Account) Accounts Receivable
Materials Inventory $0.00 Manufacturing Overhead
Accounts Payable $0.00 Cost of Goods Sold
Finished Goods Inventory
Issue of direct materials to production Materials Inventory
Sales
Wages Payable
Work in Process Inventory
Direct labour incurred
Overhead applied to production
Cost of finished production transferred to Finished Goods store
Cost of finished product sold
August Sales

Multi Choice

Q3 Job List
Job Number Date Client ID Client Name Revenue Fully Paid Client ID
4148 7/2/20 15928 Ebony Telecoms $7,335.00 Yes 4153 1 2
4149 7/5/20 15458 AHA Networks $10,571.00 Yes 2 3
4150 7/8/20 14051 Euro-M $5,488.00 Highest Revenue 3 4
4151 7/13/20 17769 Chirah Technologies $23,128.00 Yes $23,128.00 4 1
4152 7/17/20 20626 Epsilon Tech $16,456.00 Yes
4153 7/20/20 19488 Shaw Construction $17,585.00
4154 7/24/20 17464 $17,596.00 Yes
4155 7/26/20 13824 Wiz Labs $19,000.00 No Call
5 1 How many rows in an excel worksheet? How many columns in an excel worksheet? How many rows in an excel worksheet?
1 A Infinite FALSE 4 26 FALSE 1048576 TRUE
2 B 1048576 FALSE 1 1024 FALSE 1024 FALSE
3 C 1024 FALSE 2 16384 TRUE 16384 FALSE
4 D 16384 TRUE 3 Infinite FALSE Infinite FALSE
1 2 Which keyboard shortcut creates an absolute reference? Which keyboard shortcut creates an absolute reference? Which of the following is a relative cell reference?
1 A Alt+F4 FALSE 2 F4 TRUE B3 TRUE
2 B Ctrl+4 TRUE 3 Alt+F4 FALSE $B3 FALSE
3 C Shift+4 FALSE 4 Ctrl+4 FALSE B$3 FALSE
4 D F4 FALSE 1 Shift+4 FALSE $B$3 FALSE
1 3 Numbers in Excel automatically align Numbers in Excel automatically align Which formatting option has been applied to the revenue data?
1 A Top Right FALSE 2 Bottom Right TRUE Currency TRUE
2 B Top Left TRUE 3 Bottom Left FALSE Accounting FALSE
3 C Bottom Right FALSE 4 Top Right FALSE Number FALSE
4 D Bottom Left FALSE 1 Top Left FALSE General FALSE
5 4 The formula =COUNT(E6:F13) will return The formula =COUNT(E6:E13) will return The formula =COUNT(E6:F13) will return
1 A 15 FALSE 2 7 FALSE 16 FALSE
2 B 8 FALSE 3 8 FALSE 15 FALSE
3 C An error FALSE 4 0 TRUE 8 TRUE
4 D 16 TRUE 1 An error FALSE An error FALSE
5 5 Which of the following would calculate the most recent job date? Which of the following would calculate average revenue? Which of the following would calculate the most recent job date?
1 A =MAX(C6:C13) FALSE 2 =AVERAGE(F6:F13) TRUE =DATE(C6:C13,0) FALSE
2 B =MINIFS(C6:C13,"<"&TODAY()) FALSE 3 =(F6:F13)/8 FALSE =RECENT(C6:C13) FALSE
3 C =DATE(C6:C13,0) FALSE 4 =AVG(F6:F13) FALSE =MAX(C6:C13) TRUE
4 D =RECENT(C6:C13) TRUE 1 '=AVERAGEIFS(F6:F13) FALSE =MINIFS(C6:C13,"<"&TODAY()) FALSE
5 6 What is wrong with the following calculation =COUNTIFS(G6:G13,Yes)? Which formula will correctly calculate how many clients have paid in full? What is wrong with the following calculation =COUNTIFS(G6:G13,Yes)?
1 A Should be a SUMIFS TRUE 2 =SUM(G6:G13) FALSE Missing a check for empty cells FALSE
2 B Missing an absolute cell reference FALSE 3 =SUMIFS(G6:G13,"Yes") FALSE Should be a SUMIFS FALSE
3 C Missing quotes around the Yes FALSE 4 =COUNTIFS(G6:G13,"Yes") TRUE Missing an absolute cell reference FALSE
4 D Missing a check for empty cells FALSE 1 =COUNT(G6:G13) FALSE Missing quotes around the Yes TRUE
1 7 The formula =VLOOKUP(I9,B6:G13,1,FALSE) will return an error, why? The formula =VLOOKUP(I9,B6:G13,1,FALSE) will return an error, why? What is wrong with this formula =VLOOKUP(I6,B6:G13,5)?
1 A Because it is an exact match lookup FALSE 1 Because it is a range lookup FALSE It should use an absolute cell reference FALSE
2 B Because the lookup range has not been made absolute FALSE 2 Because it is an exact match lookup FALSE It should be an exact match TRUE
3 C Because the lookup value is not in the first column FALSE 3 Because the lookup range has not been made absolute FALSE Missing quotes around the 5 FALSE
4 D Because it is a range lookup TRUE 4 Because the lookup value is not in the first column TRUE Nothing, it is correct FALSE
1 8 The formula =IF(AND(F13>19000,G13<>"Yes"),"Call","No Call") will return The formula =IF(AND(F13>19000,G13<>"Yes"),"Call","No Call") will return The formula =IF(AND(F13>19000,G13<>"Yes"),"Call","No Call") will return
1 A No Call FALSE 2 Yes FALSE Yes FALSE
2 B An error FALSE 3 Call FALSE Call FALSE
3 C Yes FALSE 4 No Call TRUE No Call TRUE
4 D Call TRUE 1 An error FALSE An error FALSE