Quiz starts from here:

You are a Project Manager for residential building construction projects. You are involved in three projects namely- Bankstown, Liverpool, and Cronulla (make sure you label your projects with these names), which are under construction and lump sum contract value of these projects are as below:

Bankstown: Your Student ID (for example if your student Id is 12345678 then project contract sums is $12345678)

Liverpool: Any random value between your Student ID Minus $85,000 and your ID plus $105,000 (cell referencing must be used)

Cronulla: Any random value between your Student ID Minus $60,000 and your ID plus $90,000 (cell referencing must be used)

Claims to be made for the project activities are based on the distribution provided in the Spreadsheet.

The following assumptions can be made to complete questions below:

1. You are making 6.5% to 11.5% (randomly) profit on these activities;
2. All the projects have started at the same time and will finish on same day

By using above data complete the following questions in the Spreadsheet provided in this quiz [Quiz Excel File].

Question 1 (10.5 marks)

1. Write your student ID in the "N1" cell and write Lower and Higher numbers in the "N2" and "N3" cells respectively and calculate the contract sum in the "G4" cell. (1 mark)

1. A variation will apply to every activity listed applies to the project's cost table randomly varies between 7% of Initial Claim Per activity ($) and 12% of Initial Claim Per activity ($). The variation amount for the activities must be added to your "Initial Claim" amount per activity ($) for each trade to work out total final claim eg., Total Final Claim for an activity = Initial Claim per activity ($) + Variation. (Use the random function to generate variation amounts). (1.5 marks)

1. Using appropriate Spreadsheet formulae complete project cost tables of all three projects in separate Spreadsheets (Tabs) by naming them as-Bankstown, Liverpool, and Cronulla. For your convenience, the following headings of the columns have already been entered in the given Excel file. (5.0 marks)

1. Pay 6.5% of the Total Final Claim (G) as a bonus for the activities where more than 12.5% profit has been achieved. No bonus to be paid for other activities. You must use Excel logical function to calculate bonus. (1.5 marks)
2. Format all the cost tables created in theBankstown, Liverpool, and Cronullaworksheets in a professional manner. (1.5 marks)

Question 2 (4.5 marks)

1. Complete a separate Worksheet by linking appropriate data from theBankstown, Liverpool, and Cronulla worksheets and name it 'Summary'. Summary Worksheet should include the following data - (2 marks)

Project

Total Claim ($)

Profit ($)

Net Profit ($)

1. Draw a Bar chart on the summary worksheet to show the % profit for each activity for a selected project.(1.5 marks)
2. Format the summary cost table & the chart in a professional manner. (1 mark)

Question 3 (3.0 marks)

1. Draw a Cash flow graph for Cronulla showing Payment Date vs Final Cumulative Claim.Graph must be drawn under the Cronulla Cost table. (2 marks)
2. Format the Cashflow graph in a professional manner. (1 mark)

Question 4 (1.5 marks)

Insert a Worksheet at the beginning of your workbook and name it as "Main Menu". In this worksheet create a button using either VBA/Macro function OR HyperLink function to go to Liverpool project and label it as "Go to Bankstown Project". Create another link on Liverpool worksheet labelled as "Go to Main Menu". These menu buttons should be easily visible in the worksheets and functional.

	Write Your Company NameConstructions Pty Ltd									SID:	19788135
												Lower number:	2000
	Bill of Quantities											Higher number:	4000
					Contract sum:	19794135
	Project name:	autumn
	Date:	11/1/22
Cost Centre	Activity	Payment Date	Initial Claim (% of contract sum)	Initial Claim per activity ($)	Variation ($) (G) (Read instructions sheet)	Total Final Claim ($) (H=G+D)	Final Cumulative Claim ($)	% Profit (%)	Profit ($)	Bonus ($)	Net Profit ($)
110	Preliminary	Day 20	8%	1583530.8	1773554.496	3357085.296	3357085.296
120	Earth work	Day 30	15%	2969120.25	3206649.87	6175770.12	9532855.416
130	Concretor	Day 45	16%	3167061.6	3483767.76	6650829.36	16183684.78
140	Brickwork	Day 60	22%	4354709.7	4746633.573	9101343.273	25285028.05
150	Carpenter	Day 75	15%	2969120.25	3266032.275	6235152.525	31520180.57
160	Roof	Day 90	24%	4750592.4	5083133.868	9833726.268	41353906.84
			100%	19794135	21377665.8	41171800.8	82525707.64