BSBITU402: Analysis of Spreadsheet Development and Usage Skills

This document presents a solution to a BSBITU402 assignment focusing on the development and usage of complex spreadsheets. Part B delves into the layout and appearance of spreadsheets, specifically Corey's spreadsheets, analyzing data testing, formula implementation, and documentation practices. It also advises Corey on improving his spreadsheet techniques. Part C provides two real-world examples of macro usage in companies, including creating digital letterheads and inserting predesigned tables, detailing the steps involved and the benefits of automation. The document concludes with instructions on saving macro files and a list of references. Desklib offers a platform to explore more solved assignments and past papers.

Running head: DIPLOMA OF ACCOUNTING
Diploma of Accounting
Name of the Student:
Name of the University:
Author’s Note:

1
DIPLOMA OF ACCOUNTING
Table of Contents
Part B...............................................................................................................................................2
Layout and Appearance of Corey’s Spreadsheets...........................................................................2
Testing of Data and Implementation of Formulas...........................................................................2
Documenting....................................................................................................................................2
Advise to Corey...............................................................................................................................3
Part C...............................................................................................................................................3
Two Examples of Macro in Companies..........................................................................................3
Saving of Macro Files......................................................................................................................4
Reference.........................................................................................................................................5

2
DIPLOMA OF ACCOUNTING
Part B
Layout and Appearance of Corey’s Spreadsheets
The layout of the spreadsheets which are prepared by the company are appropriately
shown region wise as well as store wise. The sales of different regions are shown effectively
store wise so that the management cam effectively recognize sales from different areas and
different stores as well1. The spreadsheet also shows sales product wise and also shows the major
products of the business.
Testing of Data and Implementation of Formulas
The financial data which relates to sales which is achieved by the business are shown in
spreadsheets considering the region and store as well2. The formulas which are used by Corey is
not correct as he has just considered the store wise sales and not regional sales in summation of
the data and thus this produces in correct results which can affect the decision-making process of
the business.
Documenting
The financial data which is shown in the assessment shows the sales which the business
has achieved in different regions and also shows store wise and product wise sales records. The
data has been properly recorded and changes can be made to the sales records of the business as
1 Goel, Naveen, Mayur Hemani, and Himanshu Jindal. "Method and system for repurposing a spreadsheet to save
paper and ink." U.S. Patent 8,799,761, issued August 5, 2014.
2 Hofer, Birgit, André Riboira, Franz Wotawa, Rui Abreu, and Elisabeth Getzner. "On the empirical evaluation of
fault localization techniques for spreadsheets." In International Conference on Fundamental Approaches to
Software Engineering, pp. 68-82. Springer, Berlin, Heidelberg, 2013.

3
DIPLOMA OF ACCOUNTING
per the requirements of the management3. Spreadsheets provides advantages to revise the sales
data as well as the formulas used in case of any mistakes.
Advise to Corey
As per the analysis of the financial data which is recorded by Corey and also
computations of summations, the formulas are incorrectly used and Corey can be advised to
revise the same and apply appropriate formulas for the same. In addition to this, linking of the
cells can also improve the presentation and effectiveness of formulas in spreadsheets.
Part C
Two Examples of Macro in Companies
Some of the companies uses Macro for recording company letterheads for digital. This
ensures that the company can drop its letterhead on any documents easily4. The steps which is
needed to be used are given below:
 First step is to go to view tab and click on Macro and record Macro.
 Record the name of company and its relevant description
 From the insert tab, pictures need to be selected on the basis of company’s Logo
 Enter the company name and highlight the same and select the typeface and addresses of
the business
 Makes changes in date and time format of the company.
3 Abreu, Rui, Jácome Cunha, João Paulo Fernandes, Pedro Martins, Alexandre Perez, and João Saraiva. "Smelling
faults in spreadsheets." In Software Maintenance and Evolution (ICSME), 2014 IEEE International Conference on,
pp. 111-120. IEEE, 2014.
4 Brown, Stephen, Brian Hutton, Tammy Clifford, Doug Coyle, Daniel Grima, George Wells, and Chris Cameron.
"A Microsoft-Excel-based tool for running and critically appraising network meta-analyses—an overview and
application of NetMetaXL." Systematic reviews 3, no. 1 (2014): 110.

4
DIPLOMA OF ACCOUNTING
The steps which are given above are to be followed for establishing a company’s letterhead
using macro. The reason due to which automation of Macro sequence is to ensure that the
process is easily and the user does not need to update the same on a regular basis.
Another example which can be given for the application of Macro is in Inserting pre-
designed tables where templates are easily available. From the insert table, with the help of
cursor horizontally number of rows and columns can easily be selected5. Then the user can enter
the headings which can be occupations, locations any information which the user wants to show
in tables. The tables then can be adjusted in size and width as per the requirements of the users6.
This saves a lot of time for the user to manually prepares charts and templates and the same can
be adjusted as per the requirements of the user.
Saving of Macro Files
Macro files can be saved in as workbook documents in any place as per the preference of
the user. The same can be saved as excel enabled workbook file in the place of preference of the
user.
5 Konstantinidis, Andreas, and Cat Grafton. "Using Excel macros to analyse Moodle logs." (2013): 33-39.
6 Giménez‐Forcada, Elena, and F. Javier Sánchez San Román. "An Excel Macro to Plot the HFE‐Diagram to
Identify Sea Water Intrusion Phases." Groundwater 53, no. 5 (2015): 819-824.

5
DIPLOMA OF ACCOUNTING
Reference
Abreu, Rui, Jácome Cunha, João Paulo Fernandes, Pedro Martins, Alexandre Perez, and João
Saraiva. "Smelling faults in spreadsheets." In Software Maintenance and Evolution (ICSME),
2014 IEEE International Conference on, pp. 111-120. IEEE, 2014.
Brown, Stephen, Brian Hutton, Tammy Clifford, Doug Coyle, Daniel Grima, George Wells, and
Chris Cameron. "A Microsoft-Excel-based tool for running and critically appraising network
meta-analyses—an overview and application of NetMetaXL." Systematic reviews 3, no. 1
(2014): 110.
Giménez‐Forcada, Elena, and F. Javier Sánchez San Román. "An Excel Macro to Plot the HFE‐
Diagram to Identify Sea Water Intrusion Phases." Groundwater 53, no. 5 (2015): 819-824.
Goel, Naveen, Mayur Hemani, and Himanshu Jindal. "Method and system for repurposing a
spreadsheet to save paper and ink." U.S. Patent 8,799,761, issued August 5, 2014.
Hofer, Birgit, André Riboira, Franz Wotawa, Rui Abreu, and Elisabeth Getzner. "On the
empirical evaluation of fault localization techniques for spreadsheets." In International
Conference on Fundamental Approaches to Software Engineering, pp. 68-82. Springer, Berlin,
Heidelberg, 2013.
Konstantinidis, Andreas, and Cat Grafton. "Using Excel macros to analyse Moodle logs." (2013):
33-39.