The Use Of Microsoft Excel Computer Science Essay

The Use Of Microsoft pass Computer Science EssayMicrosoft Excel, popularly c onlyed as Excel is a versatile package finish and is a lot of the Microsoft Office Suite applications. It is a full-featured spread canvas program that allows the substance ab giver to perform a ad hoc treat related to decision making in the atomic number 18as of business and economic applications. The specific work may be related to organizing statistical information, performing statistical and mathematical calculations, d fonding inferences related to statistical applications, drawing graphical records and charts based on the entropy availability, and withal help in readying professional looking of reports. For better understanding, go past hindquarters be interpolate integrity in threesome major headsPartsFunctionWorksheetsIt allows the substance ab rehearser to enter, calculate, manipulate and analyze selective information of both numerical and textualCharts and graphsCharts and Graphs be the pictorial prototype of selective information series. Excel helps the users to represent the entropy to draw twain-dimensional and three-dimensional chromatography column charts, pie-charts and m both separate types of charts and graphs.DatabasesThe third important function of Excel is of managing data. For instance, erst 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 circuit card and finally, click on Microsoft Excel. The second possibility is to create a shortcut of the MicrosoftExcel icon on the desk take place. When excel needs to open, double click on the icon directly. The third possibility is that if the user is already genuine a spreadsheet excite by assigning a name to that file, then the procedure is to double click on that file. For utilization, 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 testament directly open this file. All the above three operations forget display washrag grid in the screen with rows of menus and icons at the top of the screen.EXPLORING THE WINDOWSWhen Excel is opened, mostly two types of windows appears on the screen namely the application window and the document window. The appellation of the application windows is one that contains running program and they have menu parrys. Where as, the document window appears inside the application window without the menu excludes.FIGURE-FA1Following are the instruct descriptions on various uses of different bars.a. Title barIt 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 wher e the user is working at present) has a color or intensity different from the other title bars.b. Menu barThis bar contains the available menus from which the user can choose command based on his/her need.c. Tool barThe tool bar(s) provides the user with a quick regularity 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. Sc verify barThe scroll bar(s) enable the user while moving the spreadsheet when the good spreadsheet is non coming within the display range in the computer screen. Here the user has to click the scroll arrows with the mouse to move through the spreadsheet or to happen one line at a time.e. Window b fix upThe window border is the outside edge of a window. The user can change the window size by lengthening or shortening the border on for individually one side of the window.f. CommandsCommands 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 go forth be displayed. The user has to click on that command which he/she required.g. Formula barIt displays any conventionalitys, attachress, or variables used to get the information in the kioskular phone.The pictorial description of an Excel workbook is derived in Figure-FA2 below.FIGURE-FA2 AN EXCEL WORKBOOKWORKING IN THE SPREADSHEETA spreadsheet is a tabular form of fosters 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 off 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 marke d A, B, C.Z, AA, AB,AC,AZ..) and rows (horizontal marked numeric esteems 1,2,3,4,.). Thus, a letter above each column identifies each column and a number on the left-hand(a) 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 practiced like living prison 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 conferress 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 refine based on the desired rime of cells. This facilitates all cells within that range entrust 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 DATAThree types of data are generally entered in a workbook. They areNature of dataDescriptionTextTo 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.NumbersNumbers 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.FormulasOne of the greatest advantage of Excel application is the usefulness of shapes. A blueprint is a sequence of the values and the cells reference in a cell that produces a new value by development 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 cook up the formulas easier to read. The detailed use with formulas is carried out in below derived sections.CREATING A WORKSHEETA 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 development some formatting plectrums like bold text, differe nt alignment and adding multiple colors to various texts to make the be easily distinguishable and so on. Few easy step of entering data are derived below.Click with mouse on the desired cell (in our example it is A2)Press the image key from the key board to switch to beside cell (or use Arrow key) or click on the next cell through mouseType 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 utilize step-2, enter the entire data in columns A, B, C, and D hence 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 leave alone looks like as.FIGURE-FA3 A Sample WorksheetIf 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 WORKThe 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 entrust be followed ordain be either a numeric value or may be an shout 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 belowSimple mathematical calculationsData analysis andDrawing charts1. Simple Mathematical calculationsData are the raw materials for spreadsheets. They generally are of constant values or simple values. Mathematical calculation by using the available data can be make 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 areOperator pronunciationTaskOperatorPronunciationTask+Plus sign increase*AsteriskMultiplication-Minus signSubtraction/Forward slashDivision%PercentPer cent lidExponentiationAbsciant andText JoiningAddition Any two or more values entered in violate cells can be added by using formulas. The first step is to start with =SUM(beginning cell addressending cell address) and then press the set down 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 colu mn F3. The formula has to be entered as written in the figure in F3 column and by pressing the pull in 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 scratch key. Let suppose that the user wants to add only three values viz., A2, B3 and A4 and wants the proceeds to be displayed in cell G3. The formula will be as displayed in the G3 column and after pressing degrade 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 represent 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 va lues 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 numeral CALCULATIONSMultiplication 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 d isplay 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 numeric FORMULA MODELSBy usingFormula in ExcelValueMultiplication and Addition=(E2+E4)*2 then ENTER key=(E2+E4)*C3 then ENTER key52330.2Multiplication and subtraction=(B3-E3)*5 then ENTER key16.05Multiplication and Division=(E4*A4)/3 then ENTER key61.65Addition and subtraction=(A3+A4+E3)-B4 then ENTER key20.64There 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 bel ow.Box-A1 CALCULATION OF PAY ROLL OF M/S. DIKSHYA INDUSTRIES PVT LTDMr. 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 twenty-four hour period 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 remunerate 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 refractory 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 followsStep-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 agnomen and basic pay respectively.Step-3 Column C is the number of eld in that month that the employee had worked. The entry 31 in this column indicates that this month is having 31 days (this calculation i s 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 deliberate 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 spread head 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 metric.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 omit those who are in consolidated pay scale.Step-7 ground 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 appli cability 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 aeronaut is not fixed for each employee rather depends on their consumption, thus fluctuates from employ to employ. 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 FORMATStep-13 The net payable or net salary of each employee is calculated as the total gross salary (column P) minus the deductions. The f ormula 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 affected the key board since last more than 30 years successfully developing a complete format of pay roll in Excel. This labor of Mr. Jha increased his self confidence.Data analysisExcel can perform a very large number of functions, which are formulas that perform a specific calculation beyond simple mathematical calculations. When the power 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 a nalysis of data. Excels FUNCTION option is very simple and hence, user companionable 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-F A7 INSERT FUNCTION USEThen 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 impression will be displayed by the Excel application.The above steps can be well understood with a step wise calculation of correlational statistics 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 KeyThe 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 interior(prenominal) market, it has been severely affected by insufficient power supply, steep increase in minimum support price for cotton and announcement of minimum recompense 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 municipal 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 arrive ProductionDomestic Purchase2000-0140333179692001-0241390191542002-0341311198602003-0441721209572004-0544685217842005-0648808228592006-0752665240302008-095502325493Source 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 s teps followed in the process of calculation are outlined belowStep-1 The above data set is entered in the Excel spreadsheet and the file is saved as coefficient of 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 asFIGURE-FA8 INSERT FUNCTION WITH CORRELATION OPTIONStep-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 asFIGURE-FA9 FUNCTION ARGUMENTS IN CORRELATION analytic thinkingStep-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 packBeside 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 asFIGURE-FA10 INITIALIZATION OF ANALYSIS OF TOOL PACKFor 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 REGRESSIONAn OK click in the above process opens other 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 positive below wh ere by taking the sectoral growth rate, a multiple regression result has been calculated in Excel sheet.Box-A3 Contributions of regional sources in growth quickeningA 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 beat 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 character 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 con verted 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 optionStep-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