Benefit of Additional Features Report 2022

Table of Contents
Page No
Introduction
Task One
Task Two
Task Three
Task Five
Task Six
a) If Statements
b) Naming Cells and Ranges
c) Absolute Referencing
d) Lookup Tables
e) Pivot Tables
f) Macros
g) VBA Language
h) The Advantages of Automated Workbook
i) The Improvement of the Workbook
j) Benefit of Additional Features
List of Tables
References

Introduction
This report is consisting of six tasks and is aim at to help a colleague in an affiliated University
that outsources lecturers as consultants. This report in task one will create a model that calculates
the number of staff, the total hourly rate, also by using a Lookup statement automatically return
the position of each staff, and will create a pivot table to extract relevant and meaningful
information of choosing; in the task two it will calculate the departmental fixed costs on an
hourly basis; in the task three will use a lookup function to determine the hourly rate for each
staff member on the estimate; in the task four it will modify the formulae & functions of the
original model; task five will be about the embedded VBA editor, macros; and at the last the task
six will be about the report related to the important features of excel.
Task One
Task Two
Task Three
Task Five
Task Six
Microsoft excel posses the many feature and option that help the user in decision making, data
management with its many data and management features. Its data organization features helps to
summarize, sort, outline, and filter the data that could help to focus on the specific data, the
validation features make the data reliable by correcting in useful format. Different build in
functions and formulas of excel make the calculations process easy. A spreadsheet is consisting
of columns (vertical) and rows (horizontal) that make the matrix and columns. Basically, the
excel work sheet were designed to facilitate the accountants, sale executives, bankers, students,
etc to work with huge data, and figures. Usually, in excel has 11 categories of 365 functions
along with addition more addins that also empower the spreadsheet, the report will discuss the
few of the facilities and functions of the Excel:
k) If Statements:
Like other many useful functions in the Excel If statement has crucial importance as this function
is important in the decision making context. This function is a logical function that check the
required conditions, criteria, or used when the user has to test the something or any criteria,
show one result when the test result is True and otherwise different result when the test result is
False. Normally, in the If stamens there are three arguments, which could be entered simply as:

IF (logical-test, {value-if-true}, {value-if-false})
The logical-test is required to represent the value or any other expression that has to verify for
True or false, “value-if-true” is the value that the user want to be devolved if the logical-test
expression tested to True, “value-if-false” is the value that the user want to be devolved if the
logical-test expression tested to False. This function could be used to test the more than one
argument in nested order. The “If functions” could be used with logical operators like “AND”,
OR and also with the math operators to evaluate the more complex logic in the expression
(Cheusheva, 2014).
l) Naming Cells and Ranges:
In the Excel, cell name or the name of rang, mention the concern cell or rang, that could
substitute the cell references or values, by assigning the descriptive name to cells or ranges, the
user could easily handled the cells or ranges in the formulation of formulas. By assigning the
name to any range make it easy to remember the purpose of formula. For example, as in the
project spreadsheet, cells that contains the hourly rate of the staff could be named as the “hourly-
rate” in this way, in the formula =SUM (hourly-rate) is much easy to understand than =SUM
(C5:C30), the first character of the name should be a letter, should not be same as the cell
address. By using the cell rang, the user could perform the functions like sum or average etc.
m) Absolute Referencing:
Normally, while working in spreadsheet, when user copy the formula that contains the cell
references, the cell references changes known as relative cell references. Absolute referencing
unlike the relative cell reference is the reference of specific cell or rang of cells that could not be
changed even with the change of the size; shape of the spreadsheet, or by coping to other cell. It
distinguish the location of cell or rang of cells used in any formulas, comprise of column letter
and row number as in normal cell reference but each number and letter premised by ($) sing. The
major use of the absolute reference is in the formulas when the user wishes to fix the relative cell
reference. By using the absolute reference in the formula, the original formula never affected. As
in the project spreadsheet for example, in the staff hourly rate when we copy across the cell
=sum (C5:C15) it will change to =sum (D5:D15) in relative reference but when we put the $ sing
as =Sum ($C$5:$C$15) it will not change when we copy across the cell.
n) Lookup Tables:
By using the lookup function the user could easily lookup the specific value within the given
data table or data set. This task could be performed by using the query or formula by using the
syntax and by specific the data range. Excel contains the VLookup (vertical lookup function),
HLookup (horizontal lookup function), Match, Index, and Choose built in functions to make a
lookup table. A lookup table includes the values, the user want to look up and its translation; it
may be on the same worksheet. During entering the data at least one column should have unique
value that could distinguish the that row or items from other rows or items For example in the
project there are information about the staff in different columns and on more than one sheets,
lookup function in this case help to find the required information from any sheet about the any
staff member.