Develop and use complex spreadsheets

Assessment 1: Prepare an explanatory memorandum regarding ergonomics and energy
conservation. (Maximum of 250 words)
Metharom has been experiencing increased overhead costs over the past quarter. The administration
manager has investigated, and found that the increased costs are due to two factors: increased
energy usage, and an increased amount of sick days for the clerical staff.
You are required to create an explanatory memorandum for staff:
a) explaining a minimum of 5 ergonomic practices and
b) a minimum of 5 energy conservation techniques.
You will need to ensure that:
• you refer to Metharom’s Document style guide
• Your document can be easily read and understood by all employees of Metharom Construction
• The document that you create is visually appealing
• The content has been proofread and edited to suit the audience
This assignment should be no more than 250 words.

Assessment 2: Create a template for the sales team to record their quarterly expenses, and make a
recommendation to improve efficiency and accuracy of documents submitted to accounts.
(Maximum of 250 words)
Metharom’s sales team is required to submit an expense report on a quarterly basis. Recently, as the
number of sales has increased, the reports have had a higher rate of errors. In order to prevent this
from happening again, you as an administration officer will need to create an automated sheet to
assist the sales staff.

Your expense report template must include:
A) Name this sheet as ‘Expenses’ and save it in the required format. Calculate total for each
expense using formula
B) Apply conditional formatting to highlight all expense higher than 100 in red colour
C) Link the above expense sheet with a new sheet in the same workbook and name it as “new
expense” so that any changes made on expense will also reflect in new expense sheet.
D) Write a business letter to the sales manager that recommends a minimum of two other
documents that could be automated, and explains the benefits of automation.
You will need to submit both a print copy of the sheet and soft copy of the template as well as the test
document to your assessor.
Maximum of 250 words.

Assessment 3: Using macros and absolute reference

The data reflects payments to the subcontractors for last three quarters which were pending for
A) Write the Formula to calculate tax for quarter 1 using an Absolute Reference and then
copy the formula to Quarter 2 and quarter 3 to get amount.
B) Create and record a macro and name the macro as tax
C) Create a test plan to ensure the calculations are correct.
Appendix B
This assessment must be submitted in both hard and soft copy format.

Assessment 4: Import and export data on spreadsheet (Maximum of 150 words)
Data has been provided in Appendices in text format.
You are required to refer to help option on excel and do the following tasks
A) Import the data to an excel file and format. Name and store spreadsheet as “customer” in
accordance with organisational requirements and exit application without data loss or damage,
sort the data in ascending order of names
Headings: bold
font: Arial
font size: 12
colour : black
B) Once the formatting has been completed export the data to text format. (Word document).
Ensure the spreadsheet and the word document is linked. Provide a detailed explanation step wise of
how you have used the online help option. Submit the print copy to your assessor.
Appendix C: Data

Assessment 5: Create a pie chart to represent costs of subcontractors.
The operations manager analysing subcontracting expenses to determine if hiring additional staff for
the work team, would be more cost effective than hiring subcontractors. In order to do this, she has
requested that you
A) Create a chart/graph with labels that represents the percentage of funds spent on subcontractors
for each trade.
B) Explain the use of pie charts
C) Save, view, adjust and print chart and submit to assessor
Refer to the appendices for additional data to assist you. As this is an official Metharom document,
ensure that you refer to and abide by the style guide in the company folder.