Computer Application in Business Unit 4 Creating Business Spreadsheet, College and University Answer Bank for B.com, M.Com B.sc, and Post Graduate Notes and Guide Available here, Computer Application in Business Unit 4 Creating Business Spreadsheet to each Unit are provided in the list of UG-CBCS Central University & State University Syllabus so that you can easily browse through different College and University Guide and Notes here. Computer Application in Business Unit 4 Creating Business Spreadsheet can be of great value to excel in the examination.
Computer Application in Business Unit 4 Creating Business Spreadsheet
Computer Application in Business Unit 4 Creating Business Spreadsheet Notes cover all the exercise questions in UGC Syllabus. Computer Application in Business Unit 4 Creating Business Spreadsheet Solutions provided here ensures a smooth and easy understanding of all the concepts. Understand the concepts behind every Unit and score well in the board exams.
Creating Business Spreadsheet
COMPUTER APPLICATION IN BUSINESS
| VERY SHORT TYPES QUESTION & ANSWERS |
1. How to Calculate payment for a loan?
Ans: To Calculate payment for a loan:
Formula: = PMT (rate,periods,-amount)
To calculate a loan payment amount, given an interest rate, the loan term, and the loan amount, you can use the PMT function. In the example shown, the formula in C10 is:
= PMT(C6/12,C7, – C5)
Syntax for loan calculation formula:
Rate – The interest rate per period.
Per – The period for which the interest rate is calculated.
Nper – The total number of payments.
Pv – The present value, the total amount that a series of future payments is worth now.
Type – The timing of the payment, either at the beginning or end of the period. Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period, and the number 1 represents payment at the beginning of the period. The default (empty argument) is 0. The calculation is at the end of the period.
2. Ratio analysis in excel.
Ans: Ratio Analysis: Ratio analysis is a way of comparing various aspects of a business’s finances as a way of testing such things as a business’s efficiency, liquidity, profitability and solvency.
Current Ratio = Current Assets/Current Liabilities.
Current Ratio = $162,819 million/$105,718 million
Current Ratio = 1.54x
Quick Ratio = (Cash & Cash Equivalents + Accounts Receivables) / Current Liabilities
Quick Ratio = ($48,844 million+$22,926 million)/$105,718 million
Quick Ratio = 0.68x
Cash Ratio = Cash & Cash Equivalents/ Current Liabilities
Cash Ratio= $48,844 million/$105,718 million
Cash Ratio=0.46x
3. How to create payroll in excel?
Ans: Creating a Payroll in Excel: In this newly created file where all your employee payroll information would be stored, therefore, create some column with names which can hold the values for the certain parameters/ variables. Enter the column names in the following hierarchy.
(a) Employee Name (column A): Contains your employee name.
(b) Pay/Hour (column B): Contains per hour pay rate to the employee without any currency symbol.
(c) Total Hours Worked (column C): Contains total hours worked by an employee in a day.
(d) Overtime/Hour (column D): Overtime rate per hour without any currency symbol.
(e) Total Overtime Hours (Column E): Number of hours employees overtime in a day.
(f) Gross Pay (column F): Payable amount to the employee without any deductibles.
(g) Income Tax (column G): Tax payable on Gross Pay.
(h) Other Deductibles (If Any) (column H): Deductibles other than Income Tax.
(i) Net Pay (column I): Payment the employee will receive in hand after all the deductions.
Step 1: Add the details column-wise like Employee Name in column A, a number of hours worked and hourly paying rate, etc. I will say input the fields with no formula (From column A to column E). See the screenshot below for better understanding.
In this example, if you can see the Total Hours Worked and Total Overtime Hours are considered on a monthly basis (because we pay the employee on a monthly basis, right?). Therefore 160 means total hours worked during the month. Same is the case with total hours overtimed.
Formulate Gross Pay. Gross Pay is nothing but the sum of the product of Pay/Hour, Total Worked Hours and Overtime/Hour, Total Overtime Hours.
(Pay/Hour x Total Hours Worked) + (Overtime/Hour x Total Overtime Hours). In the payroll sheet, it can be formulated under cell F4 as =(B2*C2)+(D2*E2). It’s a simple formula anyway. However, you can see the screenshot below for a better understanding.
After using formula the answer is shown below.
Drag the same formula cell F3 to cell F6.
Step 2: In this newly created file where all your employee payroll information worked.
Step 3: Formulate Gross Pay. Gross Pay is nothing but the sum of the product of Pay/Hour, Total Worked Hours and Overtime/Hour, Total Overtime Hours. (Pay/Hour x Total Hours Worked) + (Overtime/Hour x Total Overtime Hours). In the payroll sheet, it can be formulated under cell F4 as=(B2*C2)+(D2*E2). It’s a simple formula anyway. However, you can see the screenshot below for a better understanding.
After using formula the answer is shown below.
Drag the same formula cell F3 to cell F6.
To calculate income tax we need to check how much percentage of tax your employee pays on the total gross pay. Income Tax is always calculated on Gross Pay. In this case, we will consider 15% of Income-tax on all the Gross Pay. The formula for Income Tax, therefore, becomes as -0.15 x Gross Pay.
Which in terms of excel payroll sheet can be formulated under cell G2 as =0.15 x F2 (Column F contains Gross Pay amount).
Drag the same formula in cell G3 to G6.
We have to mention other deductibles if any for a particular employee. These deductibles may contain the premium of health/life insurance, professional taxes, EMI amount if any loan is taken from an organization, etc. add these amount values under column H. If there is no other deductible for a particular employee, you can set the value under column H for that employee to zero.
Step 4 Now, finally we come towards Net Pay. Net Pay is nothing but the amount that gets credited into your employee’s bank account after all the deductions from Gross Pay. Therefore, in this case, we will deduct (subtract) Income Tax (column G) and Other Deductibles (Column H) which can be formulated under cell 12 as =F2-(G2+H2). Here, Income Tax and Other Deductibles are summed up and then subtracted from Gross Pay. See the screenshot below for better understanding.
Drag the same formula in cell 13 to cell 16.
This is how we create the payroll under excel to manage the things on our own.
4. How to calculate capital business excel?
Ans: Capital budgeting makes decisions about the long-term investment of a company’s capital into operations. Planning the eventual returns on investments in machinery, real estate and new technology are all examples of capital budgeting.
Calculating the NPV and IRR of a Project Investment: The CapitalBudgeting – ProjectCashFlow – NPV worksheet in the Capital Budgeting spreadsheet allows you to key in the assumptions and estimates of a project cash flow and will calculate the Net Present Value and Internal Rate of Return of the investment.
Assumptions: This worksheet performs capital budgeting analysis by making three basic assumptions. The assumptions are the Discount Rate to use in the investment project, the company’s Tax Rate and the estimated percentage of Net Working Capital over Sales.
The Net income of the project is calculated by using the following formula: Net income = Earnings before Interest & Taxes (EBIT) – Taxes
EBIT = Net Sales – Total Variable Costs – Total Fixed Costs – Depreciation.
Projected Cash Flows: This section is where the estimated cash flows are calculated.
The Operating cash flow is defined as follows: Operating cash flow = EBIT + Depreaciation + Taxes
5. How to calculate depreciation?
Ans: Depreciation is calculated using the formula given below Depreciation = (Asset Cost – Residual Value) / Useful Life of the Asset.
Depreciation = ($3.50 million – $0.20 million)/10
Depreciation = $330,000 in year 1 and 2
6. How to calculate frequency distribution on excel?
Ans: Calculate Frequency Distribution in Excel:
Frequency Distribution: A Frequency Distribution is a summary of how often each value occurs by grouping values together.
There are multiple ways to calculate frequency distribution (table) with Excel.
(a) With COUNTIFS Function.
(b) With FREQUENCY Function.
Calculate Frequency Distribution in Excel:
(a) Enter the above data in cells B3:C15. The first row of table has headers.
The same data entered into a sheet in excel appears as follows:
(b) Select the desired class intervals.
(c) Create a table with the columns – Class intervals, Lower limit, Upper limit and Frequency.
How to calculate lower and upper limits using excel formula –
Suppose class interval column starts from cell E5 (excluding header).
Lower limit: Enter the following formula in cell F5 and paste it down till the last row of the table.
= MID(E5,1,FIND(“-“,E5,1)-1)
Upper limit: Enter the following formula in cell G5 and paste it down till the last row of the table.
= MID(E5,FIND(“-“,E5,1)+1,2)
Frequency Distribution with COUNTIFS Function: To calculate the last column of the above table, enter the following formula in cell H5 and paste it down till the last row of the table.
=COUNTIFS($C$4:$C$15,”>=”&F5,$C$4:$C$15,”<=”&G5)
7. How to find out Regression and corelation in excel?
Ans: Find out whether a correlation between body weight and egg weight exists in layers. In a sample of 10 layers following body weights (in kg) were measured: 2.2, 1.8, 2.1, 1.7, 2.4, 2.0, 2.0, 1.9, 2.3, 1.9.
In these layers following egg weights (in g) were measured (average value from 10 eggs): 41, 36, 40, 36, 42, 39, 40, 37, 41, 38.
Calculate basic statistical parameters (AVG, SD) in each sample, calculate correlation coefficient and figure a chart of linear regression (with trendline equation) of the relation between these sample data.
Type data into the table:
(a) B12:B13 and C12:C13 cells: Calculate basic statistical parameters (AVG, SD)
(b) Calculation of a correlation coefficient: B14 cell: Insert Function(fx) – Statistical – CORREL (in Array1 mark B2:B11 cells, in Array2 mark C2:C11 cells) (it’s good to merge B14 and C14 cells to display the result in a better way – correl.coef. belongs to both columns – describes power of their relation).
(c) Chart: Mark B2:C11cells, then menu Insert – Scatter – Scatter with only Markers.
(d) Through in the corner of the marked chart: add Chart Title, Axis Titles (retype appropriate text – beware of which data are on which axis!). It is possible to change colour, type and size of points (with right button of the mouse on some point – menu Format Data Series).
(e) Right button (mouse) on some point in chart figured: menu Add Trendline – Type: linear, tick Display equation on chart. It is possible to change Line Color, Type and Width (with right button of the mouse on the trendline-menu Format Trendline).
8. Explain in detail the concept an creation of Pivot table.
Ans: PivotTable is a very powerful analysis tool built into MS-EXCEL. It helps in analyzing & summarizing large collections of data. Such data can be derived from various sources. The most common choice is to however create a pivot table from an Excel list. For the purpose of understanding, the following Excel list consisting of time sheet records of a professional firm is analysed using PivotTables.
Creating a Pivot Table: For creating a PivotTable, choose the PivotTable command from the Data menu. The PivotTable wizard appears and takes you through the process of creating a Pivot Table.
Step 1: Specify the Data Source: Here we specify either an existing Excel list or an external source of data. We also specify whether we want to create a PivotTable or a PivotChart report. The default choices are generally Excel list and PivotTable. Choose Next to specify the data range.
Step 2: Define the Data Range: Ensure that the correct range of cells has been included. Click the next button to specify the location of the PivotTable.
Step 3: Choose the location of the PivotTable. Select New worksheet and click Finish.
Layout of the PivotTable: At this stage, it is necessary to understand certain terms relevant for the purposes of PivotTables. For effective analysis, PivotTable uses the concepts of row & column fields for summarizing and grouping data. The page field could be used to filter out the data on a particular item. The data field contains the summary information. To create the analysis, drag the desired item from the PivotTable Field list and drop it in the relevant section of the main table.
Some Examples: To obtain a report containing the gross amounts billed for a particular client, drag the client field as row field and “Bill Amount” field as the data field. The report is ready! On analyzing the report, I find that the firm earns maximum revenues from Fast Limited. Similarly, I could analyse the gross revenues earned by a particular employee (remove the client field by dragging it back to the pivottable field list and drag the “resource name” field list to the row field area) or also get a report of segment-wise revenue (drag the “work done” field to the row field area).
It is also possible to drill down to the details of the above report by clicking on a particular client and choosing the field on which detail is required (say resource name) Further levels of data can be selected by clicking successive items. Similarly, it is also possible to expand or collapse the levels of data displayed either by doubleclicking or by choosing the relevant command from the PivotTable Toolbar. To view the complete details of a particular summarized amount, click on that amount to.
It is also possible to drill down to the details of the above report by clicking on a particular client and choosing the field on which detail is required (say resource name). Further levels of data can be selected by clicking successive items. Similarly, it is also possible to expand or collapse the levels of data displayed either by doubleclicking or by choosing the relevant command from the PivotTable Toolbar. To view the complete details of a particular summarized amount, click on that amount to create a new sheet which displays only the records that were included in that summarized amount.
Now suppose I want a cross dimensional analysis of data in terms of client-wise revenue as well as segment-wise revenue. I position the client as row field and “work done” as the column field. The data item of course continues to be gross amount. The resultant report looks as under:
Now I divert my attention from the revenues generated to the time spent. I choose “resource name” as the row field, “work done” as the column field and “time hours” as the data field. I double click on the data field in the pivottable. A new screen pops up to provide various options for displaying the data.
The first list in the said screen (Summarize by) provides for alternative ways of summarizing information. While the default is the sum of the data item, it is also possible to choose either the average, maximum, minimum, number of occurrences, etc. The second list in the said screen (Show data as) allows different methods of displaying the summary data. I choose “% of row” and click OK to change the format of the chart. The resulting chart helps me analyse the work specialities of specific employees.
Similarly, if I choose “% of column” Format for displaying data, I can analyse whether the organization is dependent on a particular employee for a particular segment of work.
I could now replace the “work done” column field for the “client” column field and goahead to analyse the client-employee affiliations if any.
The possibilities are endless and the reports are displayed at the flash of a moment. The only requirement for an effective analysis therefore is the quality of the data list maintained.
9. Explain the Data analysis tools of Ms-Excel.
Ans: Data Tables One may also consider the use of MS-EXCEL to perform what is commonly known as a what-if analysis. What if analysis examines how sensitively a situation will react to changes in factors that influence the situation. Let us take the example of a person availing of a housing loan. The EMI payable would depend both on the interest rate and the period of repayment (assuming that the loan amount is ascertained). Let us calculate the EMI for a housing loan of Rs. 7.5 lakhs taken @ 11% per annum for a period of 15 years. I enter each of these basic data in separate cells Al = 750000, A2 = 11%, A3 = 15 I enter the formula for calculation of EMI in Cell A4 as =PMT(A2/12,A3 x 12,-A1) {Note that the EMI calculations in Excel are denominated in months and also that reverse cash flows are indicated by negative numbers). Now the person might be interested in knowing the effect on the EMI for each change of 0.25% in the interest rate. Such a sensitivity is known as what- if analysis. In Excel, what-if analysis is performed through the use of data tables. A data table is a range of cells that shows the results of substituting different values in one or more formulas. The Data Table command performs calculations using a series of different input values and hence is an efficient alternative to creating formulas in individual cells and editing or copying the formula when a value changes. To initiate the process, choose the Table Command from the Data Menu.
A data-table can be either a one-variable table or a two variable table. In a one-variable data table, the alternative values are entered as row labels and the resulting formulae are specified as column labels. As such, it is possible to specify multiple formulae. Such a table is known as column oriented data table. In case of a two variable table, the resulting formula is entered at the intersection of the row and column labels (each of which contains alternative values). As such, in case of two variable data table, one can specify just one formula.
(a) Useful for what-if analysis.
(b) Type the formulae in the columns & possible range of values in the Rows.
(c) Select the range and Choose Data → Table.
(d) Select the variable cell as the column input.
(e) For two variable data table, specify formula at the intersection of the row and column.
Scenarios: The limitations of the data tables feature are but obvious! Time to introduce the Scenarios feature. This feature enables you to analyse your data to see how changing one or more values in a worksheet affects the other cells in the worksheet. Effectively using this feature is a two step process: one is to create a scenario and the other step is to view the results as a summary.
The entire process is simple and self explanatory:
(i) What-if analysis on multiple criteria (pre-defined situations).
(ii) Choose Tools → Scenario.
(a) Define a Scenario by “ADD”
(b) Alter a Scenario by “DELETE” / “EDIT”
(c) Concept of Changing Cells & Resultant Cells.
(iii) View Results.
(iv) Scenario Summaries.
Goal Seek: Goal Seek is another useful feature which can be used to achieve a certain value in a cell that contains a formula. The way this is done is to adjust the value of another cell that has a direct effect on the original cell. After all, the valuation of closing stock has a direct effect on the net profit!
(a) Concept of working reverse way.
(b) Select Tools → Goal Seek.
Solver: Again, Goal Seek is an elementary function which may at times supply absurd results. For example, it may let stocks be over-drawn. For a more sophisticated approach, choose the Solver Add-in (cozily sitting on the Tools menu) which not only allows you to specify constraints (so that stocks don’t get overdrawn or sales don’t exceed the production capacity) but also permits multiple varying cells. The reports generated by the add-in can help solve simple problems in linear programming.
(a) Select Tools → Solver.
(b) Concept of Target Cells, Changing Cells & Constraints.
(c) Utility of Answer Reports & Sensitivity Reports.
(d) Saving the solution as a scenario.
10. Explain the procedure of copying and moving in MS-Excel.
Ans: One of the most common utilities of any Windows program is the cut/paste and the copy/paste features. Needless to mention, the same are available in MS-EXCEL also. To summarise, select a range of cells (it can be a set of non contiguous cells also), press Ctrl+C to activate the Copy command from the keyboard after selecting the cells. Ctrl+X is the keyboard command to cut the cells. You paste the cells from the keyboard by pressing Ctrl+V. One can copy/move data across cells by using the mouse and the drag-and-drop technique. Simply drag-drop would imply a move, whereas holding the Control key while dragging-dropping will imply a copy.
While copying values across poses no problems, one has to be careful while copying formulae. When a formula containing a cell reference is entered into a cell, Excel keeps track of that formula in two ways: one is relative referencing whereby the relative position of the addressed cell and not the cell position itself is stored in the formula (this is the default and is quite handy most of the times). For example, if A3 contains the formula +A1+A2 and the same is copied to B3, the formula in B3 changes to +B1+B2. While this is mostly beneficial, in some cases, one may want to freeze the cell position referred and may not want the same to be changed in case of subsequent copying of data. For this purpose, one needs to use the absolute referencing. Here, the exact cell position of the addressed cell is stored in the formula. The absolute referencing is identified by the use of the $ sign in the cell address. Thus $A$7 implies that the cell address A7 is absolutely stored in the formula. One may also consider the use of mixed referencing whereby either the row or the column remains constant. To make only the column or row portion of a cell address absolute, press F4 key. Each time you press the F4 key, the $ moves to a different co-ordinate of the cell address. To summarise this issue, when you copy a formula, relative addressing will change the cells that are referenced by the formula. If you need any of the cells to remain constant, make sure that absolute referencing has been added to the original formula before you copy it.
(a) By default, Excel uses relative referencing.
(b) To make either the column or the row referencing (or both) absolute, the column or row number has to be preceded by a $ sign.
(c) The user can circle between the various options by using F4 Function key while in the edit mode of the formula.
(A) Paste Special: Instead of copying entire cells, it is possible to copy specified contents from the cells -for example, one can copy the resulting value of a formula without copying the formula itself.
To copy only partially a cell, follow these steps:
(i) Select the cells you want to copy.
(ii) Click Copy.
(iii) Select the upper-left cell of the paste area.
(iv) On the Edit menu, click Paste Special.
(v) Click an option under Paste, and then click OK.
(B) Various options to paste: There are various options to paste special.
The options are summarized in the screen-shot below:
11. How can we secure our documents in Ms-Excel?
Ans: Once the sheet is prepared for data entry purposes which includes data validations, lookups, conditional formatting, etc., one may like to ensure that none of such validations/formulae are accidentally overwritten by the person entering the data. One may therefore look at worksheet protection. To protect a particular sheet, choose Tools → Protection → Sheet. One may provide a password if required. Similarly, to protect the entire workbook, one chooses Tools → Protection – Workbook. Once a sheet/book is protected, no alterations are permitted in the sheet/workbook.
Many a times, the need may be to protect the sheet but at the same time permit data entry in particular cells. To achieve such a dual purpose, one first unlocks the cells wherein the data entry is to be permitted (Choose Format → Cells → Protection) and then follows up the same with the sheet/workbook protection as outlined above. In such a scenario, entry is permitted only into unlocked cells. Worksheet protection still permits the viewing of the formulae. In many cases, the user may not want the other person to know the formula. In that case, the cell formula can be hidden (Choose Format → Cells → Protection). The contents of the cell are of course displayed. It should be noted that hiding a cell has no effect unless the worksheet is itself protected.
Protecting cells helps prevent accidental corruption of a spreadsheet.
To protect cells:
(a) All cells in a spreadsheet are automatically “locked” when the spreadsheet iscreated. But this is inconsequential unless the sheet is protected.
(b) Unlock only cells you may want to edit. To unlock a cell, select the cell, click the right mouse button, select Format Cell (or choose Format/ Cells), select the.
Protection tab in the box that appears, click on the locked box to remove the check that is there, this unlocks the cell.
(c) To protect the spreadsheet, select Tools/Protection/Protect Sheet. In the dialog box that appears, click OK. You may also apply a password but if you forget it you cannot unprotect the sheet!
(d) Once the sheet is protected, only unlocked cells can be edited.
File-Level Protection: One can password-protect the entire file. To do this, after creating the document, Choose “Save As” from the File Menu. Select the general options from the Tools Tab and the following screen appears:
In the file sharing options, specify the passwords. You can specify two types of passwords, one authorising the user to open the file, another permitting him to modify the same. In case you would just like to warn the user about the importance of the file without introducing the hassles of passwords, you can select the “read-only recommended” option.
12. Explain the different types of functions used in MS-Excel.
Ans: The different types of functions used in Ms-Excel is explained below:
(i) Lookup Functions: The value of a cell can also be derived from (looked up from) a pre-defined list. In this regard, it is important that the list to be looked up from should be in the same worksheet and should be sorted. Consider creating a list of status codes applicable to an assessee. The list of status codes and the description is entered in a separate area of the worksheet (ideally the record section should be reasonably far from the data section – for easy navigation, the record section may be appropriately named using a descriptive range name.) On selection of an appropriate status code, Excel can look up the status description from the list. There could be many instances where the lookup facility can be put to practical use – referring the client particulars from the client database, etc. In this regard, one may use the built-in functions of HLOOKUP or VLOOKUP or one may choose to build the function through the use of the Lookup Wizard, an Add-in which ships with MS-EXCEL. But to repeat, remember to sort the record section before you use the lookup functions. (For sorting records, refer to Data → Sort).
(a) VLOOKUP() can be used for columnar search and = HLOOKUP() can be used for a row-wise search.
The arguments are:
(i) The Lookup Value.
(ii) The table range.
(iii) The particular number of row or column in the table range.
(b) INDEX() in its simplified version finds the intersection of a particular row and column references. The arguments include: table range, row number & column number. However, the row and column numbers can be made dynamic through the use of = MATCH() command and this gives the functionality to the INDEX() function. Such process is inbuilt in the Lookup Wizard.
(c) One can also build the function through the Lookup Wizard.
Follow these simple instructions:
(i) Select Tools → Wizard → Lookup.
(ii) Follow the instructions in choosing the row, column and the range.
(iii) To make the lookup dynamic, copy both the formula and lookup parameters.
(ii) Date Functions: EXCEL stores dates as numbers. (also known as date serial number) Conceptually a date is stored as the number of days elapsed since 1st January 1900. To check this out, open a blank sheet and type 1 in one of the cells. Format that number as a date (Form at → Cells → Date). The resultant display is “01-01-00” which represents 1st January 1900. When a date is directly entered into a cell, EXCEL automatically applies the date format and stores the date serial number. Enter a date like 07-08-02 in one of the blank cells. Now change the format of the cell as number (Format → Cells → Number). The display changes to 37475. If it doesn’t the date is entered in text format. Check the display orientation. Most probably, it will be left aligned!
How is this mode of internal storage of any relevance to us? It is, because it helps us appreciate the limitations faced by EXCEL while manipulating dates. The first limitation of EXCEL is that since dates are not stored as dates but as numbers, certain calculations based on months and years have to be derived through formulae and cannot be in-built. You would like to automatically calculate the rebate available for senior citizens based on the date of birth. Let us say the date of birth is 17th March 1938 and we are interested in knowing whether the person is senior citizen (age > 65 years) as on 31st March 2003. Let us assume that the date of birth is entered in cell C3 and the year end date i.e. 31-03-2003 is entered in cell C2. I want to calculate the age. So I enter a formula +C2-C3 in cell C4. To my dismay, I get a number 23755. What does this number represent? It represents the number of days between C2 & C3. It is the age in days but I want the age in years. Can I directly divide the answer by 365? Remember the calculations would be in-exact as there would be leap years as well.
To move further, let us explore three important date functions YEAR (), MONTH() & DAY (). The notations of these functions are self- explanatory and they are used to split a date into its constituent components. For example, YEAR (“17-03-38”) will re turn 1938; MONTH (“17-03- 38”) will return 3 while DAY (“17-03-38”) will return 17. Armed with these newly learned functions, I enter the formula +YEAR (C2)-YEAR (C3) in cell C4. Voila! I get the answer as 65 and am delighted.
Note that the above formula is not fool-proof. Consider the date of birth to be 19th April 1938. While he had still not completed 65 years of age as on 31st March 2003, the above formula treats him as a senior citizen. What was missing? I could make out that in the transitory year, the above formula will not work as the months are not compared at all! Using the I function, the above formula can be modified as + IF(MONTH(C2) > MONTH(C3), +YEAR(C2) – YEAR(C3), + YEAR(C2) – YEAR(C3)-1).
In effect, the above formula compares the month component of the date of birth to determine whether the year has completed by the end of the previous year. If it has, it simply calculates the difference in the year components else, it reduces the difference in the year components by 1. In certain cases, one is interested in the difference in months (need not be completed months). To calculate the number of months of delay, one uses the following formula:
+MONTH(C7) – MONTH(C2) + (YEAR(C7) – YEAR(C2)) x 12.
The formula calculates the difference in months components of the dates. It also considers the difference in the years components by taking the same into account and multiplying the same by 12 to convert it into months. Not too interested in using long and complicated formulae? If you have Analysis Toolpack installed, there is a DATEDIF function which can do the job in a minute. So, to calculate the completed years, I just enter the formula +DATEDIF(C3,C2,”y”). The “y” refers to the completed years.
Similarly, + DATEDIF(C2, C7,”m”) gives me the completed months of delay which turns up to 13! But in certain cases, one would also like to include partially completed months. In effect, one would like to advance the date to the end of the month. This is where the EOMONTH() function is useful. So I modify my formula to say.
+DATEDIF (C2,EOMONTH(C7,0),”m”). The answer is 14!
(iii) Numeric Functions: Various built-in functions can be used for manipulating numbers.. Such functions can be used for rounding numbers [ ROUND(), ROUNDDOWN(), ROUNDUP(), MROUND(), CEILINGO), FLOOR()], counting the cells containing values [COUNT(), COUNTA(), COUNTIF(), COUNTBLANK()], identifying specific values within the list [ MAX(), MIN(), LARGE(), SMALL()] or converting the values into some other format [ ROMAN(), TEXT()]. The functions dealing with the totaling of numbers have already been covered in detail earlier.
(iv) Text Functions: Text functions aim at either case conversion [UPPER(), LOWER(), PROPER()], extraction of part of the text [LEFT(), RIGHT(), MID()], identification and replacement of particular alphabets within a text [ FIND(), SEARCH), REPLACE(), SUBSTITUTE()] or conversion into some other values [ VALUE(), DOLLAR()].
13. Explain all the formatting options available in Ms-Excel.
Ans: Sheet: It is possible to add a picture to the background of the entire sheet. This should however be done with caution.
(i) Cells (or Range): Various formatting features can be done on a single cell or a group of cells. To invoke the formatting features, choose the Format Cells Command (Ctrl+1/Right Click/Format → Cells). The following screen appears The various tabs are analysed in the subsequent paragraphs.
(ii) The Look of Data: This appears in the Numbers Tab The default tab is the Numbers tab. This is the one the user will work with to format the type of number displayed. The options available include: General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom.
(iii) Alignment & Text Control: The text can be left aligned, centred or right aligned. It can even be rotated within a cell. Often, a user wishes to have little more control over the text than is allowed within the standard cell borders. Some available options are: Merge Cells which allows more than one cell to be merged into one (accomplished by highlighting the ones the user wishes to merge), Shrink to Fit which will reduce the apparent size of characters within selected cells so that they will fit within the column and Wrap Text which wraps text into multiple lines in a cell.
(iv) Fonts, Borders & Patterns: In order for grid lines (or any other formatting) to be present, the user must apply the appropriate options. To apply borders right click on the active cell(s) then choose Format Cells from the menu that appears. From the tabbed Formatting Window, left click on the Borders tab to select it. There are parameter locations for the borders to be applied top, bottom, left, right, etc.), as well as a list of line styles and a drop down menu of color choices. The user can left click on the desired options. Left click the OK button to apply the selections and continue.
(v) Indented Text: In case one intends to indent the text within a particular cell, one can use the indent icons on the standard toolbar.
(vi) Hiding Information: It is possible to hide entire sheets, rows or columns within a particular workbook. To hide a sheet, Choose Format → Sheet → Hide. To hide a particular row or column, select the row or column, right click and choose Hide. It is however not possible to hide the contents of individual cells. The work-around solution is to format the cell contents to display the same font color and cell background so that effectively the information gets hidden.
14. Explain the Data validation process of Ms-Excel.
Ans: At times, there may be a need to restrict the content that is being typed into a particular cell. For example, one may want the residential status to be either “Resident” or “Resident but Not Ordinarily Resident” or “Non Resident”. In such a case, the entry into a particular cell can be validated through the “Data Validation” Feature.
The steps for data validation feature are explained below:
(i) Select the cell/range for which validation is to be applied.
(ii) Choose Data → Validation from the menu. The following screen appears.
(iii) This feature validates only Keyboard Input that too in cases where the entry is made after the validations are set and hence may have limitations.
(iv) The user can choose the type of data and the range of data (which may be open-ended from one side). Alternatively, the user can specify a predefined list to choose from.
(v) The user can also specify the action to be taken in case the data entered is invalid.
(a) “STOP” does not permit entry of invalid Data.
(b) “WARNING” allows alteration to invalid data. The user may still continue with the invalid data.
(c) “INFORMATION” just informs about the invalid data.
(d) Unchecking the “CHECK BOX” on the top allows entry of invalid data without any disturbance.
(vi) The Auditing Toolbar (Tools → Auditing → Show Auditing Toolbar) contains icon which enables the circling of invalid data for attention (second last icon on the toolbar)
15. Explain the sorting procedure in Ms-Excel.
Ans: In case a list of data is typed, one may need the data arranged in a particular fashion. For example, you may want your client details either alphabetically or based on the client codes. This is where sorting is useful and the same is very simple. Choose the relevant command from the Data Menu and the Sort Wizard takes you through the rest of the process. Remember, sorted data is always advantageous from three counts: firstly, it improves readability, secondly, it permits effective lookups and lastly, it lays down the foundation for data grouping and sub-totalling.
(a) Sorting is a permanent process as compared to filtering which is a temporary process.
(b) Choose any cell in the data range and Select Data → Sort.
(c) Choose successive sort keys and sort order.
On a brief review of the above screen, one understands that the sort function permits sorting only upto three levels of data. If sorting is required for multiple levels of data, then one will have to use the sort function more than.
16. What is the use of a function in MS-Excel? And also explain function burter.
Ans: A cell can also derive its value through functions. Functions are processes, which have been defined and standardized by Excel. A complete list of functions can be found at Insert → Function. Few more common functions include the SUM function (which totals all the numbers in a particular range – of course, EXCEL also has the QuickSum Feature which displays the sum of the selected range in the bottom pane) and the IF function used to manage alternate calculations in varying situations (it is very simple to use and can be nested, but take care to use the brackets appropriately otherwise the results can be disastrous!). A very common example of the use of IF function is to calculate the tax payable by an individual.
For example, if cell B3 contains the net taxable income of an individual, the tax payable by him (excluding surcharge) can be calculated using a nested IF function as stated: =IF (B3 >150000, (+B3- 150000)* 0.3+19000, IF (B3>60000, (B3-
60000)* 0.2+1000,IF (B3>50000, (B3-50000) x 0.1,0)))
Function Builder: A function takes in certain standard arguments, undertakes the evaluation process and returns a particular result. In case one is unaware of the arguments, one can type the function name along with the opening parenthesis and click on the = sign on the Formula Bar. The Function Wizard presents the list of arguments and the brief description of the arguments. In such a fashion, one can build a formula through a Wizard and simultaneously learn the function itself.
17. Explain the component of MS-Excel.
Ans: On loading MS-EXCEL (either through the shortcut menu, or icon on desktop or through the Start Menu), the following screen appears:
The various components of the EXCEL Screen are explained in brief below:
(a) Title Bar: Contains the name of the File currently open and also hasthe window control buttons to either close or minimize the program.
(b) Menu Bar: Contains the list of various commands that can be performed in MS-EXCEL. It can be invoked either by a mouse click or the Alt Key from the keyboard.
(c) Tool Bars: Contain buttons for some commonly performed tasks. The commands can be activated by a mere mouse-click.
(d) Formula Bar: Displays the content of the active cell. The left hand side of this bar includes the name box which contains the list of all the range names and thereby facilitates quicker worksheet navigation.
(e) Column Labels: Contains the headings of the columns. Can be used for column-wide operations like increasing column width, hiding columns, formatting entire columns, etc.
(f) Row Labels: Contains the headings of the rows. Can be used for rowwide operations like increasing row height, hiding rows, formatting entire rows, etc.
(g) Sheet Area: The place where the actual data is entered. The Active Cell is surrounded by a dark rectangle.
(h) Sheet Tab: Gives reference to the sheet which is currently active. One can quickly navigate through different sheets from here 9 Additional Tool Bars Some additional toolbars.
(i) Status Bar: Includes the various information sent by EXCEL. Of particular use is the QuickSum Feature in the status bar which automatically displays the totals of the selected cells.
(j) Scroll Bars & Split Indicator: The Scroll Bars can be used for quick movement within a worksheet. The extreme top of the vertical scroll bar and the extreme right of the horizontal scroll bar contain a split indicator which permits the user to divide the sheet into two parts.
(k) Application Control Buttons: These buttons are used to minimize or control the size of a particular file.
18. Write the steps for creating a new file, opening an existing file and saving a file in MS- EXCEL.
Ans: Steps for creating a new workbook:
(a) Click the new Blank Workbook button the standard toolbar.
(b) Or, frame Menu bar, choose File> New, the New Workbook Task Pane will open, and select Blank workbook.
Steps for opening an existing workbook: Click the open button found on the standard tool bar.
From the Task pane, select getting started and select more.
Or, from the menu bar, select File Open found on the standard toolbar.
From the task pane, select getting started and the select more.
(c) Or, from the menu bar, select File> Open.
Any of these methods well show the open dialog box. Choose the file and click the open butoon.
Steps for saving / storing a work book: Click the save button on the standard toolbar. Or, from the menu bar, select File> Save.
19. Explain about MS-Excel.
Ans: Microsoft Excel is a spreadsheet program that is designed to record and analyze numbers and data. Excel is very widely used for accounting and financial purposes.
The files created in Excel are known as workbooks. In turn, each workbook can contain one or more worksheets. An Excel worksheet is laid out like a grid with horizontal rows and vertical columns. Columns are labeled with alphabets (A, B, C, etc.) while rows are given numbers (1, 2, 3, etc.). The intersection of a row and a column is called a cell. A cell is referred by a combination of column alphabet and row number (A1, A2, etc.). A cell is a primary unit of measure in Excel and all the information is stored in cells. . A range is a collection of contiguous cells (which form a rectangular block) on which the user wants to perform similar type of calculations. A range is referred to by a combination of the cell addresses of the diagonally opposite cells separated by a colomm (A1:D6).
