BSBITU402-Develop and Use Complex Spreadsheets – Accounting & Finance Assignment Help

Assignment Task

Task 

Case Study Overview
Awesome Landscapes Pty Ltd provides landscaping solutions to large-scale commercial and residential developments. You can find out more about Awesome Landscapes by accessing their website:

Throughout this workbook you will need to log into Awesome Landscapes site. Each time you need to use the following information:

You are the bookkeeper for Awesome Landscapes, and you directly report to the business manager, Shaar Azad.  You have been tasked to follow safe work practices and create spreadsheets that will be used by the business managers of the organisation. In creating these spreadsheets, you will need to follow Awesome Landscapes’ policies and procedures, and guidelines for developing documents and use Microsoft Excel. You will be provided with templates and supplementary guide questions throughout this assessment to document evidence of your completion of the relevant unit requirements addressed in this task. Use the following naming convention to save your documents for submission

Note: Within this part of the Case Study, you will be required to complete tasks within designated timelines. Ensure that you go over each task instruction before you proceed.

Part 1 – Follow safe work practices
Before commencing any work, ensure that you observe Awesome Landscapes’ safe work practices for ergonomics and energy and resource conservation. 
Refer to the Awesome Landscapes Staff Handbook.

Part 2 – Develop and use complex spreadsheets
For this part of the Case Study, you are required to develop and use complex spreadsheets within the Awesome Landscapes simulation. You will do this by using Microsoft Excel.  To proceed with the tasks, you need to access the installed Microsoft Excel software on your desktop or laptop computer. An Excel Tutorial is available here.  Refer to the following guidelines when completing tasks under this part.

Guidelines:
1.Use the Awesome Landscapes Style Guide (username: newlearner password: newpassword) and prescribed templates (e.g. Planning Document).
2.Unless specified otherwise, present important information in alphabetical or chronological order. 
3.In planning the files to be produced, the planning document will require you to specify your storage options for the outcomes. You must choose two (2) storage locations for all the outputs of this practical assessment. Your file location must include both:
a.A location on your computer for later submission along with this workbook.
b.An online storage system (such as Dropbox or Google Drive). You must only choose one (1) online storage system.
4.When filling out planning documents, the title of output field in each planning document must list all titles of the printouts for the particular task. For the number of pages to be printed, an approximate number is acceptable.
5.Print documents on white A4 size paper. You may use more than one (1) sheet of paper for one output so that all data in the document are printed.
6.You are required to source and use manuals, user documentation, and online help with spreadsheet design and production. 
7.You are required to apply work organisation strategies throughout the conduct of all the tasks. 
8.Submit documents along with this workbook using the prescribed file name for each task.
9.Ensure that you complete each document within the following designated timelines. Your assessor will take note of the date and time when each document was last modified. Take note of the following deadlines for each output provided below.

Task 1 – Roleplay Activity 
This part of the assessment is a Roleplay Activity.
This activity will require you to talk to the business manager, Shaar Azad to find out the specific spreadsheets that you need to create. 
To complete this part of the assessment, you will need access to:

• Your teacher who will play the role of business manager, Shaar Azad.
• You need to contact your teacher and request a zoom meting to conduct this role play

Read the instructions carefully before proceeding.

Discussion guide for the candidate: 
In the meeting: 

• You will ask about the specifics of the spreadsheets regarding type, the relevant sources of information, and the relevant style guides or format including templates, if any. You can use the following guide questions:
• What are the spreadsheets that are needed by the business managers? Are they macro-embedded or not?
• Where can I source the data to be used in the spreadsheets? 
• Is there a manual or a guide I can use to facilitate the preparation of the spreadsheets? 
• Are there any style guides or format, including templates that I should use to prepare the spreadsheets? 

Discussion guide for the volunteer: 
In the meeting: 

• When asked about the needed spreadsheets, you will say that the types of spreadsheets are the following: 

a.Sales Summary
b.Quality Audit report
c.A spreadsheet template
d.Financial statements (balance sheet, trading statement)
e.Financial ratios and graphs 

• When asked about the relevant sources of information, you will point out the following documents: 

a.BSBITU402 Task 2 Awesome Landscapes Sales Summary
b.BSBITU402 Task 2 Cascade Peak Walkway Audit Measurements 
c.BSBITU402 Task 2 Cascade Peak Walkway Meeting Minutes
d.BSBITU402 Task 3 Combined Balance Sheet and Profit and Loss Statement.csv

• When asked about manuals or guides that can be used to facilitate the preparation of the spreadsheets, you will refer the candidate to access user guides available on the Internet.  
• When asked about the relevant style guides or format including templates, you will point out the Awesome Landscapes Style Guide and the Planning Document template.

Task 2 – Sales Summary
Awesome Landscapes is reviewing their national sales data over a three-month period. They are using Microsoft Excel software to organise and summarise their data. You are required to complete the Sales Summary by creating a pivot table. You need to download the file by accessing it from the link below: 

Task 3 – Quality Audit Report
Awesome Landscapes is part way through their project to build the Cascade Peak Walkway, a 2330m boardwalk through the Cascade Peak National Park, for the Cascade Peak Shire Council. As part of their quality control obligations, they are required to conduct a quality audit of the first section of the boardwalk and prepare a Quality Audit Report for the council.

The business manager has received the quality audit data and needs answers to the questions below:
a)Average height for each handrail measuring over 1060mm*
b)Standard deviation for the height of each handrail over the 1100m section
c)Minimum heights for each handrail at and beyond the 1100m section
d)Maximum heights for each handrail at and beyond the 1100m section 
e)Number of instances the height of each handrail is outside of the height range specified in the minutes
f)Average gap between the slats at and over the 1100m section*
g)Standard deviation for the gap between the slats at and over the 1100m section*
h)Minimum slat gaps throughout the boardwalk
i)Maximum slat gaps throughout the boardwalk
j)Number of instances where the gap between the slats falls outside of the gap range specified in the minutes

Task 4 – Spreadsheet Template
Awesome Landscapes has provided you with their financial data in a .csv file and you need to download the file by accessing.
You are required to complete a series of tasks to produce a Financial Summary using this .csv file.

Task 5 – Balance Sheet
Using the same file you created in Task 4 (BSBITU402 Tasks 4-7 Financial Summary), create a second tab name it “Balance Sheet”. Using a macro insert the business name of “Awesome Landscapes” and underneath type in Balance Sheet  On this tab, using custom cell formats where necessary, produce a 4-Year Balance Sheet. (2013-2016)
Automate this sheet using appropriate formulae to automatically populate the cells from the corresponding raw data imported into Sheet 1 (Task 4) from the .csv file provided. You are to include all data from the rows of “Cash” and “Cash Equivalents” up to the “Retained Earnings/Accumulated Losses”. 

Task 6 – Trading Statement
On the same file used in Tasks 4 and 5, create a third tab and name it “Trading Statement”. On this tab, using custom cell formats where necessary, produce a 4-year Trading Statement. Using a macro insert the business name of “Awesome Landscapes” and underneath type in Trading Statement 
Automate this sheet using appropriate formulae to automatically populate the cells from the corresponding raw data imported into Sheet 1 (Task 4) from the .csv file provided. You are to include all data from the rows of “Cash Sales” up to “Expenses”. 

Task 7 – Financial Ratios and Graphs/Charts
1.On the same file from Tasks 4, 5, and 6, create a fourth tab named “Financial Ratios”. This tab will contain the financial ratios from the data you have produced in Task 5 Balance Sheet. Using a macro insert the business name of “Awesome Landscapes” and underneath type in Financial Ratios