excel assignment

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

Information

ACCG2000 Excel Assignment 2020 Semester 2
Student Name:
Student Number:
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 columns in an excel worksheet?
A 1024 FALSE
B 16384 FALSE
C Infinite FALSE
D 26 FALSE
2 Which keyboard shortcut creates an absolute reference?
A Ctrl+4 FALSE
B Shift+4 FALSE
C F4 FALSE
D Alt+F4 FALSE
3 Numbers in Excel automatically align
A Top Left FALSE
B Bottom Right FALSE
C Bottom Left FALSE
D Top Right FALSE
4 The formula =COUNT(E6:E13) will return
A 0 FALSE
B An error FALSE
C 7 FALSE
D 8 FALSE
5 Which of the following would calculate the most recent job date?
A =MAX(C6:C13) FALSE
B =MINIFS(C6:C13,"<"&TODAY()) FALSE
C =DATE(C6:C13,0) FALSE
D =RECENT(C6:C13) FALSE
6 Which formula will correctly calculate how many clients have paid in full?
A =COUNTIFS(G6:G13,"Yes") FALSE
B =COUNT(G6:G13) FALSE
C =SUM(G6:G13) FALSE
D =SUMIFS(G6:G13,"Yes") FALSE
7 The formula =VLOOKUP(I9,B6:G13,1,FALSE) will return an error, why?
A Because it is a range lookup FALSE
B Because it is an exact match lookup FALSE
C Because the lookup range has not been made absolute FALSE
D Because the lookup value is not in the first column FALSE
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 FALSE

Cost Overview

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

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 48
1151 Mains Whole Turkey Roast with Stuffing $88 $132 Y Finger Foods 73
1236 Mains Honey Maple Glazed Leg Ham $88 $132 Salads 78
3039 Mains Seasoned Roast Pork with Crackling $63 $95 Desserts 48
1261 Mains Traditional Lamb Roast $75 $113 60
1003 Mains Fruit Cake with Frosting and Decoration $55 $83 Highest Price 45
3341 Mains Lasagna Vegetarian $42 $63 Lowest Price 37
2948 Mains Gnocchi (Spinach and Ricotta) $40 $60 30
3821 Mains Cannelloni (Beef) $40 $60 30
1316 Mains Lasagna (the way that Mama makes) $52 $78 37
3053 Mains Tortellini a la Panna $39 $59 29
3992 Mains Ravioli Fresh (Spinach and Ricotta) $39 $59 29
3908 Mains Ravioli Fresh (Veal) $39 $59 29
2864 Mains Gnocchi Con Patate $40 $60 30
3868 Mains Spaghetti Bolognaise Siciliana $29 $44 24
3295 Mains Marinara $56 $84 Y 41
2697 Mains Garlic Prawns and Pasta $58 $87 48
1189 Mains Cannelloni (Spinach & Ricotta) $35 $53 30
2346 Mains Eggplant Parmigiana $40 $60 30
1770 Mains Pineapple Glazed Ham $93 $140 78
3924 Mains Italian Pot Roast $73 $110 63
1873 Mains Roast Vegetables $30 $45 25
2267 Mains Chicken Cacciatore $35 $53 30
2430 Mains Chicken Stroganoff $48 $72 33
2792 Mains BBQ Chicken Platter $47 $71 32
1780 Mains Herb Crusted Leg of Lamb $65 $98 60
1913 Mains Roast Chicken Roll Platter $42 $63 32
3750 Mains Chicken Platter Trio $41 $62 31
1660 Finger Foods Vegetable Kebabs $42 $63 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 $44 $66 29
2965 Finger Foods Mini Quiches $35 $53 30
3413 Finger Foods Mini Vegetarian Pies $40 $60 30
2150 Finger Foods Ribbon Sandwiches - Vegetarian $31 $47 26
1793 Finger Foods Foccacia Platter - Vegetarian $42 $63 27
3203 Finger Foods Meatball Platter - Italian Style $42 $63 27
3726 Finger Foods Pizza Platter (Vegetarian) $27 $41 22
2379 Finger Foods Pizza Platter $32 $48 22
3414 Finger Foods Cutlet pieces and Arancini Platter $47 $71 32
1042 Finger Foods Foccacia Platter $37 $56 27
2557 Finger Foods Crumbed Calamari Platter $40 $60 30
3776 Finger Foods Fresh Seafood Platter $53 $80 43
2841 Finger Foods Antipasto (Mixed Meat, Cheese and Olive Platter) $35 $53 30
3388 Finger Foods Cutlets $43 $65 28
3587 Finger Foods Cooked Prawn Platter $57 $86 42
2135 Finger Foods Lemon Chicken Fingers Platter $44 $66 29
2154 Finger Foods Prosciutto Ham with Melon $38 $57 23
3693 Finger Foods Vegetarian Platter $33 $50 Y 23
1702 Finger Foods Hors d'oeuvres $27 $41 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 $46 $69 36
2388 Finger Foods Scallop and Bacon Bites $65 $98 50
2190 Finger Foods Coconut Prawns with Mango Sauce $63 $95 53
3643 Finger Foods Smoked Salmon and Camembert Puffs $40 $60 Y 25
3244 Finger Foods Lemon Ginger Prawns $55 $83 50
1601 Finger Foods Baked Oysters with Garlic Herb Butter $45 $68 40
1532 Finger Foods Chicken Drummettes $32 $48 27
2800 Finger Foods Chicken Cheese Patties $36 $54 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 $55 $83 50
1216 Finger Foods Spinach & Ricotta Puffs $42 $63 27
3791 Finger Foods Party Platter $37 $56 27
1449 Finger Foods Chicken Yakitori $39 $59 29
1181 Finger Foods Cheese & Herb Potato Croquettes $44 $66 29
3595 Finger Foods Chicken Cutlets $40 $60 30
3092 Salads Spicy Egg 'n Bacon Salad Platter $19 $29 14
2960 Salads Julienne Vegetable Salad $20 $30 15
3262 Salads Bocconcini Tomato Skewers $31 $47 26
1539 Salads Tuna Salad $25 $38 15
3826 Salads Festive Chicken Salad $27 $41 17
3436 Salads Garden Green Salad $29 $44 14
3412 Salads Cubed Potato with Sour Cream $21 $32 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 $19 $29 14
3721 Salads Caesar Salad Variation $19 $29 Y 14
1545 Salads Pecan and Avocado Salad $24 $36 14
2649 Salads Seasonal Salad $24 $36 14
2068 Desserts Fresh Fruit and Cheese Platter $37 $56 27
3836 Desserts Peach and Raspberry Tea Cake $22 $33 17
1172 Desserts Custard Cakes - Chocolate Sicilian Cannoli $23 $35 Y 18
2786 Desserts Custard Cakes - Vanilla Sicilian Cannoli $32 $48 Y 17
2728 Desserts Orange & Almond (Gluten Free) $32 $48 17
1089 Desserts Friands (Gluten Free) $19 $29 9
1655 Desserts Black Forest Cake $22 $33 17
2874 Desserts Italian Fruit Torte $32 $48 Y 27
1721 Desserts Tiramisu $37 $56 22
3681 Desserts Papered Cup Cakes (Petite) $59 $89 44
3116 Desserts Bacci Bomb $43 $65 28
2393 Desserts Cookies and Cream Cake $34 $51 19
2471 Desserts Apple, Rhubarb and Raspberry Tart $26 $39 21
2441 Desserts Salted Caramel Nut Tart $32 $48 22
3361 Desserts Lemon and Lime Tart $37 $56 22
2570 Desserts Apple Pie $32 $48 22
1220 Desserts Lemon Meringue Pie $27 $41 22
2884 Desserts Danish Pastry Platter $36 $54 26
2047 Desserts Raspberry-dusted Chocolate Fudge Truffels $45 $68 30
1667 Desserts Home-Made Biscuits $40 $60 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 1 Large
2697 3 Large
1873 2 Large
2267 3 Medium
1780 3 Large
1913 3 Medium
1660 3 Large
3523 2 Medium
2965 2 Large
1793 2 Large
2557 3 Large
3776 1 Large
2841 3 Medium
2135 1 Large
3693 2 Large
3923 3 Medium
2518 2 Large
2992 2 Large
1181 2 Large
3262 3 Large
3436 3 Medium
3810 1 Large
2874 3 Large
1721 1 Large
3361 2 Large
2570 3 Large
TOTAL FOOD COSTS $ 0.00
LABOUR COSTS
Date Temp Staff Staff Number Details/Description Hours Day Cost
8/17/20 Temp Chef 1 Prepare Ingredients 5
8/18/20 Temp Chef 2 Preparation and Cooking 8
8/19/20 Temp Chef 2 Cooking 8
8/19/20 General Help 2 Set-up at venue 4
8/20/20 Temp Chef 2 Cooking and Plating 6
8/20/20 Wait Staff 3 Table Service 6
8/21/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,250.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
1 1 How many columns in an excel worksheet? How many columns in an excel worksheet? How many rows in an excel worksheet?
1 A 1024 FALSE 4 26 FALSE 1048576 TRUE
2 B 16384 TRUE 1 1024 FALSE 1024 FALSE
3 C Infinite FALSE 2 16384 TRUE 16384 FALSE
4 D 26 FALSE 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 Ctrl+4 FALSE 3 F4 TRUE B3 TRUE
2 B Shift+4 FALSE 4 Alt+F4 FALSE $B3 FALSE
3 C F4 TRUE 1 Ctrl+4 FALSE B$3 FALSE
4 D Alt+F4 FALSE 2 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 Left FALSE 2 Bottom Right TRUE Currency TRUE
2 B Bottom Right TRUE 3 Bottom Left FALSE Accounting FALSE
3 C Bottom Left FALSE 4 Top Right FALSE Number FALSE
4 D Top Right FALSE 1 Top Left FALSE General FALSE
1 4 The formula =COUNT(E6:E13) will return The formula =COUNT(E6:E13) will return The formula =COUNT(E6:F13) will return
1 A 0 TRUE 3 7 FALSE 16 FALSE
2 B An error FALSE 4 8 FALSE 15 FALSE
3 C 7 FALSE 1 0 TRUE 8 TRUE
4 D 8 FALSE 2 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) TRUE 3 =AVERAGE(F6:F13) TRUE =DATE(C6:C13,0) FALSE
2 B =MINIFS(C6:C13,"<"&TODAY()) FALSE 4 =(F6:F13)/8 FALSE =RECENT(C6:C13) FALSE
3 C =DATE(C6:C13,0) FALSE 1 =AVG(F6:F13) FALSE =MAX(C6:C13) TRUE
4 D =RECENT(C6:C13) FALSE 2 '=AVERAGEIFS(F6:F13) FALSE =MINIFS(C6:C13,"<"&TODAY()) FALSE
1 6 Which formula will correctly calculate how many clients have paid in full? 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 =COUNTIFS(G6:G13,"Yes") TRUE 3 =SUM(G6:G13) FALSE Missing a check for empty cells FALSE
2 B =COUNT(G6:G13) FALSE 4 =SUMIFS(G6:G13,"Yes") FALSE Should be a SUMIFS FALSE
3 C =SUM(G6:G13) FALSE 1 =COUNTIFS(G6:G13,"Yes") TRUE Missing an absolute cell reference FALSE
4 D =SUMIFS(G6:G13,"Yes") FALSE 2 =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 a range lookup FALSE 1 Because it is a range lookup FALSE It should use an absolute cell reference FALSE
2 B Because it is an exact match lookup FALSE 2 Because it is an exact match lookup FALSE It should be an exact match TRUE
3 C Because the lookup range has not been made absolute FALSE 3 Because the lookup range has not been made absolute FALSE Missing quotes around the 5 FALSE
4 D Because the lookup value is not in the first column TRUE 4 Because the lookup value is not in the first column TRUE Nothing, it is correct FALSE
5 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 TRUE 3 Yes FALSE Yes FALSE
2 B An error FALSE 4 Call FALSE Call FALSE
3 C Yes FALSE 1 No Call TRUE No Call TRUE
4 D Call FALSE 2 An error FALSE An error FALSE