SPREADSHEET DESIGN/DEVELOPMENT (EXCEL)

1. Hoonuit MS Excel Tutorials (Formulas).

1. Formulas:

 

This section has eighteen videos which teach us to use eighteen formulas for excel. The first one is sum function that we use an equals to sign with the number or the cells that we want to add and the add sign. The second formula is average function which calculates the average of the data set that we pick. We can also use formula called “count” which helps us count a huge number of data set in a matter of few seconds. There is a date function which enters the data quickly and a ‘days’ formula which automatically generates the days in an order. The section also teaches match formula and transpose formula. There is a function called round which helps us to round the numbers to the nearest number. The most used formulas were also introduced in this section- ‘and’ which would help us combine two words, numbers, cells and display it in a combined form in a new cell. There is also an o function which is just the opposite of and function. The ‘if’ formula is very helpful and used a lot, it allows use to create a condition for the results to appear and keep a condition that if out of the two results that could occur, one or the other occurred then the cell would display a certain result. COUNTIF and SUMIF are two subtype of it which gives a condition to excel to count if – certain conditions existed or sum the data if- certain conditions existed. VLOOKUP is another important formula that allows a user to look up a particular value or data cell. We also learn the CONCAT function and to turn text into columns.

 

1. Linking and Consolidating Worksheets:

 

This section has nine videos and they teach to create a link and consolidate the workbooks. The first video talks about creating a link formula within the same workbook which can be done by going to the data tab of the ribbon in the connections group and click on the edit link button. The next thing we learn is to create a link formula to another workbook and to consolidate values from multiple workbooks. We learn to consolidate worksheet values within a function. We learn about absolute links and relative links and consolidate command with multiple sheets or multiple workbooks. We learn in this section to open a workbook with external reference and break links to external references.

 

 

Hoonuit MS Excel Tutorials (Pivot Tables). Complete Excel 2016 – Pivot Tables tutorials:

 

1. Getting Started (required): This is the first video tutorial and it gives an introduction to the Pivot table and selecting appropriate data for pivot table.

 

1. Creating and Deleting (required): This section has eight videos and they teach about creating the pivot table. The tutorial teaches to select various data sources, use the quick analysis tool and the recommended pivot table feature. This section also teaches to make the classic pivot table layout in the grid. We learn to select different data source and to delete the pivot table.

 

1. Custom Appearance and Layout (required): This section had ten videos and taught how to customize the appearance and layout of the pivot table. It taught us to add and remove field, add and use the page field and change the data area number format. The videos taught how the changed empty cells are displayed. We also learn to formart, sort, viewdata detail for a data cell in the pivot table. We learn to exclude row or column iterms from the data summary and group pivot table by dates.

 

1. Printing Options and Copying a Pivot Table (required): This section has three videos and they simply teach to print a pivot table with column and row field, insert page breaks after each row item and to copy a pivot table.

 

1. Summarizing with Totals and Formulas (required):This section has five videos and they teach about summarizing the totals and formulas in a pivot. We learn to display grand totals, change the data summary function, create a calculated field, insert a calculated item and list the formulas used in a pivot table.

 

1. Refreshing Data (required): Refreshing data section teaches to hit on the refresh option to refresh the data and the formulas. We also learn to do this at specific time and intervals.

 

Poor Spreadsheet Design/Development Practices.

1. Identify two poor spreadsheet design/development practices. Explain the implications of each poor practice you identified.

 

One of the poor spreadsheet design practices is inconsistency and incompleteness in data in the spreadsheet. Most of the times people make the spreadsheet without much attention to details causing this to happen.

Second thing can be mixing data, calculation and reporting. Computers process data entered in them in a different way then how people process it, so most of the time miscalculations are happening.

 

1. Explain how poor spreadsheet design/development practices contribute to spreadsheet integrity/reliability problems. Explain the potential accounting/business implications of poor spreadsheet design/development practices.

 

Users make multiple data tables in one spreadsheet which confuses the computer software and when certain formulas or commands are used, the software most of the time fails to understand what to apply. Another example can be use of null values where most statistical software does not recognize this. Such use of spreadsheet creates inconsistency and errors.

 

1. Explain why the majority of spreadsheets typically contain serious errors.

 

There has been many studies and researches on errors in spreadsheets. Based on practical experiences, a number of consultants have stated that 20% to 40% of the spreadsheets contain errors. A PWC consultant audited four spreadsheets and found 128 errors. In this way most of the spreadsheets contain serious errors. Audits in mining industry were proved to have errors in 30% of their spreadsheets. The accounting and business organizations fail to plan, analyze, design, develop, integrate, implement and maintain the spreadsheets.

 

1. Good Spreadsheet Design/Development Practices.

1. Identify two good spreadsheet design/development practices and list them in order of priority. Explain the implications of each good practice you identified.

 

1. It is important to identify what kind of spreadsheet is required for the business organization and the users need to stick to it.
2. There has to be extensive training on the use of the spreadsheet that has been decided to be used so that errors can be minimized.

 

1. Explain how good spreadsheet design/development practices contribute to spreadsheet integrity/reliability. Explain the accounting/business implications of good spreadsheet design/development practices.

 

A good spreadsheet design/development helps a business to perform operations effectively. It will allow the business to maintain integrity and security. There has to be good communication to ensure the change or editing of the spreadsheet is planned.

 

1. Review the systems development life cycle (SDLC) in the course notes/text. Explain how the principles of the SDLC may facilitate sound spreadsheet design/development.

 

The first phase is to plan what data to enter and how to enter it in the worksheet, what kind of tests and formulas are to be used. Second stem of SDLC that we can apply is to analyze the spreadsheet and the requirements of the spreadsheet project that we have, we then design or fill in the spreadsheet accordingly. The fourth step is the development of spreadsheet by using different functions and formulas. We integrate the data and test the formulas and implement them by applying them. Finally we refresh, update and maintain the data in the spreadsheet.

 

2. Improving Spreadsheet Design/Development.

 

1. One way to improve spreadsheet development is an add on that allows users to sign in which would show a log of people who signed in and made changes to the worksheet so that errors and frauds can be tracked.

 

2. There should be a scanning system that would constantly scan the formulas and data used in the spreadsheet that would catch errors made by users just like the spelling and grammar check for Word documents.