Posts
Q1) Explain the various Cell Reference methods.
A formula represents certain cell relationship, which generates a result. When typing the cell address in the formula, three types of cell references can be used. A cell reference speicifies, when the formula is copied to other cells, its column or row number is to change or not.
| Relative Reference | =B3*C3 |
| Absolute Reference | =$B$3*$C$3 |
| Mixed Reference | =B3*$c3 |
Relative Reference
If a formula with relative reference is copied, the cell references used in the formula will automatically change in the copied cell. For example, when the formula = B3*C3 in cell D3 is copied to D4, the formula will be =B4*C4 in the cell D4. Similarly, if the formula in D3 is copied to E3, the formula will be =C3*D3.
Absolute Reference
If the formula with absolute reference is copied, the cell references used in the formula remain unchanged (no change in column or row number). To achieve this, use $ symbol before the column letter and the row number. For example, when the formula =$B$3*$C$3 in cell D3 is copied to D4, the formula will be =$B$3*$C$3 itself because both column letter and row numbers are made constant. That means, the result in the cells D3 and D4 will be the same
Mixed Reference
If a formula with mixed reference is copied, the cell references used in the formula will change either the column letter or row number but not both of them. To achieve this, use $ symbol before the column letter or the row number.
For example, when the formula =B$3*C$3 in cell D3 is copied to D4, it will be =B$3*C$3. But if the formula is copied to cell E3, it will be =C$3*D$3 because, the row numbers are made constant and not the column letters.
Q2) What is Auto fill feature in Excel? Explain the steps to create your own Auto fill.
The Auto fill feature in MS-Excel will save data entry time by expanding series of numbers, days of week, different months, etc. from a given cell to adjacent ones. This is achieved using the Fill Handle.
1- To generate the serial numbers 1,2,3,4…. with the increment 1, type 1 in any cell and Ctrl + Drag the fill handle down or right, this will increase the number by 1 in each cell and Ctrl + Drag the fill handle up or left will reduce the number by 1 in each cell. Instead of 1, any other starting number can also be entered. As we drag the fill handle, the number, which will appear in the cell, will be displayed near the fill handle of our reference.
2- To generate the serial numbers with the increment or decrement other than 1, type the first 2 numbers; block those 2 cells and the Drag the fill handle. To generate 5,10,15,20…one below the other, type 5 in any cell, in the next cell type 10, block those 2 cells and drag the fill handle down. We can also generate the numbers in descending order by typing 100, 95 in different cells.
3- Text can be entered with the number. To generate F1, F2, F3…type F1 in any cell and Drag the fill handle.
4- To generate Quarter numbers Q1, Q2, Q3, Q4 type Q1 in any cell and drag the fill handle. We can also write QTR 1 or Qtr1 or Quarter 1 or quarter1 etc.
5- To generate the name of months January, February, March etc., type January in any cell and drag the fill handle. We can also start from any month name. Name of month can also be written as JAN, jan, January, JANUARY etc. The result will follow the upper or lower case letters that we have used in the first month name. In the same way, we can also generate the day of week in words like Sunday, Monday, Tuesday etc.
6- We can also generate April, July, October, and January (leaving 2-month names in the middle) by typing April and July in different cells, block the cells and drag the fill handle.
7- We can also generate 1st April, 2nd April, 3rd April etc., by typing 1st April in any cell and drag the fill handle.
8- We can also generate the continuous dates by typing any date and dragging the fill handle. For example, type 1/1/99 and drag the fill handle.
Using Auto Fill Based on Adjacent Cells
Suppose A1:A5 is to be filled by content of cells F1:F5
- Type any numbers in cell F1:F5
- Enter cell address of cell F1 in cell A1 (i.e. =F1 in cell A1)
- Click and drag the fill handle of cell A1 to A5. Cells A1 to A5 will be filled with F1 to F5. Or double click on the fill handle.
Q3) What are charts? What are the various types of Charts available in Excel? Explain the
steps to include chart in a worksheet.
Charts are graphical representation of numerical data. A set of numeric data, which is actually plotted, is called data series. Each value in the data series is a data point. Data series can be in rows or columns.
Types of Charts
Depending on the appearance of chart, chart can be an Embedded Chart and Chart Sheet. Embedded chart is
drawn on the worksheet where the data exists. Chart sheet is a separate sheet containing only the chart in it.
Steps to Create a Regular Chart
- Select the data range with the column headings and one text column entry, e.g., A2:D6.
- Click on the Chart Wizard icon in the Standard Toolbar or select Insert > Chart. Chart wizard step 1 of 4 dialog will be displayed.
- Step 1, displays the available type of charts under the groups Standard Type and Custom Type. Default is the Column Chart available in the group Standard Types. Select a type of chart. On the right side, the selected chart’s sub-types will be displayed. Select a sub-type. To see the Sample chart click and hold down the Press and hold to view example Button. Click on Next to go to second step.
- Step 2, allows the user to specify the data range, series in rows or columns, and specify the range of each series including the range of x-axis labels.
- In Step 3, titles, axes, grid lines, legend position, data labels, and data tables for the chart can be specified.
Titles: Allows you to give different titles for the chart.
Chart Title: Main title for the chart.
Category (X) axis: Title for X-axis, will be displayed below the X-axis labels.
Value (Y) axis: Titles for the Y-axis, will be displayed at the left side of Y-axis numbers.
Second category (X) axis: Titles for the Second X-axis.
Second Value (Y) axis: Title for the secondary Y-axis, will be displayed at the right side of numbers.
Second Y-axis will appear on the right side of column chart.
For the chart, give the chart title – Sale of Soaps in Oct. , Nov. and Dec., Category (X) axis title – Soaps, value (Y) axis title – Quantity.
Axis: It can be used to specify whether the primary X-axis and Y-axis must be displayed in the chart or not.
Category (X) axis: If deselected, X axis labels will not be displayed. If selected X-axis labels can be displayed in date format or with the content of X-axis label range.
Value Y-axis: If deselected, Y-axis numbers are not displayed. To display Y-axis numbers, this option must be selected.
Gridlines: It is used to specify whether major and minor X-axis and Y-axis gridlines are to be displayed in the chart or not. By default, only value Y-axis Major gridlines will be displayed.
Legend: It is used to specify, whether the legend for the chart is to be displayed or not. A legend is an index displaying the color of series and the name of series. If it to be displayed, specify the placement of legend in the chart (bottom, corner, top, right, or left). By default, the legend will be displayed at the right side of the chart.
Data labels: It is used to specify whether the data labels must be displayed inside the chart or not. Number or text displayed above each point is called data labels. If the data labels must be displayed, either the value (number) or label (text) must be displayed as the data labels. We an also specify, whether the legend key is to be displayed next to the label or not. By default, the data labels are not displayed.
Data Table: It is used to display the data below the chart. By default, it is not displayed. To display the data table, select Show data table check box. We can also specify, whether legend keys must be displayed along with the data table or not. If data labels are displayed, data table need not be displayed in the chart.
After specifying the required options, select Next to go to the last step.
- Step 4, allows you to specify the placement of chart. The chart can be placed in a new chart sheet named Chart1 by selecting the option. As new sheet, or it can be placed as an object in any one of the existing worksheets by selecting As object in.
- Click on the Finish button to complete the creation of the chart.
Q4) Write short notes on the various Account groups in Tally.
Tally provides a set of 28 pre-defined groups, of those 15 are primary groups and 13 are sub-groups as follows:
- Capital Account: This is a primary group to hold the Capital and Reserves of the company. Accounts like Partners Capital A/c, Proprietors Capital A/c, Share Capital, Partner or Proprietors Drawings etc., belong to this group.
- Reserves and Surplus: This is a sub-group of Capital Account. Accounts like Investment Allowance Reserve, General Ledger and other such Reserves belong to this group. This group is called by the name Retained Earnings.
- Current Assets: This is a primary group. All assets of the company are placed under this head. But generally accounts are created for its sub-groups. There are six sub-groups to Current Assets. Any asset, which does not fall into six sub-groups can be created under the group Current Assets.
- Bank Accounts: This is the first sub-group of Current Assets. Any banks Savings or Current A/c’s can be placed under this group.
- Cash-in-hand: This is the second sub-group of Current Assets. This will hold Cash A/c only. Cash account is automatically created under this group, when a company is created.
- Deposits (Assets): This is the third sub-group of Current Assets. Accounts related to Deposits like Security Deposits, Fixed Deposits, and Rental Deposits etc. can be created in this group.
- Loans and Advances: This is the fourth sub-group of Current Assets. Accounts related to advances and loans of non-trading nature for example, advances against salaries, work contracts, advances to purchase fixed assets etc. can be created in this group.
- Stock-in-hand: This is the fifth sub-group of Current Assets. This is used to hold the opening and closing stock figures of certain accounts like raw materials, work in progress, finished products etc.
- Sundry Debtors: This is the 6th sub-group of Current Assets. This group is used to create the account of customers (all parties to whom Credit sales are made).
- Current Liabilities: This is a primary group to hold the outstanding and statutory liabilities of the company, such as ESI, PF, TDS etc. It has three sub-groups.
- Duties and Taxes: This is the first sub-group in Current Liabilities. Accounts of trade duties and taxes like Excise, Local Sales Tax, Central Sales Tax etc. can be opened in this group.
- Provisions: This is the second sub-group of Current Liabilities. Account of trade creditors/suppliers (all parties from whom credit purchases are made) are created under this group.
- Fixed Assets: This is a primary group. Accounts like Building, Land, Furniture, Machinery etc., can be created under this group.
- Investments: This is a primary group. It holds the ledger accounts of all types of investments made by the company on shares, bonds, debentures and other securities of the company as well as other companies.
- Loans (Liability): This is a primary group. Accounts of loans taken by the company are created in this group. It has three sub-groups.
- Bank OD Accounts (Bank OCC Accounts): This is the first sub-group of Loans. Overdraft accounts of a bank like hypothecation accounts, bill discounting accounts etc, can be opened under this group.
- Secured Loans: This is second sub-group of loans. Accounts of Loans taken by the company from financial corporations, banks etc., against its fixed assets are created under this group.
- Unsecured Loans: This is third sub-group of loans. Accounts of Loans taken by the company unconditionally from outside parties including debtors and partners can be created under this group.
- Suspense Account: This is a primary group. Account of money received or paid by the company, the nature of which is not known like money paid as traveling advance, can be created in this group. This is a balance sheet item.
- Misc. Expenses (Assets): A rarely used primary group. Preliminary and preoperative expenses, which have been capitalized to the extent of not writing them off in the profit and loss account, will fall into this group.
- Sales Account: This is a primary group. (General Sales A/c, State Sales A/c etc. can be created under this group.)
- Purchase Account: This is a primary group. (General Purchase A/c, State Purchase A/c can be created under this group.)
- Direct Income [Income Direct]: This is a primary group. Selling the goods of the company can create accounts of direct income of the company like income by selling the goods of the company under this group.
- Indirect Income [Income Indirect]: This is a primary group. Accounts of indirect income like rent received, interest received, commission received etc., can be created under this group.
- Direct Expenses [Expenses Direct]: This is a primary group. The accounts related to payment of wages, freight, coolie, carriage, electricity bill, water bill etc., can be created under this group.
- Indirect Expenses [Expenses Indirect]: This is a primary group. The accounts of payments made for advertisement, maintenance of vehicles, salary paid to staff, depreciation on fixed assets, traveling expenses etc., can be created under this group.
Branch/Divisions: This is a primary group. Accounts of branches, divisions, sister concerns, subsidiaries etc., of the existing company can be created under this group.
0 Comments:
Post a Comment