UNIT-4

SPREADSHEET
APPLICATIONS FOR
BUSINESS
Spreadsheet Applications for Business – Conditional formatting –
Charts and diagrams – Logical Functions : AND, OR NOT, IF , IFNOT,
COUNT ,COUNTIF, TRUE- Text functions: UPPER, LOWER, LEFT,
RIGHT,TRIM ,TEXT,LEN DOLLAR,EXACT: Financial Functions :
Depreciation [ DB, DDB, VDB] , simple Interest [ PMT, NPER,
INTRATE] – Present Value, Net Present Value, Future Value [ PV, NPV,
FV] –Internal Rate of Return [ IRR, MIRR] – Statistical Functions :
Mean , Median, Mode, Standard Deviation, Correlation , Regression –
Date & Time Functions :DATE , DATEVALUE , DAY ,DAYS360, NOW,
TIME TIMEVALUE , WORKDAY, WEEKDAY , YEAR Lookup and
Reference Functions : Hlookup , Vlookup , Transpose Function
,GETPIVOTIDATA-PIVOT TABLE –HYPERLINK .

Spreadsheet application for business

Spreadsheets are commonly used to work with financial
data. When calculations are done on paper and data needs
to be modified, the entire job must be redone and
rewritten. If you use a spreadsheet program, the
recalculation is done automatically. A spreadsheet is a
computer application that allows us to enter and process
data (record, calculate, and compare). Spreadsheets were
created as computer models of accounting spreadsheets
printed on paper. On a modern computer, a spreadsheet is
very fast, so you can set up a sophisticated model with
thousands of computations and get an answer quickly.
The details of bank passbook, tax, preparation of bills,
Income statements, Budget analysis, Loan analysis,
Investment analysis, Inventory control, Production analysis
can also be maintained using a spreadsheet package.
Features of spreadsheet
The following are some of the qualities of a spreadsheet
that make it a suitable tool for modelling:
• Supports the idea of variables
This is required in order to modify input values and
pre-set numbers in the model such as VAT rate or
tax levels
 • Use of formulas
A model must be able to do math operations on the
input values, which is exactly what formulae can
accomplish.
• Use of functions
This is a huge time saver because spreadsheets
offer hundreds of functions that may be used to
develop the model. For example, there is a function
that can calculate compound interest. If it didn't
support functions, you'd have to do all the
complicated math yourself with formulas.
• Supports Goal Seek
The goal-seek feature will find the correct input
value given what you want the answer to be, which
is a highly powerful tool. It's as though the model is
being run backwards in time.
• Provides a wide range of Graphs
A model is frequently concerned with not only
obtaining a single response, but also with
demonstrating how that answer is arrived at over
time. Graphs are an excellent tool for this.
• Dynamic calculation
A spreadsheet runs quickly on a modern computer,
allowing you to create a sophisticated model with
thousands of calculations and obtain a rapid answer

Advantages of spreadsheet
Following are the important advantages of using spreadsheet
• Accurate findings are possible.
• Quick computation
• Data from the worksheet can be seen and printed.
• Data can be converted into graph .

There are many spread sheet packages like Gnumeric ,

GS-Calc, KSpread , Lotus 1-2-3, Microsoft Excel,
[Link] Calc, Resolver Oneetc,. Microsoft Excel is
the most popular one. We shall understand spreadsheet
with the help of MS-Excel.
Excel's strength comes in its ability to do computations,
summarization, graphic depiction of results, and data
formatting in a manner comparable to that of a word
processor. Excel's working area is divided into rows and
columns. The term "cell" refers to the intersection of rows
and columns. It is the basic unit through which data is
entered. Its potential to many of Excel's capabilities are
comparable to those of Word. Three worksheets are
included by default. Rows and columns can have different
heights and widths. Worksheets and workbooks are the two
elements of an Excel file.
Worksheets are the real spreadsheets that contain the data
that you can alter and organize. The complete Excel file is
referred to as
a workbook. Workbooks can have a large number of
worksheets or only one. Each worksheet in a workbook will
have its own name, which can be found on the bottom of
the screen in the tabs.
Parts of MS -Excel spreadsheet
Excel window appearance with its parts is given below

Title bar- Displays the application name, file name and

various window controls like minimize button, maximize
button and close button.
Formula Bar- Displays the cell content.
Workbook window- A workbook automatically shows in the
workspace when you open Microsoft Excel XP. Each
workbook contains three worksheets. A worksheet is a grid
of cells consisting of 65,536 rows by 256 columns.
Scroll Bars- Used to scroll through different parts of current sheet
Status Bar- is located at the down below the Sheet names
in an Excel worksheet where we can see the short
description of selected data and worksheet.
Quick Access toolbar- This is a customizable toolbar which
can be used to contain your most frequently used
commands.
Ribbon- is the row of tabs and icons at the top of the Excel
window that allows you to quickly find, understand and use
commands for completing a certain task.
Name box- The Name Box normally displays the address of
the "active cell" on the worksheet.
Active cell- It is used to identify the cell which is currently active.
Sheet tab- It is used to display the worksheet that a user is
currently editing.
Conditional Formatting
Conditional formatting in Excel enables you to highlight
cells with a certain color, depending on the cell's value.
Highlight Cells Rules
To highlight cells that are greater than a value, execute
the following steps.
1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click

Conditional formatting.

3. Click Highlight Cells Rules, Greater Than.

4. Enter the value 80 and select a formatting style.

 5. Click OK.
Result. Excel highlights the cells that are greater than 80.

6. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically.

Note: you can also use this category (see step 3) to highlight
cells that are less than a value, between two values, equal
to a value, cells that contain specific text, dates (today,
last week, next month, etc.), duplicates or unique values.
Clear Rules
To clear a conditional formatting rule, execute the following steps.
1. Select the range A1:A10.
 2. On the Home tab, in the Styles group, click
Conditional Formatting.

3. Click Clear Rules, Clear Rules from Selected Cells.

Top/Bottom
To highlight cells that are above average, execute the
following steps.
1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional

Formatting.
 3. Click Top/Bottom Rules, Above Average.

4. Select a formatting style.

5. Click OK.
Result. Excel calculates the average (42.5) and formats the
cells that are above this average.
Note: you can also use this category (see step 3) to
highlight the top n items, the top n percent, the bottom n
items, the bottom n percent or cells that are below
average.
Conditional Formatting with Formulas
Take your Excel skills to the next level and use a formula to
determine which cells to format. Formulas that apply
conditional formatting must evaluate to TRUE or FALSE.
1. Select the range A1:E5.

2. On the Home tab, in the Styles group, click

Conditional Formatting.

3. Click New Rule.

4. Select 'Use a formula to determine which cells to format'.

5. Enter the formula =ISODD(A1)
6. Select a formatting style and click OK.
 Result. Excel highlights all odd numbers.

Explanation: always write the formula for the upper-left cell in

the selected range. Excel automatically copies the formula
to the other cells. Thus, cell A2 contains the formula
=ISODD(A2), cell A3 contains the formula =ISODD(A3), etc.
Here's another example.
7. Select the range A2:D7.

8. Repeat steps 2-4 above.

9. Enter the formula =$C2="USA"
[Link] a formatting style and click OK.
 Result. Excel highlights all USA orders.

Explanation: we fixed the reference to column C by placing a $

symbol in front of the column letter ($C2). As a result, cell
B2, C2 and cell D2 also contain the formula =$C2="USA",
cell A3, B3, C3 and D3 contain the formula =$C3="USA",
etc.

Charts and graphs

Numeric numbers are visualized using charts and graphs. A
chart is a visual that is used to portray quantitative data in
a straightforward and understandable manner. The general
summary of the numeric data included in a workbook or
worksheet is represented by a chart. Before you begin
charting, you must first enter the data that will be used to
create the chart. Charts help your audience understand the
significance of data and make comparisons and trends
much easier to observe. Excel has a total of 16 chart kinds.
Here's a basic summary of chart types and their most
typical applications.
 • Area
Good for depicting magnitude of change over time.
 • Bar
Shows the value of two or more items at the same point
in time. Good for depicting dramatic differences
between positive and negative values.
• Column
Shows two or more values side by side.
• Line
Illustrates trends over time.
• Pie
Represents the data as a percentage of the total.
• Scatter
Depicts two values and tries to show relationships,
usually Independent of time.
• Combination
Allows to layer one type of chart over another.
• 3-D
It involves the dramatic use of some of the above
charts. However, they might be hard to read and
distort the perspective of the original data.
• Surface
A 3D surface shows trends in value across 2 dimensions
in a continuous curve.
• Stock
Requires 3 series of values in the order of high low close.
Create a Chart
To create a line chart, execute the following steps.
1. Select the range A1:C5.

2. On the Insert tab, in the Charts group, click any recommended

charts symbol.
Result:

FUNCTION WITH EXAMPLE

We can perform mathematical and statistical calculation
using Excel
1. SUM()
It is a mathematical function used to add the numeric value
in a range of cells.
Syntax: =SUM(range)

Example Description

=SUM(C5:C10) Total of numbers in the range C5 to C10

=SUM(C5.C10,D5.D10) Total of numbers in the range C5 to C10
and D5 to D10
=SUM(C5,C7,C10,D5) Total of numbers in the cellsC5 ,C7,C10
and D5
2. COUNT()
Counts the number of cells that contain numbers in the
specified range. It is a statistical function.
Syntax: =SUM(range)

Example Description

=COUNT(C5:C10) Numeric cells in the range C5 to C10

=COUNT(C5,C10,D5,D10) Numeric cells in the range C5 to C10
and D5 to D10

Consider the illustration above. Place the cursor in cell E2. Use
the COUNT function in the following calculation. In cell E2,
we get the value 2 from =COUNT (A2:D2).
Functions for Counting
• Use the counter function to count only numbers.
• Use the COUNTA function to count numbers and text.
• Use the COUNTIF function to count based on a
single criterion.
• Use the COUNTBLANK function to count empty cells.
3. AVERAGE()
It is a statistical function. It calculates and returns the
average of the numeric values in the range of cells.
Syntax: =AVERAGE(range)

Example Description

=AVERAGE(C5:C10) Average of numbers in the range C5 to

C10
=AVEAGE(C5.C10,D5.D10) Average of numbers in the range C5 to
 C10 and D5 to D10
=AVERAGE(C5,C7,C10,D5) Average of numbers in the
cellsC5
,C7,C10 and D5

4. COUNTIF ()
To count cells that are equal to a value, count cells that are
bigger than or equal to a value, and so on, use the
COUNTIF function in Excel. The COUNTIF function below
counts how many cells have a value of 20.

Consider the illustration above. Place the cursor in cell D7.

Use the COUNTIF function in the following formula. In cell
D7, we get the value 2 from = COUNTIF (D2:D6,20). It
demonstrates that Manu and Soorya are both 20 years old.

LOGICAL FUNCTIONS
To determine whether a condition is true or false, or to
check for many conditions, logical functions are used. The
Logical command button's drop-down menu on the
Formulas tab of the Ribbon contains logical functions. The
essential logical functions used in Ms Excel are IF, AND,
OR, and NOT.
1. AND
The AND Function returns TRUE if all conditions are true
and returns FALSE if any of the conditions are false.
For example, we can apply the AND function in cell D2 below
Explanation: the AND function returns TRUE if the first
score is greater than or equal to 50 and the second score is
greater than or equal to 90, else it returns FALSE.
2. OR
The OR function returns TRUE if any of the conditions are
TRUE and returns FALSE if all conditions are false. For
example, we can apply the OR function in cell D2 below.

Explanation: the OR function returns TRUE if at least one

score is greater than or equal to 50, else it returns FALSE.
3. NOT
The NOT function changes TRUE to FALSE, and FALSE to
TRUE. For example, we can apply the NOT function in cell
D2 below

Explanation: in this example, the NOT function reverses the

result of the OR function

4. IF ()
To check whether a condition is true or false, use the IF
function: If the condition is true, one value is returned, and
if the condition is false, a different value is returned.
syntax= IF ( condition ,true action, false action)
Consider the worksheet below.

Example 1:
Suppose HRA is to be decided based on salary, according
to the following table
Salary HRA
<25000 10% of salary
>=25000 25% of salary
Put the cursor focus in cell E2
Enter the following formula that uses the IF function
=IF(C2<25000,C2*10%,C2*25%)
When you're finished, press the enter key. In the workbook,
you will see the following results.

5. TRUE()
This is an example of a logical function. It will return TRUE
as the logical value. There is no need for a parameter or
argument. The
TRUE function is frequently used in financial analysis in
conjunction with other logical functions such as IF, ERROR, and so
on.

Syntax: TRUE()

Consider the following workbook.

• LEFT()

Returns the specified number of characters from the left

side of a text string. If the number is not specified, it
returns the first character.
Syntax: = LEFT (String, number)

Cell A2 contains the complete name. The LEFT function can

be used to extract the first four letters of a given name.
“ARAV” is the result of this function.

• RIGHT()
Returns the specified number of characters from the right
side of a text string. If the number is not specified, it
returns the last character.
Syntax: =RIGHT (String, number)
Cell A2 contains the complete name. The RIGHT function
can be used to extract the last four letters of a given name.
This function returns "DHAV" as a result

• LEN()
USE THE LENGTH FUNCTION The length of a text string is
returned. Characters are counted even if there are spaces
between them.
Syntax: =LEN (string)

For example , LEN(“Avinash Shankar”) .The output of this

function is 16.

1. UPPER()
UPPER function converts all lower case letters in a text
string to upper case
Syntax: =UPPER (string)

For example , UPPER(“Avinash Shankar”) .The output of

this function is “ AVINASH SHANKAR”

2. LOWER ()
LOWER function converts all uppercase letters in a text
string to lowercase. LOWER does not change characters in
text that are not letters.
Syntax :=LOWER(string)
For example ,LOWER(“Avinash Shankar”) .The output of
this function is “ avinash shankar”

3. TRIM ()

TRIM function remove all the extra spaces from the text
except for the single spaces between words.
Syntax :=TRIM(string)

4. DOLLAR ()
It converts a number to text, using a currency
format. Syntax
DOLLAR ( number , [decimal_ places])
The following are the parameters: Number- The number to
convert to text Optional, decimal places It refers to the
number of decimal places that should be displayed. The
number will be rounded accordingly

Based on the spreadsheet above, the dollar examples would return

=DOLLAR(A1,0) Result : $150
=DOLLAR(A1,2) Result: $150.10
=DOLLAR(A2,1) Result : $52.1
5. EXACT ()
This function compares two strings and returns TRUE if
both values are the same.
Otherwise, it returns FALSE.
Consider an example, in the above worksheet, cell value
A3 and A4 are same so it returns TRUE.

FINANCIAL FUNCTIONS
Excel financial functions can be used to perform a number
of financial calculations, including yield calculations,
investment valuations, interest rates, internal rate of
return, asset depreciation, and payment calculations. The
finance function in business is to obtain and utilize funds
required for efficient operations.

1. The DB function is a financial function in Excel. This

function aids in the calculation of an asset's depreciation.
For each phase of the asset's lifetime, the Fixed Declining
Balance Method is used to calculate depreciation.

SYNTAX
=DB(cost, salvage, life, period, [month])
The following arguments are passed to the DB function:
• cost - Initial cost of asset.
• salvage - Asset value at the end of the depreciation.
• life - Periods over which asset is depreciated.
• period - Period to calculation depreciation for.
• month - [optional] Number of months in the first
year. Defaults to 12.
This first example calculates the depreciation for a $20,000
asset with a $10,000 salvage value. The item has a 5-year
useful life. The first year's depreciation is being calculated,
and there are 12 months in the first year. RESULT: $
2580.00 =DB (20000,10000,5,1,12). In the case of second
example calculates the depreciation for a $20,000 asset
with a $10,000 salvage value. The item has a 5-year useful
life. The second year's depreciation is being calculated, and
there are 12 months in the second year

RESULT: $ 2247.00 =DB (20000,10000,5,2,12)

2. DDB function

The DDB function in Microsoft Excel is a built-in function .It

calculates the depreciation of an asset over a specified
time period using the double-declining balance approach.

Syntax
=DDB (cost, salvage, life, period,
[factor]) Arguments are given below
• cost - Initial cost of asset.
• salvage - Asset value at the end of the depreciation.
• life - Periods over which asset is depreciated.
• period - Period to calculation depreciation for.
• factor - [optional] Rate at which the balance
declines. If omitted, defaults to 2.
Consider the following example, which calculates depreciation on a
$20,000 asset with a salvage value of $1,000. The product has
a 5- year life span. Depreciation for the first year is being
calculated. RESULT: $ 8000.00 =DDB(20000,10000,5,1).
The depreciation for the second year is being
calculated. RESULT: $ 2000.00
=DDB(20000,10000,5,2) .The depreciation for the third year is
being calculated. RESULT: =DDB(20000,10000,5,3).
3. VDB Function
The Excel VDB function is used to calculate the
depreciation of an asset over a specified time period using
the Double Declining Balance Method or another specified
depreciation rate. VDB stands for variable declining balance.
It will assist a financial analyst in developing financial models
or constructing a depreciation plan for fixed assets for
examination.
=VDB (cost, salvage, life, start_period, end_period, [factor],[no
switch])
• cost : the asset's cost price
• salvage: The salvage value after the asset has been
fully depreciated.
• Life: The useful life of the asset or the number of
periods that you will be depreciating the asset.
• start period : the year in which the depreciation will begin.
• end period : the depreciation period ends at the end
of the year.
• [factor] : [optional] depreciation factor or rate of
decrease. The default value is 2.
• no_switch: It can either be a value of TRUE or FALSE.
When depreciation exceeds the decreasing balance
calculation, this is an optional logical argument that
determines whether the technique should convert to
straight-line depreciation. The following are the
examples of possible values:
True – Excel will not use the straight-line technique of
depreciation.
False- When depreciation is larger than the decreasing
balance calculation, Excel will switch to the straight-line
depreciation technique
To compute depreciation for an asset that cost $500,000
new and has a salvage value of $50,000 after five years.
We'll figure out the depreciation for a day, a month, and a
year.
The formula for computing daily depreciation is

The formula used in calculating depreciation for a month is:

The formula used in calculating depreciation for a year is:

We didn't give the factor argument in the calculations

above, thus Excel thought it was 2 and used the DDB
approach. Excel also assumed the no_switch argument as
FALSE.

Simple Interest
4. PMT Function
The PMT function is an Excel built-in function that is
categorized as a financial function. The PMT function
calculates the monthly amount for a loan based on an
interest rate and a fixed payment .
SYNTAX:
PMT (interest rate, number payments, PV, [FV], [Type])

• Parameters or Arguments
• interest rate : The interest rate for the loan.
• number payments: The number of payments for the loan.
• PV: The present value or principal of the loan.
• FV Optional: It is the future value or the loan amount
outstanding after all payments have been made. If this
parameter is omitted, it assumes an FV value of 0.
• Type Optional: It indicates when the payments are due.
If the Type parameter is omitted, If the type parameter is
omitted, it assumes a type value of 0. Type can be one of
the following values:

Value Explanation
0 Payments are due at the end of the period. (default)
1 Payments are due at the beginning of the period.

Consider a loan with an annual interest rate of 6% a 10-year

duration, a present value of $200,000 (amount borrowed)
and a future value of 0. The PMT function below calculates
the annual payment of $27,173.59

The PMT function below calculates the quarterly payment of

$6,685.42 , so we use 6%4=1.5% for rate and 10*4=40 for Nper
The PMT function below calculates the monthly payment of
$2,220.41, so we use 6%12=0.5% for rate and 10*12=120
for Nper.

5. NPER
The NPER function is part of the Excel Financial functions
category. In financial analysis, we often wish to build a
corporate fund. The NPER function will tell us how many
periods we'll need to accomplish our goal amount. It can
also be used to calculate the number of repayment periods
for a loan.
Syntax

=NPER (rate, pmt, pv, [fv], [type])

Arguments

• rate - The rate of interest for each period.

• pmt - Payment made at the end of each period.
• pv - Present value, or the entire value of all
current payments.
• fv - [optional] The future value, or the cash balance
you'd like after the previous payment. The default
value is 0.
Ms Neethu started to work in a company. She took a loan
of rupees 200000. She joined the company at a
remuneration of rupees 40000 per month. She can pay an
EMI amount of rupees 12000 per month. Interest rate is
12.5 % per annum. Using NPER she can clear the loan in
18.40 months. Consider the formula B2/12 divided the
yearly interest by 12.
 • -B3 - monthly payment( It is a cash outflow
represented as a negative value)
• B1 -Total loan amount taken from the company.
6. INTRATE

The INTRATE Function belongs to the Excel Financial

functions category. The interest rate for a fully invested
security will be calculated. Financial analyst may have to
frequently encounter situations in which they must
evaluate investments. The INTRATE function is particularly
useful in calculating the interest rate of an unlisted bond.

The INTRATE function determines the interest rate on a fully

invested investment.

Formula
=INTRATE (settlement, maturity, investment, redemption,
[basis]) The INTRATE function uses the following
arguments:
• Settlement (necessary argument) – This is the
security's settlement date. When a security is
traded to a buyer, the settlement date is one
year following the issue date.
• Maturity (mandatory parameter) – This is the
security's maturity date, or the day on which it
will expire.
• Investment-The amount that has been invested in
the security (mandatory argument).
• Redemption (required argument) – This is the
amount we will receive at
maturity..
• Basis – OPTIONAL. The basis for which to
calculate the number of days in a year.
Consider an example, We can calculate effective annual
interest rate for a bond with a price of 4000. The
settlement date is 4- January-2018 and the maturity date is
30-April-2019
If there are no periodic interest payments, and the day
count basis is US (NASD)30/360 day count basis is used .
The INTRATE function returns 4%.
We get the result below:

The result of the INTRAATE function may appear as a

decimal. Change the cell Number Formatting to Percentage
to display the function result as a percentage.

7. Present value(PV)

PV is a programme that determines the present value of a

loan or investment using a constant interest rate. PV can
be used to calculate periodic, consistent payments or a
future value that you want to invest in.
The formula for calculating PV in
excel is PV(rate, nper, pmt, [fv],
[type]).
The inputs for the present value (PV) formula in excel
includes the following:
• RATE = Periodic interest rate
• NPER = the number of payment periods.
• PMT = Payment made each period (if not
specified, it is considered to be 0 and FV must be
included).
• [FV] = Investment's future value (if missing, it's
considered to be 0 and PMT must be included).
• [TYPE] = When payments are made (0, or if
omitted—at the end of the period, or 1—at the
start of the period)
When using PV formulas, keep in mind that any money
paid out (outflows) should be a negative figure. Money
entering the economy (inflows) is a positive figure.
PV formula in excel can be used with periodic and constant
payments, as well as future value. If you take out a home
loan, you will be required to pay a set amount of money on
a regular basis, such as Rs. 40,000 every month for two
years. In this scenario, the present value is computed
using the pmt option of Rs. 40,000.
With a predetermined future value, you may also utilise the
PV function in Excel.
Assume you have a future worth objective of Rs 25,00,000
to achieve from a 20-period investment with a 2% interest
rate. The present value can be estimated using this
function: PV(rate=2% percent, nper=20, fv=2500000,
type=0) if the payment is made at the end of each period.

• Net Present Value

The Excel NPV function is a financial function that uses a

discount rate and a series of future cash flows to calculate
the net present value (NPV) of an investment. The accurate
NPV formula in Excel subtracts the initial investment from
the present value of a series of future cash flows using the
NPV function.
The formula for calculating NPV in excel is
=NPV(rate,value1,[value2],…..)
Arguments
• Rate – Discount rate over one period
• Value1- First value representing cash flows
• Value2- [optional] Second value representing cash flows
Assume we have the following information about monetary
inflows and outflows: The required rate of return is 10%. To
calculate the NPV, we will use the formula below:
The initial investment in C5 is not included as a value , and
is instead added to the result of NPV.(Since the number is
negative).

• Future Value(FV)

FV is an Excel financial function that calculates an

investment's future value based on a fixed interest rate.
Investors and financial planners care about future value
because it helps them predict how much an investment
made today will be worth in the future.
It can be used in relation to interest paid on loans.

The formula for calculating FV in excel is

=FV (rate, nper , pmt, [pv], [type])

Arguments

• rate - The interest rate per period.

• nper - The total number of payment periods.
• pmt - The payment made each period. It must be
entered as a negative number.
• pv - [optional] The present value of future
payments. If omitted, it is assumed to be zero. Must
be entered as a negative number.
• type - [optional] When payments are due, 0 = end of
period, 1 = beginning of period. Default is 0.

For example, if a $20,000 investment generates a 8%

yearly interest rate, the Excel FV function may compute
the investment's future value after 10 years

Note that in the above FV Function

The [pmt] input in the above FV function is set to 0

because there are no continuous payments after the initial
value returned is negative, indicating an outgoing
payment. Annual interest rate must be converted to a
monthly interest rate, and the number of periods must be
converted to months.
10. The Internal Rate of Return

The interest rate obtained for an investment with

payments and income occurring at regular intervals is
known as the internal rate of return (IRR) (i.e. monthly,
annual). Payments are expressed as negative numbers,
while income is expressed as positive numbers. The
internal rate of return (IRR) is a financial statistic that is
used to calculate the profitability of possible investments.
The Internal Rate of Return (IRR) for a given cash flow,
that is, the original investment value and a series of net
income values, will be returned by IRR.

Syntax

=IRR (values, [guess])

Arguments

• values - Array or reference to cells that contain

values.
• guess - [optional] An estimate for expected IRR.
Default is
.1 (10%).
Consider an example that has a 28 % internal rate
of return. Assume that a business is commenced
for $5,000. For the first four years, the return
earned will be $2,000,
$4,000, $1,000, and $3,000, respectively.
=IRR (A1:A5) Result: 35%
 • MIRR Function
The Excel MIRR function is a financial function that
returns the modified internal rate of return (MIRR) for a
series of cash flows,
taking into account both discount rate and reinvestment
rate for future cash flows. It is a modified form of IRR in
which the NPV of
the inflows is equal to the outflows

Consider an example , Let’s find the modified IRR for a

series of cash flows in A2:A8, finance rate in D1, and
reinvest rate in D2. The formula is as simple as this:

=MIRR(A2:A,D1,D2)

Statistical Function
A mathematical technique is applied to a collection of cells
in a worksheet using statistical functions. The SUM
function, for example, is used to add the values in a range
of cells. When applying a mathematical process to a set of
cells, functions are more efficient than formulas.

Commonly Used Statistical Functions

Function Output
ABS The absolute value of a number
The average or arithmetic mean for a
AVERAGE
group of numbers
The number of cell locations in a range
COUNT
that contain a numeric character
The number of cell locations in a range
COUNTA
that contain a text or numeric character
The highest numeric value in a group
MAX
of number
The middle number in a group of numbers
MEDIAN (half the numbers in the group are
higher than the
median and half the numbers in the
 group are
lower than the median)
The lowest numeric value in a group
MIN
of numbers
The number that appears most frequently
MODE
in a group of numbers
The result of multiplying all the values
PRODUCT
in a range of cell locations
SQRT The positive square root of a number
The standard deviation for a group of
STDEV.S
numbers based on a sample
SUM The total of all numeric values in a group

1. MEDIAN
A value that splits a sample into two sections is called the
median. It divides the population or probability distribution
with the same number of values in each section.

The MEDIAN function returns the median (middle

number) in a group of supplied numbers.

For example, =MEDIAN(12,15,16,18,19) returns 16.

Consider the following worksheet and the result obtained

2. MODE
MODE will take a list of numbers and calculate the
value that appears the most frequently. If the supplied
data contains two or
more most frequently occurring values, the function
returns the lowest of the two.

The Excel MODE function returns the most frequently

occurring number in a numeric data set.
For example, =MODE(12,13,12,14,12,15) returns 12

3. MEAN
The mean, also known as the statistical mean, is a value
that may be computed by summing all of the data points in
a collection and then dividing the total by the number of
points. The AVERAGE function in Excel does just that: it
adds up all the numbers and divides the total by the
number of numbers.

Mean is calculated using the formula given below

Mean = Sum of All Data Points / Number of Data Points
4. STANDARD DEVIATION

The standard deviation is a measurement of how far the

values deviate from the mean (the mean). The difference
between a set of data and its mean or average value is
shown by standard deviation The STDEV.S function (the
S stands for Sample) in Excel estimates the standard
deviation based on a sample. For example, you're teaching
a large group of students. You only have the test scores of
10 students. The sample size equals 10. The STDEV.S
function uses the following formula
=STDEV.S(number1, [number2], ...)

CORRELATION
Correlation is the measure of association of variables. To
find the correlation coefficient between two variables, use
the CORREL formula in Excel. The correlation coefficient of
arrays 1 and array 2 can be used to determine the
relationship between the two properties. For example, –
consider the relationship between a certain stock and the
market index. Consider the following data set in Excel to
find the correlation coefficient using the Formula.
=CORREL (array1, array2)
Array1 – array of
variable X Array2-
array of variable Y
Let’s find the correlation coefficient for the variable X and Y1.
=CORREL(A2:A6,B2:B6)
Similarly we can find correlation coefficient for(X,Y2) and (X,Y3).

REGRESSION

Regression is a statistical technique used in finance,

investing, and other fields to identify the strength and
nature of a relationship between one dependent variable
(typically indicated by Y) and a set of other variables
(known as independent variables).
Investment and financial managers can use regression to
value assets and analyse the relationship between
variables like commodity prices and the stocks of
companies that deal in those commodities.

Consider an example

1. On the Data tab, in the Analysis group, click Data Analysis.

2. Select Regression and click OK.

3. Select the Y Range (A1:A8). This is the predictor variable
(also called dependent variable).
4. Select the X Range(B1:C8). These are the explanatory
variables (also called independent variables). These
columns must be adjacent to each other.
5. Check Labels.
6. Click in the Output Range box and select cell A11.
7. Check Residuals.
8. Click OK.

Excel produces the following Output

Suppose we can't find the Data Analysis button, Click to
load the Analysis ToolPak add-in.

Consider the following steps

Step1 - On the File tab, click Options.

Step2 Under Add-ins, select Analysis ToolPak and click on

the Go button.
Step3 Check Analysis ToolPak and click on OK.

Step 4 On the Data tab, in the Analysis group, you can

now click on Data Analysis
5. For example, select Histogram and click OK to
create a Histogram in Excel.

Date & Time Function

Excel Date and Time functions can be used to extract

information from, and perform operations on, Excel dates
and times. The following tables lists all the Date & Time
functions-

NO Function and Description

1 DATE : Returns the serial number of a particular
date
2 DATEVALUE: Converts a date in the form of text
to a serial number.
3 DAY: Converts a serial number to a day of the
month.
4 DAYS360: Calculates the number of days
between two dates based on a 360-day year.
5 NOW: Returns the serial number of the current
date and time.
6 TIME: Returns the serial number of a particular
time.
7 TIMEVALUE: Converts a time in the form of text
to a serial number.
8 WORKDAY: Returns the serial number of the
date before or after a specified number of
workdays.
9 WEEKDAY: Converts a serial number to a day of
the week.
10 YEAR: Converts a serial number to a year.

Date Function
1. To add a number of days to a date, use the following
simple formula.
2. To add the number of years, months and/or days, use
the DATE function.

Note: the DATE function accepts three arguments: year,

month and day.

DATEVALUE FUNCTION
Insert the function DATEVALUE by selecting the
corresponding cells =DATEVALUE(A2)

DATEVALUE returns strings of serial numbers; to display in

date format, we need to format the cells to date format.

Click format cells. We will get a list of format options as

shown below. Choose the appropriate date option by
default; excel will choose the format which has been
marked as *
DAYS 360 FUNCTION
Time Function
To add the number of hours, minutes and/or seconds, use
the TIME function.

Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 =

10 seconds.

Current Date & Time

To get the current date and time, use the NOW function.

LOOKUP FUNCTIONS
Excel has a number of functions for searching and
extracting data from a list or table. These are known as
Lookup functions .Lookup & Reference Functions are the
often used formulas in excel along with logical functions. If
two rows or columns are to be compared while doing
financial analysis, LOOKUP function can be used. It is
designed to handle the simplest cases of vertical and
horizontal lookup and they can be used for a variety of
purposes, such as:
• returning the appropriate tax rate based on salary
• returning the data that is at, say, the second
column, third row of a table
• returning the description, price and discount rate of
an item, based on its code in the data inventory.

Reference functions
Reference functions return information about the cell
reference as text values, such as the entire address, the
row or column.
Examples of reference functions are: Column, Row and Address
HLOOKUP and VLOOKUP are more advanced versions of
the LOOKUP function.
VLOOKUP and HLOOKUP are Excel functions that allow to
search a table of data and return appropriate information
based on what the user has entered.

VLOOKUP
Syntax:
=VLOOKUP(value, table, col_index,[range-lookup])

• value - The value to be located in the first column of a

vertical table (or the first row of a horizontal table). It
can be numeric, text or a cell reference.

• table - The range reference or name of the lookup table.

• col_index- The column (row) of the table from which
the value is to be returned.
• range lookup [optional] TRUE= approximate
match(default). FALSE= exact match.
Using the Emp-no in columnA as a lookup value, VLOOKUP
can get the Emp_ name, Designation , Salary for any order.
To get a value from a particular column , provide the
appropriate number as the column index. For example to
get the employee name for Emp_ no 103, the formula is:
=VLOOKUP(103,A2:D4,2,FALSE)

HLOOKUP
HLOOKUP function “ looks for a value in the top row of a
table or array of values and returns the value in the same
column from a row you specify”.
Syntax
=HLOOKUP(value, table, row_ index,[ range-lookup])
• value -The value to be searched in the topmost row
of the table.
• It can be numeric , text or a cell reference.
• table - The range reference or name of the lookup table.
• row_ index is the row number in the table array,
from which the matching value is to be returned.
• range lookup [optional] TRUE=
approximate match(default). FALSE= exact
match.
 In our example we can calculate the total strength of
BBA students in the year 2018-19 using the formula
HLOOKUP("BBA",B1:E5,3,1)

TRANSPOSE Function
This function changes the layout of data, from horizontal to
vertical, or from vertical to horizontal. It can be used on its
own, or combined with other Excel functions.

SYNTAX
TRANSPOSE(array)

Array is an array or arrange of cells to be transposed.

GETPIVOTDATA
This function can retrieve specific data from a pivot table by
name based on the structure, instead of cell references. It
is categorized under the Lookup and Reference function.

SYNTAX
=GETPIVOTDATA(data_field,pivot_table,[fileld1,item1],…)

• data_ field – The name of the value field to query.

• Pivot_ table – A reference to any cell in the pivot
table to query
• field1, item1 –[optional] Afield/item pair.

PIVOT TABLE
Pivot Table is an analysis tool, which summarizes a large
amount of data in a readable manner. A pivot table is a
tool for summarizing, sorting, grouping, counting, totaling,
or averaging data in a table. It allows grouping by any
field, and using advanced calculations on them. A pivot
table is a useful Excel reporting tool because it allows users
to quickly assess data and make decisions based on it. This
serves as a huge advantage in the industrial world, where
it is crucial to make precise and quick decisions.
 To create a Pivot table, let us consider the
following worksheet.

Insert a Pivot Table

To insert a pivot table, execute the following steps.
1. To make a PivotTable, choose the cells you want to use.
2. Select Insert > PivotTable.

The dialogue box below appears. Excel will choose the data
automatically. New Worksheet is the default location for a
new pivot table.
3. Click OK.

Drag fields
We can calculate the total amount by dragging the fields
below to the various regions.
The PivotTable Fields pane appears
1. Drag the field ITEM to the raw area.
 2. Drag the field AMOUNT to the Values area.
3. Drag the field SALESMAN to the raw area
Pivot table will be generated and displayed on the screen.

HYPERLINK
In Excel, hyperlinks allow users to construct a shortcut to any
worksheet, file, folder, or webpage. The HYPERLINK
function generates a shortcut that opens a document
saved on a network server, intranet, or the Internet, or
moves to another position in the current workbook. Excel
moves to the specified URL or opens the document when
you click a cell that contains a HYPERLINK function.
SYNTAX
HYPERLINK (link_ location, [ friendly _name])
Link_ location - The path to the file or page to be opened.
Friendly_ name – [optional] The link text to display in a
cell.
Consider following worksheet, To link to another cell in the
same worksheet, prefix the cell with “#”
To create a hyperlink to an existing file or webpage,
execute the following steps.
1. Select cell A1.
2. On the Insert tab , in the link group, click Link, a
dialog box appears.
To create a hyperlink to an existing file, select a file
3. To create a hyperlink to a web page, type the Text to
display, the Address , click OK

RESULT

To create a hyperlink to a place in this document,

execute the following steps.

1. Select cell A2
2. On the Insert tab, in the Links group , click Link, a
dialog box appears.
3. Type the Text to display, the cell reference , and click OK.
Result

***
 V Semester [Link]. Degree CBCSS (OBE) Regular
Examination, November 2021
(2019 Admns. Only)
Core Course
5B11COM: Computer Application – III
INFORMATION TECHNOLOGY FOR BUSINESS
Time: 2 Hours
Max. Marks: 20
PART – A
Answer any three questions from the following. Each
question carries 1 mark. (3*1=3)

1. What is a customer relationship management system?

2. Explain Management Information System.
3. What is the use of functions in Excel?
4. What is a human resource management system?

PART B
Answer any three questions from the following. Each
questions carries 3 marks. (3*3=9)
5. Write down the benefits of ERP.
6. Write a note on statistical function in Excel.
7. What are the components of ERP?
8. Write a note on Charts in Excel.
PART C
Answer any one question from the following. The question carries
8 marks. ` (1*8=8)
9. Briefly explain different types of E-payment systems.
[Link] are the characteristics of MIS?
 1. What is a customer relationship management system?
• Customer Relationship Management (CRM) is a
sound concept or approach for strengthening customer
relationships while lowering costs and increasing
productivity and profitability in the workplace.
• A CRM system is used by small and large businesses
alike, with the primary purpose of providing effective
customer service.
Customer Relationship Management is an essential technique
for any firm. Because the customer is the most crucial
aspect of every company
2. Explain Management Information System

Management Information System or MIS is a well-structured

method which combines the principles, theories and
practices of management using an information system. MIS
plays an important role in business organization for
planning and decision-making process. It provides
managers with different tools which help organize,
evaluate and run their departments efficiently. MIS also
provides information to the employees at various levels of
management for performing their respective jobs.
3. What is the use of functions in Excel?
Ans: FUNCTION WITH EXAMPLE
We can perform mathematical and statistical calculation using
Excel
1. SUM()
It is a mathematical function used to add the numeric value
in a range of cells.
Syntax: =SUM(range)

Example Description
=SUM(C5:C10) Total of numbers in the range C5 to
C10
=SUM(C5.C10,D5.D10) Total of numbers in the range C5 to
C10 and D5 to D10
=SUM(C5,C7,C10,D5) Total of numbers in the cellsC5
,C7,C10 and D5
2. AVERAGE()
It is a statistical function. It calculates and returns the
average of the numeric values in the range of cells.
Syntax: =AVERAGE(range)
Example Description

=AVERAGE(C5:C10) Average of numbers in the

range C5 to C10
=AVEAGE(C5.C10,D5.D10) Average of numbers in the
range C5 to C10 and D5 to
D10
=AVERAGE(C5,C7,C10,D5) Average of numbers in
the cellsC5 ,C7,C10
and D5

3. COUNT()
Counts the number of cells that contain numbers in the
specified range. It is a statistical function.
Syntax: =SUM(range)
Example Description
=COUNT(C5:C10) Numeric cells in the range C5 to
C10
=COUNT(C5,C10,D5,D10 Numeric cells in the range C5 to
) C10 and D5 to D10

Consider the illustration above (on previous page). Place

the cursor in cell E2. Use the COUNT function in the
following calculation. In cell E2, we get the value 2 from
=COUNT (A2:D2).
Functions for Counting
• Use the counter function to count only numbers.
• Use the COUNTA function to count numbers and text.
 • Use the COUNTIF function to count based on a
single criterion.
• Use the COUNTBLANK function to count empty cells.

4. COUNTIF ()
To count cells that are equal to a value, count cells that are
bigger than or equal to a value, and so on, use the
COUNTIF function in Excel. The COUNTIF function below
counts how many cells have a value of 20.

Consider the illustration above. Place the cursor in cell D7. Use
the COUNTIF function in the following formula. In cell D7,
we get the value 2 from = COUNTIF (D2:D6,20). It
demonstrates that Manu and Soorya are both 20 years old.
4. What is a human resource management system?

Human Resource Management System (HRMS) Refers to a set

of software used by a company to manage internal HR
functions. The human resource information system
supports the human resource management function of the
organization. The function of human resource management
is also called personnel management. The personnel
information system also handles employee compensation,
wages, payroll, labor relations, and employee services and
benefits.
5. Write down the benefits of ERP.
Benefits of ERP
The benefits of ERP are many. ERP system benefits range
from optimizing processes to helping different departments
better collaborate to improving the relationship between a
business and its customers. The following are several of
the most valued benefits to utilizing ERP.
• Increased Efficiency
One of the primary advantages of ERP is that it
allows companies to automate manual and routine
functions. This frees up employees to focus on more
revenue-driving tasks, as well as standardize
common business processes. The increased
efficiency leads to improved demand forecasting,
reduced production bottlenecks, shorter lead times,
and a more transparent and responsive supply
chain.
• Improved Collaboration
ERP systems also connect teams, improving
communication and employee engagement. With an
ERP system, every approved employee has on-
demand access to operational data, allowing them
to understand all of the company’s moving parts
and the role they play
• Better Customer Service
A portion of the data ERP provides a central hub for
is customer information. With centralized customer
data, multiple departments can easily access and
collaborate on customer needs for faster response
times and improved delivery and order accuracy.
Reduction of Lead-Time
Lead-Time is the elapsed time between placing an order
and receiving it. By reducing Lead-Time organization
should have an efficient inventory management system,
which is integrated with the purchasing, production
planning and production departments.
• Flexibility in customization
The modular design of most ERP systems is one of
their biggest advantages. Most ERP vendors offer
several different applications focusing on specific
parts of the businesses process. Users can use
these different applications at will depending on
their requirements. Each application is designed to
be used in conjunction with other applications and
the system as a whole.
6. Write a note on statistical function in Excel.
Ans: Statistical Function
A mathematical technique is applied to a collection of cells
in a worksheet using statistical functions. The SUM
function, for example, is used to add the values in a range
of cells. When applying a mathematical process to a set of
cells, functions are more efficient than formulas.

Commonly Used Statistical Functions

Function Output
ABS The absolute value of a number
The average or arithmetic mean for a
AVERAGE
group of numbers
The number of cell locations in a range
COUNT
that contain a numeric character
The number of cell locations in a range
COUNTA
that contain a text or numeric character
The highest numeric value in a group
MAX
of numbers
The middle number in a group of
numbers (half the numbers in the group
MEDIAN
are higher than the median and half the
numbers in the group are
lower than the median)
The lowest numeric value in a group
MIN
of numbers
The number that appears most frequently
MODE
in a group of numbers
The result of multiplying all the values
PRODUCT
in a range of cell locations
SQRT The positive square root of a number
The standard deviation for a group of
STDEV.S
numbers based on a sample
SUM The total of all numeric values in a group

7. What are the components of ERP?

An ERP system is a type of information system used to

integrate all of your organization's data and processes into
one consistent system. The ERP system is considered an
enterprise-wide framework that covers all major
departments of an organization, including sales, marketing,
manufacturing, inventory management, accounting,
finance, and human resources. These departments are
considered as the backbone of the organization
The below mentioned figure shows the major functional
components of the ERP system.

8. Write a note on Charts in Excel.

Charts and graphs
Numeric numbers are visualized using charts and graphs. A
chart is a visual that is used to portray quantitative data in
a straightforward and understandable manner. The general
summary of the numeric data included in a workbook or
worksheet is represented by a chart. Before you begin
charting, you must first enter the data that will be used to
create the chart. Charts help your audience understand the
significance of data and make comparisons and trends
much easier to observe. Excel has a total of 16 chart kinds.
Here's a basic summary of chart types and their most
typical applications.
• Area : Good for depicting magnitude of change over time.
• Bar :Shows the value of two or more items at the
same point in time. Good for depicting dramatic
differences between positive and negative values.
• Column: Shows two or more values side by side.
• Line: Illustrates trends over time.
• Pie: Represents the data as a percentage of the total.
• Scatter: Depicts two values and tries to show
relationships, usually Independent of time.
• Combination:Allows to layer one type of chart over another.
• 3-D:It involves the dramatic use of some of the above
charts. However, they might be hard to read and
distort the perspective of the original data.
• Surface:A 3D surface shows trends in value across 2
dimensions in a continuous curve.
• Stock: Requires 3 series of values in the order of high
low close.
Create a Chart
To create a line chart, execute the following steps.
1. Select the range A1:C5.

2. On the Insert tab, in the Charts group, click any

recommended charts symbol.

Result:
9. Briefly explain different types of E-payment systems.

[Refer Page No: 34]

10. What are the Characteristics of MIS ?

MIS exhibits different characteristics which help specify

the approach, design and development of MIS. The various
characteristics of MIS include the following:
• System approach: MIS follows the system approach
which implies a step by step approach to the study
of complete system of an organization and its
performance in the light of the objectives of the
system. In doing so, MIS takes a comprehensive
view of the subsystems that operate within the
organization.
• Management-oriented: The management-oriented
nature of MIS means that you need to follow a top-
down approach when designing MIS. The top-down
approach suggests that system development begins
with determining management needs and overall
business goals. In addition, the MIS development
plan needs to be derived from the overall business
plan. Since MIS is management-oriented, it also
means that the administrator is actively involved in
directing system development work.
• Need-based: The design and development of MIS
should meet the information required by the
managers at different levels of management such
as strategic planning, management control and
operational control. This implies that MIS needs to
provide the requirements for the managers
throughout the management hierarchy of the
organization.
• Future-oriented: The design and development of MIS
should not be restricted to the past information. It
should also look into the future aspects on the basis
of the predictions made for the system.
CORE COURE X1: INFORMATION TECHNOLOGY FOR
BUSINESS

SEMESTER -5

UNIT-1

E-COMMERCE

E-commerce – Ecommerce, features, advantages,

difference between traditional commerce and E-
commerce, Components of E- commerce- B2B , B2C ,C2C,
C2B – Process of E- commerce[work flow] – E –governance
– laws governing E-commerce . IT Act 2000, Companies
Act 2013 , GST 2017 , FEMA 1999 , Consumer
Protection Act [ brief outline only] – E –payment system –
meaning
, importance , advantages , types –UPI , IMPS , E-Wallets,
Aadhar enablesd payment system , OR Code payment,
NEFT, RTGS , Net banking, Plastic money [ brief outline
only ]

UNIT-2

BUSINESS INFORMATION SYSTEM

Information systems- meaning, components, business process-

role of information system in business -types of information
systems- Transaction Processing System, Office
Automation System, Decision Support Systems,
Knowledge Management System [brief outline only]
Management Information System – MIS Definition – Need –
Benefits – Functions- Objectives – Characteristics –Role of
MIS.

UNIT-3

ENTERPRISE RESOURCE PLANNING

Concept and definition , features , components , levels of ERP ,
Benefits of ERP, Modules of ERP , Phases of ERP
implementation
, Limitation of ERP – Customer relationship Management
System [CRM] –Supply Chain Management System
[SCM] – Human Resources Management System [HRMS]
– Accounting Information System .

UNIT-4
SPREADSHEET APPLICATIONS FOR BUSIENSS
Spreadsheet Applications for Business – Conditional formatting
– Charts and diagrams – Logical Functions : AND, OR
NOT, IF , IFNOT, COUNT ,COUNTIF, TRUE- Text
functions: UPPER, LOWER, LEFT, RIGHT,TRIM
,TEXT,LEN DOLLAR,EXACT:
Financial Functions : Depreciation [ DB, DDB, VDB] , simple
Interest [ PMT, NPER, INTRATE] – Present Value, Net
Present Value, Future Value [ PV, NPV, FV] –Internal Rate
of Return [ IRR, MIRR] – Statistical Functions : Mean ,
Median, Mode, Standard Deviation, Correlation ,
Regression – Date & Time Functions
:DATE , DATEVALUE , DAY ,DAYS360, NOW, TIME TIMEVALUE
, WORKDAY, WEEKDAY , YEAR Lookup and Reference
Functions:Hlookup, Vlookup, Transpose
Function, GETPIVOTIDATA-PIVOT
TABLE –HYPERLINK .

PRACTICE QUESTIONS
Question 1
• Create a worksheet as shown below
• Enter the formula to find COMMISSION for the first employee.
The commission rate is 2% of sales, COMMISSION = SALES *
2%
Copy the formula to the remaining employees.
• Enter the formula to find TOTAL SALARY for the first
employee where: TOTAL SALARY = SALARY +
COMMISSION
Copy the formula to the remaining employees.
• Enter formula to find TOTALS, AVERAGE, HIGHEST,
LOWEST, and COUNT values.
Copy the formula to each column.
Output

Panda EST
Monthly Sales Report -July

Sales
[Link] Name sa Amount commis Total
la sion salary
ry
S101 Ahmed 16 2500 50 1650
00
S105 Hassan 18 3000 60 1860
00
S112 Ali 15 2200 44 1544
00
S107 Waleed 20 4500 90 2090
00
S110 Mohammed 17 3500 70 1770
00
S103 Samir 16 2500 50 1650
00

Total 10 18200 364 10564

20
0
average 17 3033.333 60.6666 1760.666
00 333 6667 667
highest 20 4500 90 2090
00
Lowest 15 2200 44 1544
00
Count 6 6 6 6

Question 2

• Create a worksheet as shown below

• Set labels alignment appropriately.

• Use warp text and merge cells as desired.

• Apply borders, gridlines and shading to the table as desired.

 • Calculate the Gross Pay for employee; enter a formula in cell E4
to multiply Hourly Rate by Hours Worked.
• Calculate the Social Security Tax (S.S Tax), which is 6%
of the Gross Pay; enter a formula in cell F4 to multiply Gross
Pay by 6%.
• Calculate the Net Pay; enter a formula in cell G4 to subtract
Social Security Tax from Gross Pay.
Output

Payroll
01-01-
Date 11

EMPL
EMPL name HOURLY Hours gross pay Net pay
Number RATE worked ss tax

E00001 Ford 7.5 3 262.5 15.75 246.75

E00002 Mino 8 3 240 14.4 225.6

E00003 Bell 6.5 2 162.5 9.75 152.75

E00004 Davis 9 4 360 21.6 338.4

E00005 Turro 10 3 390 23.4 366.6

Question 3
• Create the worksheet that shows marks secured by the students in
various subjects and find total using auto sum.

Roll No Name Marks1 Marks2 Marks3 Marks4

2001 Ramu 64 48 56 48
2002 Srikanth 78 57 75 57
2003 Ramesh 59 88 85 88
2004 Radha 86 84 49 84
2005 Kalyan 89 79 59 79
• Find Mean, Median and Mode using above data.

• Using above table find the variance and standard deviation

output
 Roll Name Marks1 Marks2 Marks3 Marks4 TOTAL
No
2001 Ramu 64 48 56 48 216
2002 Srikanth 78 57 75 57
267
2003 Ramesh 59 88 85 88 320
2004 Radha 86 84 49 84 303
2005 Kalyan 89 79 59 79 306

Mean 75.2 71.2 64.8 71.2 282.4

Median 78 79 59 79 303

Mode 1 1 4 1 2

standard
Deviation 13.2551 17.6550 14.77 17.6550 41.955929

Variance 175.7 311.7 218.2 311.7 1760.3

Question 4

• Create the following worksheet Salary (Enter at least 5 records)

Name Basic HRA TA Deductions Gross TAX Net

Pay Pay

Calculations are done as follows: HRA = 50% of Basic;

• TA = 10% of Basic;

• Assume your deductions

• Gross Pay is Basic + HRA+TA-Deductions

• Tax is 30% of Gross Pay

• Net Pay is Gross Pay –Tax

Output

Deductions=basic*10%
 PAYROLL
Name Basic HRA TA Deductions Gross TAX Net
Pay Pay

Dipu 10000 5000 1000 1000 15000 3000 12000

6000 1200 1200 18000 3600 14400
Asna 12000
7500 1500 1500 22500 4500 18000
Rohit 15000
10000 2000 2000 30000 6000 24000
Riju 20000
5000 1000 1000 15000 3000 12000
Varun 10000

Question 5

• TAX should be 100.

• TOTAL PRICE BEFORE TAX =NO. OF ITEMS * ITEM
PRICE.
• TOTAL PRICE AFTER TAX = TOTAL PRICE BEFORE TAX
+ TAX
 • Find Count of Items, Average of Taxes, Min Item PRICE and
Max Item PRICE

OUTPUT
TOTAL TOTAL
ITEM NO. OF ITEM PRICE PRICE
TAX
NO ITEMS PRICE BEFORE AFTER
TAX TAX
100 115 30 100 3450 3550
101 256 12 100 3072 3172
102 49 56 100 2744 2844
103 23 150 100 3450 3550
104 840 5 100 4200 4300
105 200 56 100 11200 11300
106 294 300 100 88200 88300
107 4 90 100 360 360

COUNT OF
ITEMS 8
AVERAGE OF
TAX 100
MIN ITEM PRICE 5
MAX ITEM
PRICE 300
Question 6

• Let Rate = 5.00%

NPER = 240
PV = 150,000
• TAX (If ITEM PRICE is less than 100, TAX is 50, otherwise it
should be 100).
• [Link] PRICE BEFORE TAX =NO. OF ITEMS * ITEM
PRICE.
• [Link] PRICE AFTER TAX = TOTAL PRICE BEFORE
TAX + TAX
• TAX (If ITEM PRICE is less than 100, TAX is 50, otherwise it
should be 100).
 • [Link] PRICE BEFORE TAX =NO. OF ITEMS * ITEM
PRICE.
• [Link] PRICE AFTER TAX = TOTAL PRICE BEFORE
TAX + TAX
FV = 0

Calculate PMT, Rate, NPER and FV

OUTPUT
5.00%

RATE
NPER 240
PV 150000
FV 0.00
PMT ₹ -7,500.06
NPER 240
RATE 5%
FV ₹ -0.00
Question 7
• Implement string functions such as len(), left(), right(),
mid(),lower(),upper() and trim().

OUTPUT

Text: HAI hru ,I Am FINE

LEN( ) 21
LEFT( ) H
RIGHT( )
MID( ) Hru
LOWER( ) hai hru ,i am fine
UPPER ( ) HAI HRU ,I AM FINE
TRIM( ) HAI hru ,I Am FINE