Class 12 Commerce . Accountancy . Computerised Accounting System . Use of Spreadsheet in Business Applications
Chapter 3: Use Of Spreadsheet In Business Applications
Ch 3
Accountancy
Class 12 Commerce
Introduction &Payroll Accounting Part- I
Introduction
- Applications: Spreadsheets can support business calculations, including payroll, fixed-asset accounting and loan repayment.
Payroll Accounting
Payroll processing sequence
- Record employee, pay-period and attendance details
- Calculate earnings using pay values and applicable rates
- Calculate deductions using applicable rules and employee instructions
- Find net salary and prepare salary statements or slips
- Generate bank advice for salary transfers and statutory payments
Payroll Components
| Component | Meaning |
|---|---|
| Basic Pay (BP) | Pay in the pay scale plus Grade Pay; it excludes Special Pay. |
| Grade Pay (GP) | Pay added according to the employee’s designation and applicable pay band or pay scale. |
| Dearness Pay (DP) | The portion of Dearness Allowance declared and treated as merged with Basic Pay. |
| Dearness Allowance (DA) | Compensation for loss of purchasing power due to rising prices; granted periodically as a percentage of Basic Pay plus Dearness Pay, where applicable. |
| House Rent Allowance (HRA) | An amount paid to help the employee obtain residential accommodation on lease. |
| Transport Allowance (TRA) | An amount paid to help meet the cost of commuting to the place of work. |
| Other earnings | Other allowances declared from time to time, such as education, medical or washing allowance. |
DA is calculated as a percentage of Basic Pay and Dearness Pay, if applicable.
| Component | Meaning |
|---|---|
| Professional Tax (PT) | A statutory deduction imposed under the relevant State Government legislation; it applies in some states. |
| Provident Fund (PF) | A statutory social-security deduction under the Provident Fund Act, calculated as a percentage of Basic Pay plus Dearness Pay, if applicable. |
| Tax Deduction at Source (TDS) | Monthly deduction towards the employee’s income-tax liability; it apportions the yearly liability across 12 months. |
| Loan instalment recovery (LOAN) | An amount deducted as authorised by the employee towards repayment of a loan. |
| Other deductions | Other recoveries, such as salary advance, food grain advance or festival advance. |
PF is computed using the applicable rate on Basic Pay and Dearness Pay, if applicable.
Gross salary is total earnings before deductions; net salary is the amount payable after deductions.
Using the given illustrative figures, find the employee’s gross and net salary.
- Suppose an employee’s monthly earnings are BP Rs. 30,000, DP Rs. 2,000, DA Rs. 6,400, HRA Rs. 5,000, TRA Rs. 1,000 and other earnings Rs. 600. Total deductions are PT Rs. 200, PF Rs. 3,200, TDS Rs. 1,500 and loan recovery Rs. 1,000.
- Add all earnings to find gross salary.
- Add the deductions.
- Subtract total deductions from gross salary.
Do not subtract deductions when finding gross salary. Also, follow the organisation’s salary format to avoid counting Grade Pay twice when Basic Pay is defined as including it.
An employee has Basic Pay Rs. 24,000, Dearness Pay Rs. 1,000 and DA at 10% of BP plus DP. Find DA.
Gross salary is Rs. 48,000 and total deductions are Rs. 7,250. Find net salary.
Name the payroll document or advice used to show the net salary to be transferred to employees’ bank accounts.
Quick recap
- Payroll calculations use employee pay, attendance, applicable rates and deductions for a specified month and year.
- Payroll work includes maintaining employee data, computing payroll, preparing salary statements and slips, and generating bank advice.
- Earnings include BP, DP, DA, HRA, TRA and other allowances; DA is based on BP plus DP, if applicable.
- Deductions include PT, PF, TDS, loan recovery and other authorised recoveries.
- Gross salary is total earnings before deductions; net salary equals gross salary less total deductions.
Introduction & Payroll Accounting Part-II
Payroll Calculation Elements
- Payroll elements: Payroll calculations use attendance, pay rates and applicable allowances to determine employee earnings.
- Effective days: Basic Pay Earned is based on the employee’s effective days present during the month.
- Total earnings: Basic Pay Earned, DA, HRA and transport allowance are combined to calculate total earnings.
Deductions and Net Salary
- Deductions: Common payroll deductions include Provident Fund, TDS and loan instalments.
- Total deductions: Add the applicable deductions to calculate Total Deductions.
- Net salary: Net Salary = Total Earnings − Total Deductions.
Spreadsheet Template and Payroll Layout
| Parameter or employee type | Rate or amount |
|---|---|
| DA | 35% of BPE |
| Supervisory HRA (Sup) | 40% of BPE |
| Non-supervisory HRA (Nsup) | 30% of BPE |
| Consultant or contract employee | Nil HRA and nil TRA in the example |
| Supervisory TRA | Rs. 1,000 fixed |
| Non-supervisory TRA | Rs. 500 fixed |
| PF | 12% of BPE in the spreadsheet calculations |
Calculate payroll net salary for the given employee.
- For a supervisory employee with BP Rs. 16,500, 1.5 deduction days in a 28-day month, find effective days.
- Calculate basic pay earned.
- Calculate DA and HRA using the applicable rates.
- Add TRA to find total earnings.
- Calculate PF and total deductions.
- Subtract deductions from earnings to find net salary.
Do not calculate full-month basic pay when the employee has unpaid or unauthorised absence: prorate it by effective days. The source’s parameter-cell references differ (the prose names one set of cells while its sample formulas use another), so use the actual parameter locations consistently. Also, the source’s PF rule table says 12% of BP + DA, but the spreadsheet formulas and example values calculate 12% of BPE; follow the stated formula for the spreadsheet example.
Quick recap
- Effective days = days in the month − deduction days; BPE is prorated using effective days.
- DA, HRA and TRA are added to BPE to obtain total earnings.
- PF, TDS and loan instalments make up total deductions.
- Net salary = total earnings − total deductions.
- A payroll template separates direct inputs, fixed parameters and formula-driven results.
- Absolute cell references keep common parameter cells fixed when formulas are copied.
Asset Accounting Part-I
Assets and Depreciation
Fixed assets — Long-term resources that provide productive capability to an organisation; they include both tangible and intangible assets.
Asset types and examples
- Current assets: Assets classified separately from long-term fixed assets.
- Tangible fixed assets: Physical assets with form, shape and size, such as buildings and machinery.
- Intangible fixed assets: Non-physical resources that add value, such as goodwill, patents, copyrights and trademarks.
- Depreciation: Recognises the cost of a fixed asset consumed during an accounting period because its useful life extends beyond one year.
- Freehold land: Depreciation is usually not charged on freehold land.
| Category | Types or examples |
|---|---|
| Goodwill | Goodwill |
| Land | Freehold land; leasehold land |
| Building | Factory; office; residential |
| Plant and machinery | Plant and machinery |
| Furniture and fixtures | Furniture and fixtures |
| Vehicles | Vehicles |
| Capital work in progress | Capital work in progress |
| Others | Other asset classes |
Year-to-date depreciation — Accumulated depreciation from the date an asset is put to use up to the current accounting year.
Total depreciation over the asset’s useful life is acquisition cost less salvage value. Let denote acquisition cost and salvage value.
Asset-accounting records and methods
- Records maintained: An asset register, a depreciation calculation sheet and a fixed-asset schedule.
- Reporting: The fixed-asset schedule supports balance-sheet reporting in the annual accounts.
- Methods: Depreciation is calculated according to organisational policy, commonly using the Straight Line Method (SLM) or Written Down Value Method (WDV).
Computerised Asset Accounting
Computerised asset accounting
- Prescribed rates: The Companies Act, 1956, Schedule XIV lists depreciation rates for asset classes under SLM and WDV.
- Method choice: Prescribed rates differ between methods; corporate enterprises may use either method and its applicable rates for reporting.
- Spreadsheet use: Asset details are entered in columns, while calculated values are produced with formulas or built-in functions.
Straight Line Method (SLM)
Straight Line Method — A depreciation method that charges an equal amount of depreciation in each year of an asset’s useful life.
SLM inputs
- Acquisition cost: Purchase value plus related costs, such as transportation, installation and pre-operating expenses.
- Salvage value: The value expected to be realised at the end of the asset’s useful life.
- Depreciable amount: Acquisition cost less salvage value.
Let be purchase value, other acquisition expenses, salvage value, and useful life in years.
Calculate SLM depreciation in a spreadsheet
- Add purchase cost, installation expenses and pre-operating expenses to find acquisition cost.
- Subtract salvage value from acquisition cost to find the total depreciable amount.
- Divide the depreciable amount by useful life in years to obtain annual SLM depreciation.
- Use the spreadsheet SLN function with cost, salvage value and useful life as its inputs.
| Column | Heading | Entry or formula |
|---|---|---|
| A | Asset Name | Entered directly |
| B | Date of Purchase | Entered directly |
| C | Date of Installation | Entered directly |
| D | Cost of Purchase | Entered directly |
| E | Installation Expenses | Entered directly |
| F | Pre-operating Expenses | Entered directly |
| G | Cost to Use | |
| H | Salvage Value | Entered directly |
| I | Life in Years | Entered directly |
| K | Depreciation Amount | SLN function; example reference: |
Date cells in the example sheet
- Year beginning date: Entered in cell .
- Year end date: Entered in cell .
- First-half year end date: Entered in cell .
| Asset | Purchase date | Installation date | Purchase cost | Installation expenses | Pre-operating expenses | Cost to use | Salvage value | Life | Allowed depreciation | Depreciation |
|---|---|---|---|---|---|---|---|---|---|---|
| CNC Machine | 11-Jul-08 | 17-Jul-08 | Rs. 877,000 | Rs. 11,000 | Rs. 3,000 | Rs. 891,000 | Rs. 45,000 | 7 years | 100% | Rs. 120,857.14 |
| Packing Machine | 03-May-06 | 07-May-06 | Rs. 123,000 | Rs. 8,000 | Rs. 2,500 | Rs. 133,500 | Rs. 17,000 | 7 years | 100% | Rs. 16,642.86 |
Using SLM, calculate annual depreciation for M/s GUNGUN Ltd.’s CNC Machine and Packing Machine.
- Calculate the CNC Machine's cost to use by adding purchase cost and related expenses.
- Subtract salvage value and divide by its useful life to find annual SLM depreciation.
- Calculate the Packing Machine's cost to use, then its annual depreciation.
An asset costs Rs. 250,000, has a salvage value of Rs. 25,000 and a useful life of 5 years. Find annual SLM depreciation and the depreciation rate.
Purchase cost is Rs. 180,000, installation expenses are Rs. 12,000, pre-operating expenses are Rs. 8,000, salvage value is Rs. 20,000 and useful life is 6 years. Find annual SLM depreciation.
Do not divide purchase cost alone by useful life: first add relevant acquisition expenses, then subtract salvage value before dividing by useful life.
Quick recap
- Fixed assets are long-term resources and include both tangible and intangible assets.
- Depreciation recognises the cost of a fixed asset consumed over its useful life; freehold land is usually not depreciated.
- Depreciable amount equals acquisition cost less salvage value.
- SLM charges equal annual depreciation: depreciable amount divided by useful life.
- Spreadsheet SLN uses cost, salvage value and useful life to calculate SLM depreciation.
- Asset registers, depreciation sheets and fixed-asset schedules support annual balance-sheet reporting.
Asset Accounting Part-Il
Written Down Value (WDV) Method
- Method: The Written Down Value method calculates depreciation using the asset’s current book value as the base for the next period.
- Declining balance: WDV is also called the Declining Balance method.
- Excel function: The DB function calculates depreciation under this method.
WDV Spreadsheet Inputs and Timing
Use these formulas to prepare the asset inputs and calculate depreciation.
Find the WDV depreciation for the CNC Machine and Packing Machine for FY 2008–09 using the DB spreadsheet.
- For the CNC Machine, calculate the cost to use from its purchase cost, installation expenses and pre-operating expenses.
- Use the given salvage value, life, period and first-year months as the remaining DB inputs.
- For the Packing Machine, use its cost to use and the stated DB inputs.
Schedule Forming Part of the Balance Sheet
Apply the same calculations to each fixed-asset category.
| Asset | Opening gross | Gross additions | Gross deductions | Closing gross | Opening depreciation | Depreciation additions | Depreciation deductions | Closing depreciation | Opening net block | Closing net block |
|---|---|---|---|---|---|---|---|---|---|---|
| Building | 12,081.00 | 0.00 | 0.00 | 12,081.00 | 1,812.15 | 604.05 | 0.00 | 2,416.20 | 10,268.85 | 9,664.80 |
| Plant & Machinery | 13,942.00 | 2,765.00 | 14.00 | 16,693.00 | 4,182.60 | 166.93 | 0.00 | 4,349.53 | 9,759.40 | 12,343.47 |
| Office & Other Equipment | 2,894.00 | 616.00 | 3.00 | 3,507.00 | 868.20 | 350.70 | 0.00 | 1,218.90 | 2,025.80 | 2,288.10 |
| Furniture & Fixtures | 1,681.00 | 777.00 | 0.00 | 2,458.00 | 504.30 | 245.80 | 0.00 | 750.10 | 1,176.70 | 1,707.90 |
| Vehicles | 927.00 | 483.00 | 24.00 | 1,386.00 | 370.80 | 277.20 | 0.00 | 648.00 | 556.20 | 738.00 |
An asset has an opening gross block of Rs. 50,000, additions of Rs. 8,000 and deductions of Rs. 1,500. Find its closing gross block.
Opening gross block is Rs. 80,000 and opening depreciation is Rs. 12,000. Closing gross block is Rs. 90,000 and closing depreciation is Rs. 18,500. Find the opening and closing net blocks.
Quick recap
- WDV/DB: Each period’s depreciation uses the current book value; Excel’s DB function calculates the amount.
- Cost to use: Add purchase cost, installation expenses and pre-operating expenses.
- Timing inputs: Installation date determines the period, first-year end date and first-year months.
- DB inputs: Cost, salvage value, useful life, period and first-year months.
- Gross block: Closing amount = opening amount + additions − deductions.
- Depreciation and net block: Closing depreciation adjusts for additions and deductions; net block = gross block − depreciation.
Loan Repayment Schedule
Loan and Repayment
- Loan: A loan is borrowed principal for a specified period at a pre-agreed interest rate.
- Repayment: The borrower repays the loan through periodic instalments, usually monthly, over the repayment period.
Using PMT to Calculate Instalments
| Parameter | Meaning |
|---|---|
| Rate | Interest rate for each payment period. |
| Nper | Total number of payments; its time unit must match the rate. |
| Pv | Present value of the loan, i.e. the amount borrowed. |
| Fv | Future value or remaining balance at the end; usually for a fully repaid loan. |
| Type | means payment at the beginning of a period; means payment at the end. |
Use the parameters in this order. In Excel, a negative present value can be used so the payment is displayed as a positive amount.
Spreadsheet Layout and Formulas
| Column | Heading | Entry or formula |
|---|---|---|
| A | Loan amount | Enter the loan amount. |
| B | Loan disbursement date | Enter the date. |
| C | Period of loan (years) | Enter the period. |
| D | Rate of interest | Enter the annual rate. |
| E | Future value | Enter when the loan is fully repaid. |
| F | Yearly instalment amount | |
| G | Monthly instalment |
The given layout first calculates the yearly instalment, then divides it by .
What are the yearly and monthly instalments?
- A loan of Rs. 1,00,000 is given on 1 April 2007 for 2 years at 10% interest. Find the instalments using the spreadsheet layout.
- Calculate the yearly instalment using the PMT formula with type 1, meaning payment at the beginning of each period.
- Divide the yearly instalment by 12 to obtain the monthly instalment.
| Loan | Period and rate | Future value | Yearly instalment | Monthly instalment |
|---|---|---|---|---|
| Rs. 1,00,000; disbursed 1 April 2007 | 2 years; 10% | Rs. 52,380.95 | Rs. 4,365.08 | |
| Rs. 2,50,000; disbursed 15 May 2008 | 3 years; 11% | Rs. 92,165.11 | Rs. 7,680.43 |
Check the payment timing and time units before using PMT. The given spreadsheet formula uses annual rate and years, and sets type to .
A loan of Rs. 2,50,000 is taken for 3 years at 11%. Using the given schedule approach, what are the yearly and monthly instalments?
In the PMT function, what does type indicate, and why is future value commonly entered as for a fully repaid loan?
Quick recap
- A loan is borrowed principal repaid through instalments over an agreed period at a stated rate.
- PMT uses rate, number of payments, present value, future value and payment timing.
- The rate and number of payments must use matching time units.
- Future value is zero when the loan is fully repaid.
- In the given spreadsheet, yearly instalment is calculated with PMT and divided by 12 for the monthly amount.
- The spreadsheet formula uses type 1, so payment is assumed at the beginning of the period.
Summary
Spreadsheet Applications and Planning
Spreadsheet application — A spreadsheet application uses entered values and formulas to carry out business calculations.
Planning a spreadsheet
- Applications: The chapter illustrates payroll calculation, fixed asset accounting, and loan repayment scheduling.
- First step: List the elements needed for the application.
- Classify each element: Decide whether its value is entered directly or calculated with a formula.
- Built-in functions: Excel provides functions, including financial functions, for calculations; a formula may use one or more functions.
How should the elements of a spreadsheet application be planned?
- When planning a spreadsheet application, first list its required elements.
- For each element, mark whether it is entered directly or computed using a formula.
Absolute Addresses and the IF Function
Absolute address — An absolute address keeps a cell reference fixed when a formula containing it is copied to other cells.
Key uses
- Absolute reference: Use it when the referenced cell’s content must not change as the formula is copied; for example, `