SPREAD SHEET BASIS
• Spread sheet is a simple worksheet consisting of rows and columns in which any data can be entered and revisited again and again
• A spreadsheet, is used to record and compare numerical or financial data.
• There are two types of spread sheets
• Manual spread sheet
• Electronic spread sheet
• Originally, spreadsheets only existed in paper format, but now they are most likely created and maintained through a software program that displays the numerical information in rows and columns.
• Spreadsheets can be used in any area or field that works with numbers and are commonly found in the accounting, budgeting, sales forecasting, financial analysis, and scientific fields.
• Spread sheets are used for performing calculations, recalculating results if any stored data is changed, creating financial reports, comparing reports etc…
Advantages of electronic spread sheets are
• Calculation
• Automatic Recalculation
• What if analysis
• Use of functions
• Repeat information Introduction to MS-Excel
• Ms-Excel is one of the most versatile and popular spread sheet programs.
• It serves as an electronic pad for the accountants and other financials
• It can easily perform simple as well as complex mathematical calculation.
Three Types of Cell Entries
• Labels – Text
• Values – Numbers
• Function – Formula ( sum, average, mod etc )
Important Excel Concept
=
=(E4/H9)
=SUM(F6:F19)
=AVERAGE(A1:A50)
Different interpretation to use a function
• There are many flexible methods to use the functions.
• For eg SUM function
1. You can use AUTOSUM
2. Or =E4+E5+…….
3. Or =SUM(E3:E5)
4. Or =SUM(E3,E4,E5) Etc……..
A Function
• Performs a predefined operation
• A function
• Accepts one or more arguments as input
• Performs the indicated calculation
• Returns another value as output
Main Functions
• Financial Functions
• Statistical Functions
• DATABASE Functions
Annuity Functions
• An annuity is a series of constant cash payments made over a continuous period
• A car loan or a mortgage is an annuity
• Benefits received each period is an annuity
• Cash you pay out is represented by a negative number; cash you receive is represented by a positive number
• A $1,000 deposit to the bank would be represented by the argument -1000 if you are the depositor
• A $1,000 deposit to the bank would be represented by +1000 if you are the bank
The following functions apply to annuities:
• NPV
• IRR
• PMT
• IPMT
• Be consistent about the units for specifying rate and nper
Monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper.
• Annual payments on the same loan, use 12% for rate and 4 for nper
Net present value (NPV): the sum of the present values of all cash inflows minus the sum of the present values of allcash outflows. The internal rate of return (IRR): (1) the discount rate that equates the sum of the present values of all cash inflowsto the sum of the present values of all cash outflows; (2) the discount rate that sets thenet present value equal to zero.The internal rate of return measures the investment yield.
PMT and IPMT
• PMT- This function calculates the payment for a loan based on constant payments and constant interest rate
• IPMT- This function calculates the interest payment based on periodic , constant payments and a constant interest rate NPV
• Net present value (NPV): the sum of the present values of all cash inflows minus the sum of the present values of all cash outflows.
• NPV(rate,value1,value2,…)
• Rate is the rate of discount over the length of one period
• Value1,value2,… must be equally spaced in time and occur at the end of each period
IRR
• The internal rate of return (IRR):
• (1) the discount rate that equates the sum of the present values of all cash inflowsto the sum of the present values of all cash outflows;
• (2) the discount rate that sets the net present value equal to zero.
• The internal rate of return measures the investment yield.
• IRR(values,guess)
• Values are an array or a reference to cells that contains numbers for which you want to calculate the internal rate of return
• Guess is a number that you guess is close to the result of IRR
PMT
• This function calculates the payment for a loan based on constant payments and constant interest rate
PMT(rate,nper,pv,fv,type)
• Rate is the interest rte of the loan
• Nper is the total number of payments for the loan
• Pv is the present value
• Fv is the future value
• Type is the number 0 or 1 and indicates when payments are due
IPMT
• This function calculates the interest payment based on periodic , constant payments and a constant interest rate
• IPMT(rate,per,nper,pv,fv,type)
• Rate is the interest rate per period
• Per is the period for which you want to find the interest and must be in the range 1 to nper
• Nper is the total number of payment periods in an annuity
• Pv is the present value
• Fv is the future value
• Type is the number 0 or 1 and indicates when payments are due
For example:
Let's take a look at a few examples:
This first example returns the monthly payment on a $5,000 loan at an annual rate of 7.5%. The loan is paid off in 2 years (ie: 2 x 12). All payments are made at the beginning of the period.
=Pmt(7.5%/12, 2*12, 5000, 0, 1)
This next example returns the weekly payment on a $8,000 loan at an annual rate of 6%. The loan is paid off in 4 years (ie: 4 x 52). All payments are made at the end of the period.
=Pmt(6%/52, 4*52, 8000, 0, 0)
This next example returns the annual payment on a $6,500 loan at an annual rate of 5.25%. The loan is paid off in 10 years (ie: 10 x 1). All payments are made at the end of the period.
=Pmt(5.25%/1, 10*1, 6500, 0, 0)
This final example returns the monthly payment on a $5,000 loan at an annual rate of 8%. The loan is paid on for 3 years (ie: 3 x 12) with a remaining balance on the loan of $1,000 after the 3 years. All payments are made at the end of the period.
=Pmt(8%/12, 3*12, 5000, 1000, 0)
For example:
Let's take a look at a few examples:
This first example returns the interest payment for a $5,000 investment that earns 7.5% annually for 2 years. The interest payment is calculated for the 8th month and payments are due at the end of each month.
=IPmt(7.5%/12, 8, 2*12, 5000)
This next example returns the interest payment for a $8,000 investment that earns 6% annually for 4 years. The interest payment is calculated for the 30th week and payments are due at the beginning of each week.
=IPmt(6%/52, 30, 4*52, 8000, 0 ,1)
This next example returns the interest payment for a $6,500 investment that earns 5.25% annually for 10 years. The interest payment is calculated for the 4th year and payments are due at the end of each year.
=IPmt(5.25%/1, 4, 10*1, 6500)
Statistical Functions in Excel
Excel has a wide variety of built-in statistics functions that give, the standard and mean deviation of a data sample, and the mean, median and mode of a set of values.
Average Function
Returns the average (arithmetic mean) of the arguments.
Syntax ::: AVERAGE(number1,number2,...)
Number1, number2, ... are 1 to N numeric arguments for which you want the average.
Example ::: Data = 10,7,9,27,2
AVERAGE(A2:A6) Average of the numbers above (11)
AVERAGE(A2:A6, 5) Average of the numbers above and 5 (10)
Median Function
Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.
Syntax ::: MEDIAN(number1,number2,...)
Number1, number2, ... are 1 to N numeric arguments for which you want the median.
Example ::: Data = 1,2,3,4,5,6
MEDIAN(A2:A6) Median of the first 5 numbers in the list above (3)
MEDIAN(A2:A7) Median of all the numbers above, or the average of 3 and 4 (3.5)
AVEDEV Function
Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.
Syntax ::: AVEDEV(number1,number2,...)
Number1, number2, ... are 1 to 30 arguments for which you want the average of the absolute deviations. You can also use a single array or a reference to an array instead of arguments separated by commas.
Example ::: Data = 4,5,6,7,5,4,3
AVEDEV(A2:A8) Average of the absolute deviations of the numbers above from their mean (1.020408)
STEDEV Function
Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the standard value.
Syntax ::: STDEV(number1,number2,...)
Number1, number2, ... are 1 to 30 number arguments corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.
Example :::
Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength. The example may be easier to understand if you copy it to a blank worksheet.
Data = 1345,1301,1368,1322,1310,1370, 1318,1350,1303,1299
STDEV(A2:A11)Standard deviation of breaking strength (27.46391572)
CORREL Function
Returns the correlation coefficient of the array1 and array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.
Syntax ::: CORREL(array1,array2)
Array1 is a cell range of values.
Array2 is a second cell range of values.
Example ::: Suppose given two data sets regarding the population density & death rate of a particular area. Find the correlation between the two.
DENSITY :: 300,200,400,500,600
DEATH RATE :: 9,7,12,14,16
CORREL(A2:A6,B2:B6) = 0.997176
RANK Function
Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Syntax ::: RANK(number,ref,order)
Number is the number whose rank you want to find.
Ref is an array of, or a reference to, a list of numbers.
Order is a number specifying how to rank number.
• If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.
• If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order
Database Functions in Excel
• In this information age, the major challenge for every organization is to keep proper database in order to prosper in the future.
• Databases are store-houses of information.
• Provides latest information.
• Database is an organized collection of related information about a particular subject or purpose.
• Information in database is stored in rows and columns
• Ms-Excel also contains a good database which is a collection of information that is organized so that it can easily be accessed, managed, and updated
What is a database?
A database is a collection of logically related data designed to meet the information needs of one or more users
A database defines a structure for storing information. Databases are typically organized into tables, which are collections of related items. You can think of a table as a grid of columns and rows. Egs Oracle, DB2, and SQL Server.
A database is a collection of information that is organized so that it can easily be accessed, managed, and updated
Traditional databases are organized by fields, recordsand files. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records.
To access information from a database, you need a database management system. This is a collection of programs that enables you to enter, organize, and select data in a database.
DAVERAGE Function
Averages the values in a column of a list or database that match conditions you specify.
Syntax ::: DAVERAGE (database,field,criteria)
• Database is the range of cells that makes up the list or database.
• Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as “-----” as a number that represents the position of the column within the list
• Criteria is the range of cells that contains the conditions you specify
DSUM Function
Adds the numbers in a column of a list or database that match conditions you specify.
Syntax ::: DSUM (database,field,criteria)
• Database is the range of cells that makes up the list or database.
• Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as ”------” or as a number that represents the position of the column within the list
• Criteria is the range of cells that contains the conditions you specify
DMAX Function
Returns the largest number in a column of a list or database that matches conditions you specify.
Syntax ::: DMAX(database,field,criteria)
• Database is the range of cells that makes up the list or database.
• Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as “-----” or as a number that represents the position of the column within the list
• Criteria is the range of cells that contains the conditions you specify
DMIN Function
Returns the largest number in a column of a list or database that matches conditions you specify.
Syntax ::: DMIN(database,field,criteria)
• Database is the range of cells that makes up the list or database
• Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as “-----” or as a number that represents the position of the column within the list
• Criteria is the range of cells that contains the conditions you specify
Excel XP/2000: Charts
A chart is a graphic or visual representation of data
Multiple chart types can enhance information, adding visual appeal and making it easy to analyze data
Choosing a Chart Type
Graphic representation of data
Attractive, clear way to convey information
Select the type of chart that best presents your message
Add enhancements to better communicate your information
Data point - numeric value that describes a single item on a chart
Data series - group of related data points
Category label - describes a group of data points in a chart
You've probably heard the adage "a picture is worth a thousand words." Sometimes a chart or a graph can say more than a thousand numbers; Excel has the ability to create these visual aids. Before you start a chart, you must first input the data from which the chart will be drawn. Do this just as you would for creating any Excel worksheet. To create the chart, Excel will plot sets of data from your worksheet; these sets are called data series. (For more information about organizing your data in Excel, see [ http://help.unc.edu/?id=159 ] Excel: Database Management. In the worksheet example above, the columns for median house prices for the U.S. and for Chapel Hill are two data series which could be plotted in a chart. In this example, the data in the first column, the years between 1985 and 1993, will be category labelsin your chart.
What Type of Chart Should You Use?
Excel 2000 has 14 types of standard charts that you can select and use. The selection of chart type is usually driven by the data, although there are no hard and fast rules for determining the chart type you should use. Experiment! It is extremely easy to change your chart type selection. Use the one which displays your data and conveys your message in the simplest way possible. Below is a brief description of chart types and their general uses:
Bar
Shows the value of two or more items at the same point in time. Good for depicting dramatic difference between positive and negative values.
Column
Shows two or more values side by side.
Line
Illustrates trends over time.
Pie
Represents your data as a percentage of the total.
Column Charts
Used to show actual numbers rather than percentages
Displays data comparisons vertically in columns
The X or horizontal axis depicts categorical labels
The Y or vertical axis depicts numerical values
The plot area contains graphical representation of values in data series
The chart area contains entire chart and all of its elements
Bar Charts
Column charts with a horizontal orientation
Emphasizes the difference between items
Pie Charts
Effective way to display proportional relationships
The pie denotes the total amount
Each slice corresponds to its respective percentage of the total
Line Chart
Shows trends over a long period of time
A line is used to connect data points
How Excel Works with Charts
When you create a chart, Excel creates a link between the worksheet data and the chart. When you update or change the data, the chart is automatically adjusted. Also, when you create a chart you can choose from two different placement types: an embedded chart or a chart sheet. You can choose to insert the chart directly into a worksheet as an object (an embedded chart) or you can make it a new sheet (a chart sheet) in the workbook.
Stepping Through Charts with the Chart Wizard
Once you start the creation process, the ChartWizard will walk you through the following four steps. First, click on the Chart Wizard tool button on the right side of Formatting Toolbar, or choose Chartunder the Insertmenu option.
1. Selecting the Chart Type
The ChartWizard will now allow you to select from 14 different chart types. Select the chart type and sub-type you desire, and then choose the Nextbutton. Below the sub-type options is a box that tells you specifically what the chart type and sub-type are designed for. There is also a button for you to press that will show you a sample of your data in that specific chart type. You also have an option for you to create a custom type of chart. It is advised here that early on in your Excel charting experience you choose from the over 100 options offered to you from Excel before you go out on your own.
2. Defining the DataRange
In this step you will be asked to define the area or range of data to be included in the chart. Before beginning the Chart Wizard, click somewhere within the data set or select the area you want included in the chart. In the Data Range box, the area you selected (if you selected a range before beginning the Chart Wizard) or Excel’s estimate of your data area (if you clicked somewhere in your data) will appear highlighted. If the Data range specified is incorrect, simply select the correct range of data with your mouse; Excel will update the DataRange accordingly. (Note: include column headings in your data selection and Excel will recognize them as labels for each data series.) From here you can select the Nextbutton to move on to the next step.
The 2nd tab, Series, allows you to add a series of data to act as your X-axis. Select the range of data (in the example above, the years 1985-1993) in the same way you selected or modified the data range. The other options in this tab are not necessary if you specified the correct data ranges, names, and labels. However, if you wish to change any of these values you can do so here. See also the later section Modifying a Data Series.
3. Displaying the Sample Chart
The Chart Wizard will display a sample chart based upon the selections you made in the previous steps. If you don't like the chart type or format that you've selected, you may change these by using the Backbutton to return to the previous dialog boxes.
There are 6 options for your manipulation under step three:
Title: Allows you to give the chart and both axes titles.
Axes: Allows you to choose if you want values on both the X and Y axes.
Gridlines: Allows you to place gridlines (major and minor) on both the X and Y axes.
Legend: Allows you choose if and where a legend should be placed.
Data Labels: Allows you to put the Y values as labels above the X series (or vice versa) within the plot area.
Data Table: Allows you to affix a data table to the chart.
If at any time later you want to change the selections you made, just select the Chart Wizard tool button or Chartunder the Insertmenu option.
Chart Location
The final Chart Wizard step lets you decide if you wish to place your newly created chart on its own sheet or as an object in an existing sheet. If you choose As object in: Excel will place the chart in the current worksheet that contains your data range unless you specify a different worksheet to use. If you choose As new sheet, you can give the sheet a name in the blank provided. Excel will insert the chart sheet immediately below the worksheet containing the data range.
Parts of a Chart
Anything you see in an Excel chart can be modified. Listed below are four different methods for modifying items within a chart.
Double-click on the item in the chart you wish to modify. This will bring up a formatting dialog box for the item chosen and will allow you to make all modifications to the item at once.
Select the chart item with a single-click. You may then use the menus to make your changes.
Use the drop down menu from the chart toolbar to select a chart component and click on the tool button directly to the right of this box. This button is called the Format whatever(Whateverdepends on which chart object is chosen in the adjoining drop down menu.). This tool button looks very similar to the MS Properties tool button.
Point to the item you wish to modify and click the right mouse button to display the shortcut menu options for that item.
Remember that if you are working with an embedded chart, before you start making changes, you must first select the chart by clicking on it to edit it.
Changes to the Chart
If you wish to make a change to the entire chart, such as changing the fonts throughout it or copying the chart to another document, you need to select the chart area. You can select the chart area by clicking with the left mouse button anywhere outside of the axis area. Black handles will appear around the chart area. Double-clicking on the chart area will bring up the Format Chart Areadialog box where you can make changes to fonts and patterns.
Changes to the Plot Area
The plot areais the area inside and bounded by the axes. You may choose the plot area by clicking in any blank areas inside the boundaries. Black handles will appear on the area within the axis. You may also double click on the plot area to make changes to the borders, colors, or patterns in that area.
