USE OF SPREADSHEET IN BUSINESS APPLICATIONS

Notes on the Use of Spreadsheet in Business Applications

This chapter elaborates on how spreadsheets, particularly Microsoft Excel, can be effectively utilized for various business applications. We will explore payroll accounting, depreciation calculations for asset management, and loan repayment schedules, providing a comprehensive understanding for students.

1. Payroll Accounting

Payroll accounting is crucial in managing employee compensation within an organization. It involves the systematic recording and tracking of salary payments based on various factors such as attendance, allowances, and deductions. Here are the steps involved in payroll accounting:

a. Maintain Payroll Data

Essential payroll data include:

• Employee Number: Unique identification for every employee.
• Employee Name: The name of the employee.
• Attendance: Data on days worked, leave without pay (LWP), and unauthorized absence.
• Basic Pay: The fundamental amount calculated as per the pay scale.
• Allowances: Includes Dearness Allowance (DA), House Rent Allowance (HRA), etc.
• Deductions: Statutory deductions like Professional Tax (PT), Provident Fund (PF), and Tax Deduction at Source (TDS) must be recorded.

b. Payroll Computation

Payroll computation includes several formulas to calculate:

• Basic Pay Earned (BPE): This is derived from the Basic Pay and the number of effective days present.
• Total Earnings (TE): Sum of all earnings, including BPE, DA, HRA, and Transport Allowance.
• Total Deductions (TD): Sum of all deductions.
• Net Salary (NS): The resulting amount after total deductions, calculated as NS = TE - TD.

c. Template Design

Designing the spreadsheet layout is critical for efficient data handling. It typically consists of columns for employee data, earnings, deductions, and summarized totals. Using absolute references in formulas (e.g., $G$3) ensures consistency when the formula is copied across multiple cells.

2. Asset Accounting

Asset accounting pertains to tracking the values and depreciation of an organization’s assets. There are two primary methods for calculating depreciation: Straight Line Method (SLM) and Written Down Value Method (WDV).

a. Straight Line Method (SLM)

• Formula: SLM Depreciation = Total Depreciable Amount / Expected Useful Life
• Process: Establish the acquisition cost and salvage value. The total depreciable amount is the acquisition cost minus the salvage value. SLM spreads this cost evenly across the asset’s useful life. Introduction to SLN function in Excel simplifies this calculation.

b. Written Down Value Method (WDV)

• Formula: Depreciation for each period is computed on the basis of the written-down value at the beginning of the period. The DB function in Excel can be utilized for these calculations, reflecting a declining balance.

3. Loan Repayment Schedule

In business economics, managing loans is vital. Excel’s PMT function enables businesses to compute the monthly installment required to repay a loan.

a. Understanding the PMT Function

• Parameters:
  • Rate: Interest rate per period
  • Nper: Total number of payments
  • Pv: Present value, or loan amount
  • Fv: Future value (often zero at the end of the loan term)
  • Type: Timing of payments (beginning or end of the period)

Using these parameters in a formula provides a straightforward calculation of the monthly repayment amount, ensuring effective financial management.

Conclusion

Spreadsheets offer significant advantages for businesses in executing complex calculations quickly and efficiently, making them an indispensable tool in today’s data-driven economy. Understanding the application of functions like PMT, SLN, and DB enriches one's skills in financial management significantly.
Know that careful design and understanding of functions contribute greatly to effective data management and decision-making in businesses.

1. Payroll Accounting: Essential for managing employee compensation.
2. Earnings Calculation: Includes Basic Pay, DA, HRA, etc.
3. Deductions: TDS, PF, loan repayments must be monitored.
4. Depreciation Methods: Understand SLM and WDV for asset valuation.
5. Excel Functions: Utilization of PMT, SLN, and DB for financial calculations.
6. Template Design: Important for organizing payroll and accounting data.
7. Absolute vs Relative Addresses: Critical for maintaining formula integrity in spreadsheets.
8. Loan Management: Effective repayment scheduling is essential for financial health.
9. Computation of Net Salary: Involves accurate summation of earnings and deductions.
10. Versatility of Spreadsheets: Enhanced productivity and decision-making in business applications.