The Use Of Microsoft Excel Computer Science Essay

The Use Of Microsoft Excel Computer Science Essay Microsoft Excel, popularly called as Excel is a versatile software application and is a part of the Microsoft Office Suite applications. It is a full-featured spreadsheet program that allows the user to perform a specific work related to decision making in the areas of business and economic applications. The specific work may be related to organizing statistical data, performing statistical and mathematical calculations, drawing inferences related to statistical applications, drawing graphs and charts based on the data availability, and also help in developing professional looking of reports. For better understanding, excel can be divided in three major heads: Parts Function Worksheets It allows the user to enter, calculate, manipulate and analyze data of both numerical and textual Charts and graphs Charts and Graphs are the pictorial representation of data series. Excel helps the users to represent the data to draw two-dimensional and three-dimensional column charts, pie-charts and many other types of charts and graphs. Databases The third important function of Excel is of managing data. For instance, once the user entered a set of data in the excel worksheet, it is having the facility to sort the data, if required, can search specific data, and can select that specific data that is needed by the user. STARTING EXCEL? There are a number of ways to start in Excel. The one possibility is to click on the START button, then select PROGRAMS from the start menu and finally, click on Microsoft Excel. The second possibility is to create a shortcut of the MicrosoftExcel icon on the desktop. When excel needs to open, double click on the icon directly. The third possibility is that if the user is already developed a spreadsheet file by assigning a name to that file, then the procedure is to double click on that file. For example, there is an excel file which is saved by a user in the file name namita.xls. Double clicking on the icon of this file will directly open this file. All the above three operations will display white grid in the screen with rows of menus and icons at the top of the screen. EXPLORING THE WINDOWS: When Excel is opened, mostly two types of windows appears on the screen namely the application window and the document window. The identification of the application windows is one that contains running program and they have menu bars. Where as, the document window appears inside the application window without the menu bars. FIGURE-FA1 Following are the brief descriptions on various uses of different bars. a. Title bar: It shows the name of the application, document, group, directory, or file. If more than one window is open at a time, the title bar for the active window (that window where the user is working at present) has a color or intensity different from the other title bars. b. Menu bar: This bar contains the available menus from which the user can choose command based on his/her need. c. Tool bar: The tool bar(s) provides the user with a quick method of working with various parts of the worksheet. Tool bars can be displayed on the screen based on the use of the user. They can be customized and also multiple tool bars can be displayed at the same time. d. Scroll bar: The scroll bar(s) enable the user while moving the spreadsheet when the entire spreadsheet is not coming within the display area in the computer screen. Here the user has to click the scroll arrows with the mouse to move through the spreadsheet or to see one line at a time. e. Window border: The window border is the outside edge of a window. The user can change the window size by lengthening or shortening the border on each side of the window. f. Commands: Commands are accessed through the menu bar of the file. The user has to select the menu item and then the commands associated with that menu will be displayed. The user has to click on that command which he/she required. g. Formula bar: It displays any formulas, address, or variables used to get the information in the cell. The pictorial description of an Excel workbook is derived in Figure-FA2 below. FIGURE-FA2: AN EXCEL WORKBOOK WORKING IN THE SPREADSHEET: A spreadsheet is a tabular form of values arranged in rows and columns. When an Excel file is clicked, a workbook is opened. The normal file type in Excel is referred as a workbook. Hence, spreadsheets are created on the worksheets. The first blank workbook displayed in the monitor of the computer is called Book1. Each workbook contains sheets which are called as worksheets when they contain in spreadsheet. But when include a graph is called as chartsheet. Workbooks are rectangular grid containing columns (vertical marked A, B, Cà ¢Ã¢â€š ¬Ã‚ ¦.Z, AA, AB,AC,à ¢Ã¢â€š ¬Ã‚ ¦AZ..) and rows (horizontal marked numeric values 1,2,3,4,à ¢Ã¢â€š ¬Ã‚ ¦.). Thus, a letter above each column identifies each column and a number on the left side of the grid identifies each row. Each worksheet in a workbook has 256 columns and 65,536 rows. Cells are the building blocks of plants and animals à ¢Ã¢â€š ¬Ã‚ ¦and spreadsheets too! Just like living cells do inside the living organisms, spreadsheet cells similarly can perform many functions. The cell is the basic unit of the worksheet and the data is entered in a cell. Each cell can be identified by a unique cell address otherwise called as cell references, which is the interaction of a column and a row. In order to identify a cell the column letter is specified first followed by the row number. For example, in the above figure-FA2 the highlighted single cell reference is B4 (as B stands for column and 4 stands for row). Similarly, multiple cells can also be identified by the user. In such a case, the user has to identify an active cell first, then hold the left mouse button, then drag to down and have to move the mouse right based on the desired numbers of cells. This facilitates all cells within that range will be highlighted. Data has to be entered in the active cell. The active cell in the workbook can be identified by a heavy border surrounding the cell. In addition, the active cell is listed in the reference area immediately above column A and row 1 referred as A1. NATURE OF DATA: Three types of data are generally entered in a workbook. They are: Nature of data Description Text To identify a set of work, some identification is needed. These identifications are expressed in qualitative descriptions. For example, titles of work like A regression analysis, Household budget analysis, etc. and for identifying the columns it also requires the textual variables like sales, productions, income, expenditure, profit, interest, etc. Numbers Numbers is the second items that can be entered in a workbook. Numbers consists of the digits zero through nine (i.e., 0, 1, 2, 3, à ¢Ã¢â€š ¬Ã‚ ¦, 9) and some special characteristics like +, -, ( ), /, . , $, %, #, etc. Formulas One of the greatest advantage of Excel application is the usefulness of formulas. A formula is a sequence of the values and the cells reference in a cell that produces a new value by using the existing values which is available with the user. Formula can be entered in uppercase or lowercase, and space can be added between the arithmetic operations to make the formulas easier to read. The detailed use with formulas is carried out in below derived sections. CREATING A WORKSHEET: A sample worksheet is created in Figure-FA3 below for the better understanding of the readers. First of all the user has to is to enter correct values in the required cells. Once the required information are entered in the cell the user can make the whole worksheet attractive by using some formatting options like bold text, different alignment and adding multiple colors to various texts to make the matter easily distinguishable and so on. Few easy steps of entering data are derived below. Click with mouse on the desired cell (in our example it is A2) Press the ENTER key from the key board to switch to next cell (or use Arrow key) or click on the next cell through mouse Type the column title to identify your variable (like A3 for the data of GDP, in B3 data on Agricultural sector, C3 Industrial sector etc.) By using step-2, enter the entire data in columns A, B, C, and D Then click in B2 to enter the next title of the data and enter data in each cell till it completed. Enter the data in other columns based on the availability of data. After entering the data, the entire spreadsheet will looks like as. FIGURE-FA3: A Sample Worksheet If the user has noticed any mistake while entering a data in a cell, then by using the back space key that value can be corrected. On the other hand, in case of mistakes in any cell where the data is already entered, make that cell as active cell. When the user clicks on a cell, the value entered in that cell is displayed in the formula bar of the worksheet. By using the backspace at the formula bar the mistake needs to be corrected. HOW FORMULAS WORK: The basic concern of this book is to narrate the detailed use of calculations by using the formulas and its execution procedure. Each formula has to be entered with the formula operator which is the equal sign i.e., =. When an equation sign is entered in a cell, Excel understands that whatever will be followed will be either a numeric value or may be an address of the cells which must contains a numeric value. For example, =C6 implies put in the value that is in the cell with the address of column C and row 6. For the better understanding of the readers, the process of working with formulas is discussed in three different heads below: Simple mathematical calculations Data analysis and Drawing charts 1. Simple Mathematical calculations: Data are the raw materials for spreadsheets. They generally are of constant values or simple values. Mathematical calculation by using the available data can be done by using formula (s). Hence, formulas are the command that instructs Excel to carry out a calculation. There are four independent basic mathematical operations and group operations of these four types can be easily carried out in Excel. The operators used for mathematical calculations are: Operator Pronunciation Task Operator Pronunciation Task + Plus sign Addition * Asterisk Multiplication - Minus sign Subtraction / Forward slash Division % Percent Per cent ^ Hat Exponentiation Absciant and Text Joining Addition: Any two or more values entered in separate cells can be added by using formulas. The first step is to start with =SUM(beginning cell address:ending cell address) and then press the ENTER key. In figure-FA4 below, a user wants to sum two values entered in cell A2 and cell B2 and wants to get the value at column F3. The formula has to be entered as written in the figure in F3 column and by pressing the ENTER key the value will be displayed as 28.66. In the second process the user can add different values entered in different cells by using the + sign. For example, =first cell address+second cell address+à ¢Ã¢â€š ¬Ã‚ ¦, then press ENTER key. Let suppose that the user wants to add only three values viz., A2, B3 and A4 and wants the result to be displayed in cell G3. The formula will be as displayed in the G3 column and after pressing ENTER the value will be displayed in that cell as 42.47. Excel makes it easy to quickly add a column or row of numbers. To illustrate the AutoSum feature, consider the above Figure-FA4. Let that the user wants to sum value entered in B column. Click on cell B6 which is the active cell, then click on the AutoSum botton displayed in the tool bar with a sum symbol like à ¢Ã‹â€ Ã¢â‚¬Ëœ and click on ENTER key for the value. In such a case, the sum function automatically selects the values of the entire column. Here the user is not required to enter the address of all the cells manually. The AutoSum value of the data entered in column B as displayed in B6 cell is 60.21. ii. Subtraction: Two or more values can be subtracted by using the - operator. The formula can be used as = first cell address-second cell address then press ENTER key. For example, one wants to subtract two values entered in cell B3 to A4 by using the values of above Figure-FA4 and wants to get the value in F5 cell. The formula will be as displayed in the F5 column and after pressing ENTER the value will be displayed in that cell as 5.26. FIGURE-FA4: VARIOUS MATHEMATICAL CALCULATIONS Multiplication: two or more values can be multiplied by using the multiply operator *. The formula can be used as =first cell address*second cell address*à ¢Ã¢â€š ¬Ã‚ ¦ and then press ENTER key. Let for example, one wants to multiply three values as entered in cell A1, B2 and A4 by using the values of above derived Figure-FA4 and wants to display the multiplication value in cell G4. The formula will be as displayed in the G4 column and after pressing ENTER the value will be displayed in G4 cell as 2533.073. Division: For the use of division in Excel, the / operator is used. The formula will be =first cell address/second cell address and then press ENTER key. For example, one wants to divide two values entered in cell B4 to A2 by using the values of above Figure-FA4 and wants to get the value in G5 cell. The formula will be as displayed in the G5 column and after pressing ENTER the value will be displayed in that cell as 1.378. Group expressions: In many calculations the situation demands for a grouped calculation. In such calculations parentheses (or brackets) are used to combine two independent functions into a group. The rule is that any part of the formulas expressed within the brackets is done first and the next operation is carried out then after. For example, the expression 8+3/2 will be 9.5 where as (8+3)/2 will be 5.5. Similarly, the value of 7+3/2 is 8.5 (not 5) where as (7+3)/2 is 5. Group calculations in Excel can be done as: =(first cell address then the operator(let it be +)second cell address, so on)the next operator (let it be *) by the constant value 2 (or if the value is entered in any cell then the cell address of that cell). By using the above Figure-FA4, some other possibilities are illustrated for the better understanding of the reader. Table-TA1: MATHEMATICAL FORMULA MODELS By using Formula in Excel Value Multiplication and Addition =(E2+E4)*2 then ENTER key =(E2+E4)*C3 then ENTER key 52 330.2 Multiplication and subtraction =(B3-E3)*5 then ENTER key 16.05 Multiplication and Division =(E4*A4)/3 then ENTER key 61.65 Addition and subtraction =(A3+A4+E3)-B4 then ENTER key 20.64 There are many other such possibilities of calculating values by using the group function. To illustrate the concept more clearly, an attempt has been made to calculate the PAY ROLL in Excel of few employees in the Box-A1 derived below. Box-A1: CALCULATION OF PAY ROLL OF M/S. DIKSHYA INDUSTRIES PVT LTD Mr. J.P.Voga, since his joining as the Managing Director of M/S. Dikshya Industries last month had passed various resolutions in his industry. One such resolution is that the salary of its 385 employees should transform to their accounts positively on the 1st day of each month. Mr. P.K. Jha, the Head, Accounts Department of that industry, is a retired government auditor who is well habituated with manual calculations. But after joining this industry, he is learning the use of Excel since last few months casually as he managed himself with this industry with his earlier expertise. But, Mr. Vogas order pressurized him a lot and he at the end finalized to develop the pay roll of each departments of the industry in Excel. For this he approached Mr. Vinay Jalota, his sub-ordinate at office who is at the post of Assistant Finance Officer at his industry. They decided to formulate separate pay roll for each department separately. Since, Mr. Jha is formulating the pay roll for the first time , they decided to start with the IT-Department of the company which is having 16 employees starting from the top management to the bottom employee. The steps followed are as follows: Step-1: In the first place it has been decided to divide the pay calculation in two separate heads like Basic and Allowances and the second phase is the deductions. The basic and allowances section consists all the gains to the employees i.e., credit amounts and the deduction section consists of debit amounts or looses to employ. Step-2: From the model pay roll attached in Figure-FA5 one can find that column A and Column B are the serial numbers of the employees and the name of the employees along with designation and basic pay respectively. Step-3: Column C is the number of days in that month that the employee had worked. The entry 31 in this column indicates that this month is having 31 days (this calculation is on May 2010). One can see that against each employees 31 is entered which implies all have worked in this month without any without pay. Step-4: Then in column D salary is calculated in respect of the basic of each employee separately. The formula =ROUND(VALUE(21400/B3*C6),0) is entered. In this formula, ROUND(VALUE is entered to round the fractions to nearest 0. The entry 21400 is the basic of the employee and it is first divided by B3 column i.e., to get per day salary of that month and then multiplies by C6 which is the number of days the employee worked. That is out of the 31 days in May how many days that particular employee worked. Once this formula is entered then copy the formula and paste it in the D7 column to get the salary of the next employee. But the basic of each employee differs. Hence, new basic needs to be edited in the copied formula in the formula bar. In this way in column D salary for each employee has been calculated. Step-5: Column E is the addition of the 50per cent of employees. The formula entered as =ROUND(VALUE(D6/50%),0). As one can see D6 column estimates the basic salary based on the number of days that employee worked on that month. A 50% of this amount has been calculated. Then copy this formula and paste it against each employee in column E. Since this formula is fixed for all, hence, there will be no change in the formula. But this formula doesnt fit for that employee who is in consolidated pay salary. Step-6: Column F is the added value of column D and column E. This amount will be the basic salary amount of the employees. The formula entered for calculation is =ROUND(VALUE(D6+E6),0). This formula is remained unchanged against all employees who are in the regular pay scale, of course except those who are in consolidated pay scale. Step-7: Based on the total amount calculated in column F, 64% is to be added as DA allowances. The formula is =ROUND(VALUE(F6*64%),0). This is applicable for all the employees except consolidated pay packagers. Step-8: Special amount per month has been fixed by the employer based on the basic of each employee. This amount as applicable has been added against each employees. The formula is = ROUND(VALUE(4600/B3*C6),0). This formula implies that per month special amount is Rs. 4600. this amount is first divided by total days in that month (B3) to get per day amount and then multiplied by numbers of days the employee worked in that month (C6). Step-9: HRA is at the 5% of the total salary calculated at column F. Hence, the formula is = ROUND(VALUE(F6*5%),0). But those employees who are residing in the quarters provided by the Industry are not entitled to get this amount. Hence, these columns for some employees are left vacant without any calculation. Step-10: Similarly Transportation, Traveling and other allowances, Medical, extra special allowances, Personal Pay and Telephone allowance as per applicable has been separately calculated by using the same logic i.e., at first divided by numbers of days in that month and then multiplies by numbers of days the employee worked. For example, the formula for Transportation is = ROUND(VALUE(400/B3*C6),0) i.e., Rs. 400 is the medical allowances. Similarly, Traveling is calculated by the formula = ROUND(VALUE(6000/B3*C6),0) and so on. Step-11: The Gross salary of each employee will be the addition of total amount of the above allowances. In column P row 6 i.e., in P6 to calculate the gross salary of the first employee the formula is = (F6+G6+H6+I6+J6+K6+L6+M6+N6+O6). This formula then copied and past in against all the employees in P column. The format in Excel sheet is shown in figure-FA5 below. Step-12: Then deductions are to be deducted from the gross salary of each employee. The items include TDS, EPF, License Fees, Maintenance, Electricity, and Advance. Among these items, the items like TDS and EPF are applicable for all the employees but the amount differs based on the gross salary. There fore, these amounts as per applicability have been simply entered in the respective column of each employee. License fee is applicable to those who are the residents of industry provided quarters. The formula is = ROUND(VALUE(F7+G7*10%),0). This implies that, license fee is the 10% of total column plus DA i.e., F7+G7. The readers should not be confused with the entry of row 7 in the formula because employee entered in the model pay roll is not a resident of industry provided quarter. Similarly, the residents of industry quarters have to pay Maintenance and Electricity. Electricity bill is not fixed for each employee rather depends on their consumption, thus fluctuates from employ to em ploy. Hence, are entered directly against the employee. Where as maintenance is the 10% of the license fee calculated in column S. The formula is = ROUND(VALUE(S7*10%),0). FIGURE-FA5: PAY ROLL FORMAT Step-13: The net payable or net salary of each employee is calculated as the total gross salary (column P) minus the deductions. The formula for the first employee is =P6-Q6-R6-S6-T6-U6-V6. Simply copy the formula and pasted it against each employee. The deduction portion of the model pay roll is shown in the Figure-FA6 below. Step-14:.Then by setting the pay roll worksheet by using the page setup options, the page can be printed by using the print option. FIGURE-FA6: PAY ROLL FORMAT (DECUCTIONS) This process when over surprised Mr. Jha. He could not believe him self that a person who is not touched the key board since last more than 30 years successfully developing a complete format of pay roll in Excel. This task of Mr. Jha increased his self confidence. Data analysis: Excel can perform a very large number of functions, which are formulas that perform a specific calculation beyond simple mathematical calculations. When the FUNCTION option in the INSERT menu is selected, it will display a large number menu of functions. This task of the Excel is more useful by the researchers for the simple statistical and econometric analysis of data. Excels FUNCTION option is very simple and hence, user friendly than other available statistical packages like SPSS, LIMDEP, STATA, STASTICA, GRITL, E-VIEWS, etc. But one think that the users should keep in mind that Excels data analysis wizard is not helpful for advanced data analysis purposes. Rather it is best suit for the beginners, particularly for the students and entry level researchers. Following are the steps of using the FUNCTION button in Excel worksheet for the purpose of drawing some statistical inferences. The first step is to create a data base in the worksheet containing the data series to be calculated. Then, click on INSERT button on menu bar and choose FUNCTION button. A small Excel window will appear showing the dialogue box. The user has to select the type of analysis it requires. The window is sheen as displayed in Fgure-FA7 below. The best option is to go for All type which will display all the options available under this function. FIGURE-FA7: INSERT FUNCTION USE Then specify the function (i.e., whether analysis is a standard deviation or correlation analysis, and so on.) Input the required cell address as per the need of the function and click OK. The final result will be displayed by the Excel application. The above steps can be well understood with a step wise calculation of CORRELATION between two variables as derived in Box-A2 below. Readers who are interested on the subject of correlation are advised to refer chapter-14 for a detailed analysis. Box-A2: Indian Textile Industry: Domestic Purchase is the Key The Indian textile industry faces many challenges in its efforts to become a major player in the global arena. Though it has many positive factors such as a multi-fibre raw material base, low cost labour base and big domestic market, it has been severely affected by insufficient power supply, steep increase in minimum support price for cotton and announcement of minimum wages on one side and stiff competition from other textile producing countries like China, Pakistan and Bangladesh. However, among the variables that positively influence the growth of the industry, the share of domestic purchase plays a very crucial role. For this we are having around eight years data on the total production of Indian textile sector and total domestic purchase by the Indian households. The detailed data set is shown in the Table-TA2 below. TABLE-TA2: TRENDS IN DOMESTIC PURCHASE (Quantity measured in million meters) Year Total Production Domestic Purchase 2000-01 40333 17969 2001-02 41390 19154 2002-03 41311 19860 2003-04 41721 20957 2004-05 44685 21784 2005-06 48808 22859 2006-07 52665 24030 2008-09 55023 25493 Source: Compiled from industry data base. In order to know whether there really exists strong relationship between the two variable i.e., between the total production of textile and domestic purchase, the correlation between the two variables are calculated with the help of Excel software and the steps followed in the process of calculation are outlined below: Step-1: The above data set is entered in the Excel spreadsheet and the file is saved as Correlation Analysis.xls. Step-2: The FUNCTION button from INSERT menu has been selected. Step-3: The window is now asking to specify the required function. In the Data type option All has been selected. Then CORREL option for correlation analysis is to be selected. The window appears as: FIGURE-FA8: INSERT FUNCTION WITH CORRELATION OPTION Step-4: When OK button is clicked, the next window will flash by asking for specifying the data range. In Excels language it is referred as Array1 and Array 2. Array 1 asks for first data series and Array 2 asks for second data series. In our above data set, year is only for the purpose of interpretation. Hence, while calculating the correlation value the column containing the Year data is not considered. After specifying the two data cell range, the window will looks like as FIGURE-FA9: FUNCTION ARGUMENTS IN CORRELATION ANALYSIS Step-5: By a click on OK button shows the value of correlation between the two variables which is measured by coefficient of correlation as 0.9559. The simple interpretation of the value says that both the variables considered under study are related with each other by up to 95 per cent. In other wards, there are other 5 per cent of variables, except domestic purchase contributes to total production of textile in India. Hence, the policy makers are to be serious enough with this result and they should pave necessary step to strength the domestic market for further growth of textile industry. Using Analysis tool pack Beside the FUNCTION option, data can also be analyzed by using the Analysis Toolpack and Analysis toolpack-VBA options. In some computers, these packs are not loaded automatically. To load this option in the computer click on TOOLS in menu bar and click on Add-ins. A small window asking the addition will display in the screen. Tick on the two options i.e., on Analysis Toolpack and Analysis toolpack-VBA with mouse and then click OK. The window will be as: FIGURE-FA10: INITIALIZATION OF ANALYSIS OF TOOL PACK For using the analysis tool pack, in the first place enter data in the Excel sheet and save the sheet by assigning a name. Then click on TOOLS and select DATA ANALYSIS option. A small window will appear showing numbers of statistical options. Specify on the desired one based on the need through mouse. From the below derived Excel window one can see that the regression option has been selected. FIGURE-FA11: DATA ANALYSIS OPTION FOR REGRESSION An OK click in the above process opens another window asking for specifying the cell address of the data series based on the desired statistical analysis. The user has to specify the cell address where as some functions need adjustments on basis of statistical properties and nature of the study. In order to explain the above steps more clear a Box-A3 has been developed below where by taking the sectoral growth rate, a multiple regression result has been calculated in Excel sheet. Box-A3: Contributions of regional sources in growth acceleration A nation is an aggregation of its regions. Thus, if we add the incomes of all states (regions), we should get the national income. This premise does not strictly hold in India given the problems in measurement and the data availability issues for more states and union territories. However, such problems are not considered serious in terms of magnitude and are, therefore, ignored in India [Dholakia, 2009]. In order to derive the contribution of states in the national growth acceleration the growth rate of the states during 1991-92 to 2003-04 for all the three sectors and the growth rate of the economy as a whole has been considered. The relevant concept of income is the gross state domestic product (GSDP) originating within the geographical boundary of the state and is measured at constant (1993-94) prices. The raw data is then converted into natural logarithms by taking ln value to make the data smooth one. A multiple regression model has been developed by considering the lnGSDP as dependent variable (Y) and other three variables i.e., contribution of primary sector (referred as lnPRI), contribution of secondary sector (referred as lnSECO) and the contribution of Tertiary sector (referred as lnTER) are considered as the dependent variable. Step-1: Enter the data in Excel sheet. Step-2: Click on TOOLS and select DATA ANALYSIS option Step-3: Select Regression option and click on OK. Step-4: The regression is now asking to specify the cell address of Y value and the cell add