You work for a company called Pro-NET that design and install network solutions. You are required to provide a spreadsheet that will help cost 'individual

You work for a company called Pro-NET that design and install network solutions. You are required to provide a spreadsheet that will help cost 'individual jobs and provide a summary of completed jobs for the year. Download the instruction sheet and follow all instructions carefully.


PLEASE NOTE:
Do not use any rounding functions in formulas unless specifically requested.
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 light green cells should be changed and with the exception of the journal names, these should all contain formulas
Formulas should be flexible enough to work with different values and regardless of sort order
Do not create formulas or paste values in cells other than the ones indicated

image text in transcribed

B15 1 Information Overhead is applied to jobs using a pre-determined rate per direct labour hour based on the following annual budgets: 4 Budgeted overhead 48,000.00 5 Budgeted direct labour hours 6,000 2,50,000.00 7 Job 3820 was invoiced on 26 November for the quoted price of: 10 Journal Accounts 11 Accounts Payable 12 Accounts Receivable 13 Admin Overhead 14 Cost of Goods Sold 15 Finished Goods Inventory 16 Materials Inventory 17 Sales 18 Wages Payable 19 Work in Process Inventory FALSE x fx Pro-NET HR Data Date: 18/12/13 Employee ID E1002 E1004 E1012 E1015 E1016 E1017 E1023 E1028 Employee Name James Mason Lemin Lu Sam Viney Cynthia Chang Divika Kapoor Bin Wang Andy Forbes Jordan Riddle Job Title Office Project Manager Sydney Senior Engineer Melboume Sales Engineer Project Manager Sydney Sales Engineer Melboume Engineer Sydney Project Manager Melboume Engineer Sydney Start Date 21/10/13 08/11/13 25/01/14 20/05/161 04/06/16 25/06/16 29/12/17 22/07/18 Years Service 6.2 16.1 5.91 3.6 3.51 Super 11% $ 9% $ 9% $ 11% $ 9% $ 9% $ 11% $ % $ Annual Salar 1,48,000.00 1,10,000.00 1,23,000.00 1,42,000.00 1,21,000.00 98,000.00 1,40,000.00 92,000.00 . 2.0 1. 4 9 579 Employee of the Month: Lemin Lu 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 1 How many rows in an excel worksheet? A 1048576 B 1024 C 16384 D Infinite FALSE FALSE FALSE FALSE 2 Which of the following is a relative cell reference? A $B3 5 B B$3 6 C $B$3 7D B3 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 19 3 Numbers in Excel automatically align 30 A Top Left 31 B Bottom Right 32 C Bottom Left 33 D Top Right 34 35 4 The formula =COUNT(B3:B11) will return 36 A 9 BO Overview Information FALSE A HR Data Completed Jobs Stats Labour Costs Inventory JOB 4 The formula =COUNT(B3:B11) will return A 9 . C An error D 8 FALSE FALSE FALSE FALSE 5 Which of the following would calculate the average salary over 100000 A =AVERAGEIFS(14:111,>100000) B =AVERAGEIFS(14:111)>100000 C AVERAGEIFS(14:111)>"100000" ID =AVERAGEIFS(14:111,14:111,">100000") FALSE FALSE FALSE FALSE 6 Which formula when entered in G4 and copied down will correctly calculate Years Service for all staff A =11-($F$4/365.25) FALSE B =(11-$F$47365.25 FALSE c =($I$ 1-F4y365.25 FALSE D =($I$1-F4/365.25) FALSE 3 7. The formula =VLOOKUP(D13,B3:111,1,FALSE) will return an 41A Because it is not an exact match 5. B Because it is not a range lookup C Because an absolute cell reference has not been used W D Because the lookup value is to the right of the value you wish to retum FALSE FALSE FALSE FALSE C Percentage D O klin Gothic Bo... 10 A A I U V HA Conditional Formatting Format as Table Cell Styles Insert BX Delete Format CE - % ) Editing fx 6% CETG pleted Job Summary for 2018 M N O Total Revenue Price Before Discount Discount Amount Discount % Gross Profit Margin % 6% 4% 7% 4% 3% % 5% 8% % 8 6 Date Completed Location 3/19 14/03/19 Sydney 4/19 1 7/04/19 Sydney 03/19 31/03/19M elbourne 04/19 2 7/04/19 Sydney 04/191 21/04/191 Sydney 04/19_ 21/04/19 Perth 04/19 14/05/19 Perth 05/19) 03/06/191 Melboume 04/19 06/05/19 Sydney /06/19 1 8/06/19 Perth /06/19 19/06/19 Perth 707/19 19/07/19 Melbourne 9/06/19 1 5/06/19 Perth /06/19 2 9/06/19 Perth 5/08/19 2 2/08/19Sydney 2/08/19 1 5/08/19 Perth 3/08/19 2 9/08/19) Melbourne 4/08/19 24/08/19 Perth 7/10/19 21/10/19 Melboume 26/09/19 11/10/19 Perth 22/10/19 26/10/19 Sydney Quoted Price (less discount) $32,350.00 $50,950.00 $19,300.00 $64.250.001 $30.450.00 $22,200. 00 $43,800.00 $64,400.00 $26,150. 00 $75,300.00 $42.700.00 $2,69,000.00 $9,500.00 $35,800.00 $57,850.00 $38,000.00 $46,700.00 $19,400.00 $51,650.00 $14.450.00 $75,050.00 Material Cost $25,320.86 $39.354.71 $13,717.30 $45,161.93 $16.760.16 $12,018.55 $24,433.85 $56,197.441 $14,022.84 $46,018.56 $23,950.59 $1,53,384.98 $6,096.38 $21,848.40 $52,295.76 $28,724.68 $38,265.70 $12,899.14 $38,880.27 $9.607,84 $54,093.31 Labour Cost $2,391.61 $3,194.57 $1,664.28 $5,037.98 $2.239.101 $1,114.30 $2,611.66 $5.477.471 $1,849.50 $5,776.82 $2,263.02 $36,548.76 $862.22 $2,467.48 $4,731.48 $3,168.20 $4,493.28 $1,632.96 $4,228.22 $793,07 $5,176.08 Production Overhead COGS $640.00 $576. 00T $768.00 $576.00 $832.00 $832.00 $1,216.00 $832.00 $512.00 $640.00 $960.00 $768.00 $832.00 $320.00 $1,088.00 $384.00 $384.00 $1,280.00 $896.00 $960.00 $256.00 . 7% 3% 4% 6% 7% 6% 5% 8% 6% 5% 6%! ome Insert View Data = Review = General 8 5 Draw Page Layout Formulas Arial 1 v 10 A B I UV / v O A & fix Com For Cell Paste E E AO - % 9 - 26 Pro-NET Stats 2018 mo Total Revenue Gross Profit (in $) Average Labour Cost Highest Revenue Earned Lowest Revenue Earned Revenue by Manager and Region Melbourne Number of Jobs Sydney Perth 10 11 Manager 12 Andy Forbes 13 Cynthia Chang 14 James Mason 15 Lemin Lu View Format Tools Data Excel File AutoSave Edit OFF Insert S U Window Help ACCG200 Excel Assessment 2019 33 ne Insert Draw Page Layout Formulas Data Review View = = abe General Franklin Gothic Bo.. 10 v A A ste & BI U v ra . A x v fx Conditional Forma Format as Table Cell Styles d % to GT Pro-NET Labour Cost Estimates by Activity Rate Category 1000 2000 3000 4000 Description General Specialised Highly Specialised Management Rata $92.00 $110.00 $120.00 $144.00 Activity Code 4026 3027 3028 1022 3035 1033 2064 1012 1013 2045 1099 2050 2024 4580 Special General Description Customer Consulation High Level Design Low Level Design Installation System Commissioning Training Trouble Shooting Services Cable Installations on Campus Cable Installations off Campus Configuration Review Documentation Handover Data Network Repair Project Management Tools File Edit toSave OFF View A Insert S Format 5 = Data Window Help ACCG200 Excel Assessment 2019 S3 Q- Search Sheet Draw t Page Layout Formulas Data Review Share View Comment 10 A Insert A = = = General Franklin Gothic Bo... BI U r i- Conditional Formatting Format as Table Cell Styles Deletev O Editing Ideas a. A 8 pe cer setzes Tablew -% 600 Format X. fx FES-port 10/100Base-TX (RJ45) plus 4-port 1000Base-T and 1000Base-X (SFP) with AC power supply letwork Equipment Inventory Category Retail Price R eorder? Reorder Oty 2016 Standard Markup: Premium Markup: Minimum Stock Level: 259 L Reorder Lookup Category Premium Regular 25 20 |100000) B =AVERAGEIFS(14:111)>100000 C AVERAGEIFS(14:111)>"100000" ID =AVERAGEIFS(14:111,14:111,">100000") FALSE FALSE FALSE FALSE 6 Which formula when entered in G4 and copied down will correctly calculate Years Service for all staff A =11-($F$4/365.25) FALSE B =(11-$F$47365.25 FALSE c =($I$ 1-F4y365.25 FALSE D =($I$1-F4/365.25) FALSE 3 7. The formula =VLOOKUP(D13,B3:111,1,FALSE) will return an 41A Because it is not an exact match 5. B Because it is not a range lookup C Because an absolute cell reference has not been used W D Because the lookup value is to the right of the value you wish to retum FALSE FALSE FALSE FALSE C Percentage D O klin Gothic Bo... 10 A A I U V HA Conditional Formatting Format as Table Cell Styles Insert BX Delete Format CE - % ) Editing fx 6% CETG pleted Job Summary for 2018 M N O Total Revenue Price Before Discount Discount Amount Discount % Gross Profit Margin % 6% 4% 7% 4% 3% % 5% 8% % 8 6 Date Completed Location 3/19 14/03/19 Sydney 4/19 1 7/04/19 Sydney 03/19 31/03/19M elbourne 04/19 2 7/04/19 Sydney 04/191 21/04/191 Sydney 04/19_ 21/04/19 Perth 04/19 14/05/19 Perth 05/19) 03/06/191 Melboume 04/19 06/05/19 Sydney /06/19 1 8/06/19 Perth /06/19 19/06/19 Perth 707/19 19/07/19 Melbourne 9/06/19 1 5/06/19 Perth /06/19 2 9/06/19 Perth 5/08/19 2 2/08/19Sydney 2/08/19 1 5/08/19 Perth 3/08/19 2 9/08/19) Melbourne 4/08/19 24/08/19 Perth 7/10/19 21/10/19 Melboume 26/09/19 11/10/19 Perth 22/10/19 26/10/19 Sydney Quoted Price (less discount) $32,350.00 $50,950.00 $19,300.00 $64.250.001 $30.450.00 $22,200. 00 $43,800.00 $64,400.00 $26,150. 00 $75,300.00 $42.700.00 $2,69,000.00 $9,500.00 $35,800.00 $57,850.00 $38,000.00 $46,700.00 $19,400.00 $51,650.00 $14.450.00 $75,050.00 Material Cost $25,320.86 $39.354.71 $13,717.30 $45,161.93 $16.760.16 $12,018.55 $24,433.85 $56,197.441 $14,022.84 $46,018.56 $23,950.59 $1,53,384.98 $6,096.38 $21,848.40 $52,295.76 $28,724.68 $38,265.70 $12,899.14 $38,880.27 $9.607,84 $54,093.31 Labour Cost $2,391.61 $3,194.57 $1,664.28 $5,037.98 $2.239.101 $1,114.30 $2,611.66 $5.477.471 $1,849.50 $5,776.82 $2,263.02 $36,548.76 $862.22 $2,467.48 $4,731.48 $3,168.20 $4,493.28 $1,632.96 $4,228.22 $793,07 $5,176.08 Production Overhead COGS $640.00 $576. 00T $768.00 $576.00 $832.00 $832.00 $1,216.00 $832.00 $512.00 $640.00 $960.00 $768.00 $832.00 $320.00 $1,088.00 $384.00 $384.00 $1,280.00 $896.00 $960.00 $256.00 . 7% 3% 4% 6% 7% 6% 5% 8% 6% 5% 6%! ome Insert View Data = Review = General 8 5 Draw Page Layout Formulas Arial 1 v 10 A B I UV / v O A & fix Com For Cell Paste E E AO - % 9 - 26 Pro-NET Stats 2018 mo Total Revenue Gross Profit (in $) Average Labour Cost Highest Revenue Earned Lowest Revenue Earned Revenue by Manager and Region Melbourne Number of Jobs Sydney Perth 10 11 Manager 12 Andy Forbes 13 Cynthia Chang 14 James Mason 15 Lemin Lu View Format Tools Data Excel File AutoSave Edit OFF Insert S U Window Help ACCG200 Excel Assessment 2019 33 ne Insert Draw Page Layout Formulas Data Review View = = abe General Franklin Gothic Bo.. 10 v A A ste & BI U v ra . A x v fx Conditional Forma Format as Table Cell Styles d % to GT Pro-NET Labour Cost Estimates by Activity Rate Category 1000 2000 3000 4000 Description General Specialised Highly Specialised Management Rata $92.00 $110.00 $120.00 $144.00 Activity Code 4026 3027 3028 1022 3035 1033 2064 1012 1013 2045 1099 2050 2024 4580 Special General Description Customer Consulation High Level Design Low Level Design Installation System Commissioning Training Trouble Shooting Services Cable Installations on Campus Cable Installations off Campus Configuration Review Documentation Handover Data Network Repair Project Management Tools File Edit toSave OFF View A Insert S Format 5 = Data Window Help ACCG200 Excel Assessment 2019 S3 Q- Search Sheet Draw t Page Layout Formulas Data Review Share View Comment 10 A Insert A = = = General Franklin Gothic Bo... BI U r i- Conditional Formatting Format as Table Cell Styles Deletev O Editing Ideas a. A 8 pe cer setzes Tablew -% 600 Format X. fx FES-port 10/100Base-TX (RJ45) plus 4-port 1000Base-T and 1000Base-X (SFP) with AC power supply letwork Equipment Inventory Category Retail Price R eorder? Reorder Oty 2016 Standard Markup: Premium Markup: Minimum Stock Level: 259 L Reorder Lookup Category Premium Regular 25 20 |