FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
Activity description
This unit describes the performance outcomes, skills and knowledge required to:
a) comply with organisational inventory procedures, reconcile inventory records to
general ledgers, record inventory flows, prepare schedules and produce ad hoc
reports.
b) create complex spreadsheets to store and retrieve data by providing administrative
support within an enterprise, or independently responsible for designing and working
with spreadsheets relevant to their own work roles.
To learn the scope of this unit go to the Resource Library and study the unit outlines:
“FNSACC405A Unit Outline”
“BSBITU402A Unit Outline”
Explanation of Symbols
Study Material in the Resources Library
Virtual Business – ABC Financial Services
Assessment Task
Information
© 2011 AIFA
Page 1 of 7
FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
1.0 Complex Spreadsheets – Introduction
People working in a variety of fields often need to create complex spreadsheets to
illustrate budgets, business proposals, or manage data and provide analysis. Developing
complicated worksheets requires specific knowledge and skills for manipulating large
quantities of data.
Spreadsheet software, such as Microsoft Excel, can assist to complete business tasks and
to produce complex documents. For example, once set up, a spreadsheet can show the
flow-on effect of just changing one week’s sales figures! Adding flow charts, querying data
from external sources or creating PivotTables using Excel not only makes creating and using
complex spreadsheets easier, it also can make the information clear to others.
In this activity we are going to use a complex spreadsheet to manage a series of inventory
transactions and then combine this with some advanced spreadsheet techniques for
management reporting.
Among the advanced Excel techniques which will be required in the summative assessment
that follows are:
a) Excel Charts
b) Conditional formatting
c) VLOOKUPS
d) Pivot tables
© 2011 AIFA
Page 2 of 7
FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
e) Simple Macros
If you are unfamiliar with these techniques, or need a refresher:
Go to the Resource Library and study the short films:
“Excel Charts”
“Conditional Formatting”
“VLOOKUPs in Excel”
“Pivot Tables in Excel”
“Macros in Excel”
Additionally the Resource Library contains an advanced Excel training manual:
“Excel 2007 Advanced”
These skills will be required in the summative assessment that follows.
2.0 Ergonomics
The first element of BSBITU402A Develop and use complex spreadsheets is:
“1.1 Organise personal work environment in accordance with ergonomic
requirements”
Ergonomics is the study of designing equipment and devices that fit the human body, its
movements, and its cognitive abilities.
The International Ergonomics Association defines ergonomics as follows:
Ergonomics (or human factors) is the scientific discipline concerned with the
understanding of interactions among humans and other elements of a system, and
the profession that applies theory, principles, data and methods to design in order to
optimize human well-being and overall system performance.
Ergonomics is employed to fulfill the two goals of health and productivity. It is relevant in the
design of such things as safe furniture and easy-to-use interfaces to machines. Proper
© 2011 AIFA
Page 3 of 7
FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
ergonomic design is necessary to prevent repetitive strain injuries, which can develop over
time and can lead to long-term disability.
As we use computers for longer hours every day, we may notice increasing aches and pains
in some parts of our bodies. These musculoskeletal problems can happen in anyone who
uses a computer for long hours: computer programmers, engineers, data entry workers,
telephone operators, customer service workers, and even graduate students. The problems
can range from minor muscle aches that last less than a few hours to persistent tendon
problems that can last for years. The more severe problems can lead people to leave a job
they like or stop doing sports activities they enjoy, like tennis or bicycle riding. So we should
do what we can to prevent the minor aches of work from progressing to disabling conditions.
The most common body areas to watch for are the hands, wrists, elbows, shoulders and
neck. The problems may vary from aches to pain, burning, numbness or tingling. These
symptoms may be felt during typing or mouse use or at other times when no work is being
done, including during the night when the symptoms might wake you up. If you experience
these symptoms, or other persistent or recurring pain that you think may be related to using
a computer, you should see a qualified physician or talk to your company’s health and safety
staff. The earlier a problem is properly diagnosed and treated, the less chance there is that it
will progress to a disabling condition.
Go to the Resource Library and study the PowerPoint Presentation:
“What is Ergonomics”
3.0 Resource Conservation in the Office
Element 1.4 of BSBITU402A Develop and use complex spreadsheets is:
“Apply work organisation strategies and energy and resource conservation
techniques to plan work activities”
In most contemporary business environments we are expected to take our share of the
responsibility for implementing energy and resource conservation.
Go to the Resource Library and study the guide:
“Green Office Resource Guide”
© 2011 AIFA
Page 4 of 7
FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
Summative Assessment
Inventory Records – Spreadsheet Analysis
Assessment Instructions
1. This unit is being undertaken as a component of FNS40601
2. You must undertake the role of the Contract Accountant and whenever directed you
must communicate (email, phone, fax or personally) with the Manager, Client, and all other
designated personnel
3. Your trainer will undertake the role of client, employer, and all other designated
personnel that you must obtain authorisations from or report to
4. The other students are your colleagues and you must communicate with them via the
forum in business meetings as scheduled by the trainer (as the designated person)
5. In all responses you must use industry standard terminology and follow correct
calculations – using current calculation requirements
6. Read the case study material first and the assessment support material and links before
attempting this assessment
Scenario
ABC Virtual Financial Services has been retained by City Health Foods Pty Ltd to assist in:
a) Verifying the accuracy of an Inventory Spreadsheet purchased via the internet, and
b) The development of Management Reports based on purchases and sales of
inventory
You have been given this assignment by your ABC Manager. As part of this assignment you
have been provided with:
Go to the Resource Library and locate:
“City Health Foods Inventory 2011” (Excel Spreadsheet)
“City Health Foods Client Work Form”
© 2011 AIFA
Page 5 of 7
FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
Recommendation: make a practice backup copy of the spreadsheet before you
start to develop management reports
Required
1.1 First, review the scenario, the spreadsheet data supplied and read these instructions
carefully.
1.2 Verification of Inventory Spreadsheet Calculations
a) Your client has purchased the inventory spreadsheet City Health Foods Inventory
2011 from a reputable company via the internet and populated the spreadsheet with
City Health Food data for the period January 2011 to August 2011
b) Your client is happy that the correct data has been transferred to the spreadsheet but
is unsure about how weighted average inventory costs are calculated. As this
calculation directly affects product gross margin, your client needs reassurance that
gross margins are being correctly calculated in the spreadsheet
c) You are required to consider how you can prove to your client that the following
values are being correctly calculated in the spreadsheet
• Average Cost Previous and New in Columns K and L of the Purchases
Worksheet
• Gross Margin % in column S of the StockCodes worksheet
d) In section 1.3 you will need to show to your Manager how you can prove to the client
that these calculations are correct
1.3 Verify Client Requirements and Obtain Authorisation
a) Complete the City Health Foods Client Work Form for your Manager as follows:
• In this form you must demonstrate that you have a clear understanding of the
client’s objectives as to inventory analysis and reporting
• Complete the section of the form “How will you demonstrate to the client that
the Average Cost and Gross Margin % values are correctly calculated?” so
your Manager can assess your methodology
• Complete the section of the form: “How could you reconcile the year to date
Sales and Gross Margins in the Worksheet – Profitability with the client’s
accounting system General Ledger”? so your Manager can assess your
methodology
• Complete the section of the form “How have you organised your personal
work environment in accordance with ergonomic requirements?”
• Complete the section of the form: “What resource conservation techniques
will you adopt as you plan your work activities?”
b) Before beginning the client work, obtain signed authorisation from your Manager
to proceed by submitting the Client Work Form to your Manager for approval.
c) Follow-up by implementing any comments made by your Manager
© 2011 AIFA
Page 6 of 7
FNSACC405A Maintain Inventory Records
BSBITU402A Develop and Use Complex Spreadsheets
Following your receipt of Authorisation
1.4 Develop Management Reports
a) In the Excel template “City Health Foods Inventory 2011.xls” create new
worksheets containing the following reports for the period Jan – August 2011. Note:
you must use an example of Pivot tables, Data Sorting and Conditional Formatting
in your worksheets.
i.
Top 10 Profitable Products (based on Gross Margin %)
ii.
Top 10 Customers by $ Sales
iii.
Top 10 Customers by Profitability (Gross Margin $)
iv.
Top 10 Stock Adjustments by $
b) For each Report create a chart that would best help the client understand the
information being presented. Underneath each report explain for the client why you
selected the particular report style
c) Ensure the PRINT settings are in place to print the reports and charts professionally
d) Save the Inventory template in the format “Your Name_ City Health Foods
Inventory 2011.xls
1.5 In a separate worksheet develop an excel message box macro with the following
words:
“Read the instructions carefully before you start using this template. Do not
delete all the formulas in the columns with light blue column headings,
otherwise you will have to download a new version of the template and start
over”.
