DATABASE & SPREADSHEETS
This report aims to provide a detailed instructions which would hence support the database prepared for the departments that outsource lectures for consultancy for the ‘USA Brains' Company.
A Spreadsheet is an electronic interactive program that includes a set of columns and rows used to store and organise data. A cell is called the intersection point of a row and column. The rows are labelled by numbers 1,2,3,4 etc whereas columns are highlighted by alphabets like a, b, c, and d etc. representation of cell can be done by A1, B2 etc. The spreadsheet is mainly used as it makes the entire work easy and user friendly by having features like formulas, that can perform many calculations for one set of problems, and by dragging or copying that cell, we can have same formula being used all others problems too.
FORMULAS AND FUNCTION
Get your grade
or your money back
using our Essay Writing Service!
Each formula in the excel spreadsheet has to begin with an ‘=' sign. The user can enter al kinds of different functions including the basic arithmetic operations like multiplication, subtraction, addition and division to comparative functions like greater than and less than.
In order to have something specific, the user is supposed to enclose the function in brackets two ( ).For instance =PRODUCT(D14,C15)
IF functions provides the excel spreadsheet the power of decision making which makes it very helpful for the user. It can provide the user with options which can fit best into the condition requirement. Companies use IF function in order to decide pay rise or even in terms of promotion for their employees who have their pay on the performance and monthly targets. For instance they can use a formula like if the workers have reached the target sale of 20% compared to last year, they will get 5% bonus on their salaries. The IF function used for our clients, USA brains given in the question is:
Three possible syntax for IF function could be-
=IF(A1>0,”POSITIVE”,”NEGATIVE”) [e.g. for the first argument]
=IF(A1>0,”POSITIVE”,IF(A1<0,”NEGATIVE”,”ZERO”)) [e.g. for the second argument ]
=IF(A1<0,”NEGATIVE”,”ZERO”) [e.g. for the third argument]
Lookup function provides easy asses to the users into the big excel database of the companies. It provides ease of finding any kind of data from any sheet of the database as it allows the user to enter value entered in one cell and can be returned from a similar table in anther sheet.
For instance, task 3 required us to collect the hourly rate for the consultants from the task 1, hence I used lookup to find those values by using this lookup function-
In this function, the lookup value is A17, lookup vector is stf, name given to the entire data range in task 1 and HR is the name given to the range of cells from where the value has to be returned.
The two types of lookup available for users in the excel database are: VLOOKUP & HLOOKUP. VLOOKUP- It can be quite useful when we need to retrieve text or value from different table, based on specific key in the first column of the table; the retrieved result is at a specified horizontal offset from the first row of the table. HLOOKUP FUNCTION- It works exactly like the VLOOKUP except that it looks up the value horizontally in the table's first row.
NAMING CELLS AND RANGES
In to have easy asses to large amount of data and figures, excel enables the user to provide easy to remember names. By naming a cell or a range of cell, it becomes extremely easier to perform all other excel function at the same time as well. In multinational companies, when they decide to find out who has should be given the bonus this year, inspite of going through all salaries separately of selecting the entire sheet, they can simply, the entire range of cells which include employee performance.
Always on Time
Marked to Standard
For instance, a few cell range names given in the excel sheet are:
Fxc for fixed cost, stf and HR which was very helpful while using lookup function.
The various advantages and limitations of cell names and referencing are:
* Cell name makes our formula easier to use.
* Macros are easier to create and maintain.
* A meaningful cell name is much easier to remember then cell range.
* when we name a cell or range it appears on the name box
The limitation / rules are:
* No spaces are allowed.
* Cell name should be different from the cell reference
* It should not being with a number
Macro is very unique feature of MS excel which provides its users with a solution to link two or more pages in a workbook. The way in Macros can be set is similar to watching a recorded movie. Step including set up of a button and then clicking on assign Macros. The macros then record every single step till the user click on the stop button. In the given scenario, task 6 required use of Macros. A list on how to use of run Macros is also attached at the end of the file 3, task 6.
ADVANTAGES OF USING SPREADSHEETS
This workbook offers following advantages:
* Interactive help
* Graphs and charts data can also be developed which assist users to recognize data movement.
* Easy to use features: by categorizing and filtering data, we can easily find specific information
* Simple charts and tables
* Multi-sheet files
* Multiple Document Interface
* Calculating payments plan such as salary increments
* Performing arithmetic and comparative calculations.
An excel file has a variety of functions and features which have not been used in this task. In order to improve the worksheet, we can introduce the use of Pivot tables, which can quickly summarise a list or a database. The other things which could be used for improvement can be VLOOKUP and HLOOKUP, in order to provide an accurate result. Next, we can use the drag and drop techniques in order to change the layout of tables. It is believed to be the most powerful tool of the MS Excel.
BENEFIT OF ADDITIONAL FEATURES
The additional features used in the workbook are: use of buttons and development of an interactive page in task 6. The additional feature provides easy navigation between work sheets and hence making it more flexible, attractive and well presentable in-return providing a professional look to the entire workbook.
Thus, while concluding I would say that, MS Excel is widely used by companies all over the world to create electronic spreadsheet which stores, calculates and also helps in decision making for the companies.
In terms of out scenario- the USA Brain, the spreadsheet task was very interesting. I did had many problems in the initial stage but the more deep I went into, the more interesting it became for me. I have presented the data in as simple yet professional manner as possible. Use of light background has been done to provide that extra edge to the file.
The various features used namely are: if statements, Lookups, count, cell referencing, sum, product, macros, buttons etc. In order to count the total number of consultant out of the lecturers, which was asked in the question, I used =COUNTA(C12:C34) function. It takes some time to formulate the decision making IF function but when it is formed, it can be applied to one cell and get the result for all the cells, simply by dragging the result cell. Cell referencing and naming also saved a lot of time and calculation errors.