Use of Spreadsheet in Business Applications

(d) Rate

In financial functions, Rate represents the interest rate for a period. It is one of the important parameters used in functions like PMT for loan repayment calculations.

(c) Nper

Nper means the total number of payment periods. For example, if a loan is repaid monthly for 2 years, then the total number of payments will be 24.

(b) Financial

The PMT function belongs to the financial category of functions. It is used to calculate the repayment instalment of a loan.

(c) [Ctrl]+[Shift]

This is the correct option given in the answer key of the exercise.

(d) `=AND(C4<10, D4<100)`

The AND function returns TRUE only when all given conditions are true. Here both required conditions are:

So option (d) is correct.

(c) Value_when_false

The correct arguments of the IF function are:

So value_when_false is not a valid argument.

(a) C8

In the PMT function, the first argument is Rate. Since the formula begins with `PMT(C8, …)`, the rate is in cell C8.

PV stands for Present Value. It means the current worth of a future amount of money. In simple words, it tells us what a future sum is worth today at a given rate of interest. It is used in financial calculations when we want to know how much money should be invested or considered today in place of a future amount.

For example, if a person expects to receive ₹ 1000 after some years, that future amount is not equal to ₹ 1000 today because money has a time value. The PV helps in finding today’s equivalent value of that future sum. This is why Present Value is very useful in business and financial planning.

FV means Future Value. It is used to calculate the value of an investment or amount at a future date on the basis of a constant interest rate and, where applicable, periodic payments. In other words, it helps us know how much a present investment will grow into in future.

This function is useful in financial planning, savings planning and investment analysis. For example, if ₹ 1000 is invested today at a certain rate of interest, the FV function can tell what amount it will become after 2 years, 3 years, or any other given period. Thus, FV is used when we want to estimate the maturity value or future worth of money.

The DB function is used to calculate depreciation under the Written Down Value Method (WDV), which is also called the Declining Balance method. Under this method, depreciation is charged on the current book value of the asset, not on the original cost every year.

Its purpose is to make depreciation calculation easier in spreadsheet form, especially when the depreciation amount keeps changing from year to year. The DB function uses parameters like:

So, the DB function is used when we need systematic and automatic depreciation calculation under WDV method.

To find the Future Value, we use the basic idea that money invested today increases with interest over time. FV tells us the value of present money at a future date.

Here:

Using the future value idea:

So, the Future Value after 2 years = ₹ 1210.

In spreadsheet, this can also be calculated using the FV function appropriately.

To calculate Present Value, we find the current worth of a future amount. PV tells us what a future sum is worth today.

Assuming the same rate of interest = 10% per annum, we have:

Using the present value idea:

So, the Present Value of ₹ 1000 receivable after 3 years = approximately ₹ 751.31.

This type of calculation is useful in investment and business decision-making because it helps compare present and future values properly.

In the SLM method, depreciation is calculated by dividing the total depreciable amount by the expected useful life. So, every year the same amount of depreciation is charged.

In the WDV method, depreciation is calculated on the written down value or current book value of the asset. Therefore, the depreciation amount becomes smaller every year. That is why it is also called the declining balance method.

The two basic methods of depreciation are:

Under the Straight Line Method, depreciation is charged equally every year over the useful life of the asset. The depreciable amount is the difference between acquisition cost and salvage value. This total depreciable amount is spread evenly over the expected useful life of the asset.

The spreadsheet uses the SLN function for this method. The depreciation column in the example uses:

Under the Written Down Value Method, depreciation is charged on the current book value of the asset for each period. As the book value decreases year after year, the depreciation amount also decreases. This method is also called the Declining Balance method.

The spreadsheet uses the DB function for this method. The depreciation column in the example uses:

Thus, both methods are used for charging depreciation, but they differ in the basis of calculation and the Excel function used.

Cell addresses are very important in spreadsheet because formulas depend on them. When formulas are copied from one cell to another, the behaviour of the address determines whether the formula gives the correct result or not.

A relative address changes automatically when the formula is copied to another cell. It is the default type of cell reference in spreadsheet. For example, if a formula contains `G3` and is copied down, the reference may change to `G4`, `G5`, and so on.

Basis of using relative address:
Relative address is used when the reference should change according to the new position of the formula. It is useful when similar calculations are to be repeated row by row or column by column.

An absolute address remains fixed even when the formula is copied to another cell. It is written using dollar signs, such as `$I$3` or `$F$3`.

Basis of using absolute address:
Absolute address is used when the formula should always refer to the same cell. This is needed when a cell contains a constant value such as:

For example, in the payroll spreadsheet, values such as NODM, DA rate, HRA rates, Transport Allowance, and PF rate are stored in fixed cells. Since these values are common for many records, absolute addresses are used so that the copied formulas continue to refer to the same cells.

So, the importance of these addresses is that they help formulas work correctly. The basis of choosing them is simple:

The payroll sheet uses these fixed values in cells I3 to I9 and these are meant to remain constant for all employees. The formula table also shows that columns F, G, H, I, J and N use these fixed cells through absolute references such as $I$3, $I$4, $I$5, $I$6, $I$7, $I$8 and $I$9.

These formulas and the fixed input cells are shown in the payroll formula table, and the employee data is shown in the payroll spreadsheet image.

These employee values are visible in the payroll worksheet image.

If relative addresses are used instead of absolute addresses, then while copying the formula downward, the references to the fixed cells will also shift downward. That means:

As a result, the formulas will stop using the fixed payroll rates and will start picking up wrong cells such as other rates, HRA values, employee-wise amounts, and even text headings like HRA. Therefore, the calculations of No. of Effective Days, Basic Pay Earned, DA, HRA, Transport Allowance and PF will become wrong. After a few rows, some results may even become meaningless or produce `#VALUE!` type errors because the formula may refer to text cells instead of numeric cells. This follows directly from the given formula table and the payroll layout.

So, from row 12 onward, the formula no longer uses the required fixed values.

Below, the first row remains correct because the formula starts from the original fixed cells. The problem begins when the formula is copied to the next rows.

The wrong values above are obtained by letting the references shift downward exactly as relative references do in Excel. The payroll sheet data and the formulas together show why this happens.

Absolute addresses are necessary in columns F, G, H, I, J and N because these formulas use fixed payroll values stored in cells I3 to I9. These cells contain common values like number of days in month, DA rate, HRA rates, transport allowance and PF rate, and these values must remain the same for every employee.

If relative addresses are used, then while copying the formulas downward, Excel will shift the cell references row by row. Because of this, the formulas will begin using wrong cells instead of the fixed payroll values. For example, the formula for effective days will use I4, I5, I6 instead of always using I3. Similarly, DA, HRA, transport allowance and PF formulas will start using other employee values or even text headings. This will make salary computation wrong. In some rows, the values become illogical, and in some cases the formula may return `#VALUE!` because a text cell gets used in arithmetic calculation.

Therefore, if relative addresses are used in place of absolute addresses, the payroll worksheet will not calculate salary correctly after copying the formulas. Only the first row may remain correct, but the remaining rows will show wrong earnings, wrong deductions and wrong net salary.

The worksheets mentioned in the question are as follows:

In the WDV depreciation worksheet, the Year End Date is entered in cell F3. The formula used in the Period column is:
`=IF(MONTH(C5)>3,(YEAR($F$3)-YEAR(C5)),(YEAR($F$3)-YEAR(C5))+1)`
and the Depreciation column uses the DB function:
`=DB(G5,H5,I5,J5,K5)`. The worksheet also shows two asset records — CNC Machine and Packaging Machine — with their installation dates, cost to use, salvage value, life in years, period, months in first year, year-end date in first year, and depreciation.

These figures come from the WDV spreadsheet and its formula table. The sheet clearly shows that F3 is the common Year End Date, and the period is calculated by comparing the installation year with the year of $F$3.

The absolute address $F$3 is used because F3 contains the fixed Year End Date for the whole worksheet. The formula in the Period column must use this same date for every asset row. That is why the formula is written with an absolute reference.

If relative address F3 is used instead of $F$3, then when the formula is copied downward, Excel will shift the reference automatically. So:

This causes a problem because only F3 contains the Year End Date. The shifted cells do not contain the required fixed date. Therefore, the formula will no longer calculate the period correctly. Since the DB function in column M uses the value of the Period column, the depreciation amount will also become wrong or may even return an error.

The second wrong formula is incorrect because F4 is not the fixed Year End Date cell. The formula should still refer to F3 only.

The first row may still give the correct result because the formula starts in that row itself and refers to F3. The problem starts when the formula is copied to the next row. Then F3 changes to F4, which should not happen. Because of this, the copied formula does not use the fixed Year End Date and therefore produces the wrong result.

So, the reason for using absolute address $F$3 is that the Year End Date is common for all rows. If relative address is used, Excel shifts the reference while copying, and the period calculation becomes wrong. Since depreciation under the DB function depends on this period value, the depreciation result also becomes wrong. Therefore, the copied formula must continue to use $F$3 in every row.

This worksheet should record attendance for 25 students for 10 months and calculate:

To keep the worksheet clear, one row can be used for working days of each month, and the rows below it can contain the days present for each student. Then formulas can be used to calculate percentages automatically. Spreadsheet is suitable for this because once formulas are entered, the values are updated automatically for all students.

If:

then April percentage for that student can be calculated as:

This formula should be copied for all months and all 25 students.
Here, the working days cell is kept fixed by using absolute reference, while the student attendance cell changes row-wise. This is the proper use of spreadsheet formulas.

Examples:

If monthly present values are in cells C4:L4, then:

This gives the total number of days present in all 10 months.

If total present days are in M4, and total working days for all 10 months is 226, then:

or, if the working days row is totalled in another cell, that cell can be used instead.

Example for Aditi:

Thus, the attendance worksheet contains 25 student records, month-wise attendance for 10 months, percentage of presence for each student every month, total present days, and overall percentage of presence. A separate month-wise summary also shows the total attendance and average presence percentage of the whole class. This makes the attendance record systematic, clear and easy to analyse in spreadsheet form.

A spreadsheet can be used to prepare a class timetable in a neat and systematic way. It can also help in checking whether each subject has been given the required number of lectures, tutorials and lab practical sessions. At the same time, it can calculate the total number of hours of engagement for each teacher. This makes timetable preparation easier, more accurate and easier to verify.

To prepare this worksheet properly, the timetable can be entered first in one table, and then separate summary tables can be created for:

Below is a sample timetable for one week.

This main table gives all the required timetable details in one place.

After entering the timetable, the spreadsheet should compute the number of lectures, tutorials and lab practical sessions for each subject.

This summary makes it easy to check whether a subject has been given enough lectures, tutorials and practical periods.

The spreadsheet should also calculate the total number of hours allocated to each teacher.

This summary helps in checking the workload of each teacher.

Assume the following:

Then the following formulas can be used.

To count lectures for English:

To count tutorials for English:

To count lab practicals for Computer Science:

To calculate total hours for English:

To calculate total engagement hours for Mr. Khan:

These formulas help in automatic checking and summarising of timetable data.

The spreadsheet makes timetable verification easy in the following ways:

1. It shows whether each subject has enough lectures, tutorials and lab practical sessions.

2. It shows whether any teacher has too many or too few hours.

3. It helps in balancing workload among teachers.

4. It makes editing easier, because once the main timetable is changed, the summaries are updated automatically.

5. It reduces manual errors in timetable preparation.

From the sample data above:

Thus, the spreadsheet timetable contains the full weekly schedule, subject-wise session count and teacher-wise engagement summary. It also computes the total number of lectures, tutorials and lab practical sessions for each subject and the total number of hours of engagement for each teacher. This makes the timetable systematic, accurate and easy to check.

This activity requires preparing spreadsheet worksheets similar to the business application examples and then adding two new records in each worksheet to check whether the formulas continue to work correctly. The chapter illustrates spreadsheet use through Payroll Calculation, Depreciation by WDV/DB method, Attendance Record, and Class Timetable. Therefore, worksheet-style examples can be prepared for practical verification.

For proper completion of this activity, the following worksheets can be prepared:

1. Payroll worksheet

2. Depreciation worksheet using WDV (DB function)

3. Attendance worksheet

4. Timetable worksheet

Below, two new records have been added in each worksheet and the computations have been verified.

The payroll example uses salary-related fields such as employee type, deduction days, basic pay, DA, HRA, transport allowance, gross salary, deductions and net salary. Some formulas use absolute references because common rates and values remain fixed for all employees.

For Karan:

The calculations are correct.

The WDV example uses the DB function. The worksheet includes fields such as:

These values are computed by applying the DB function for first-year depreciation on the basis of:

Thus, the depreciation amount is lower than straight-line total rate and is calculated according to the declining balance method. This matches the worksheet logic used in the WDV example.

The attendance worksheet records attendance for 25 students for 10 months and calculates monthly attendance percentage for each student, total present days, and overall percentage of presence. To verify the worksheet, two new student records can be added and the same formulas can be applied.

For Nikita:

For Rohit:

The calculations are correct.

The timetable worksheet contains the full weekly schedule, subject-wise session count and teacher-wise engagement summary. To verify the worksheet, two new timetable records can be added and the summary formulas can be checked again.

After adding these two records, English gains one more tutorial hour and Computer Science gains one more lab practical session of two hours. Similarly, the engagement hours of Mr. Rao increase by 1 hour and those of Mr. Khan increase by 2 hours. This shows that the worksheet formulas update correctly when new timetable records are added.

Thus, the required worksheets were prepared in spreadsheet style and two additional records were added in each one. After applying the same formulas and spreadsheet logic, the calculations were verified and found correct. This shows that spreadsheet formulas can be copied for new records and still produce correct results when the worksheet is designed properly with the required cell references and built-in functions.

This worksheet should be prepared to maintain the salary details of employees of M/s Opportunities Company. The spreadsheet must contain employee name, designation and basic salary, and then compute salary components automatically by using formulas. Payroll worksheets are commonly used for salary statements and salary-related calculations. In payroll accounting, salary is computed using fixed rules for earnings and deductions, and spreadsheet is useful because formulas can be copied for all employees.

The required salary components here are:

The worksheet can be prepared in the following format:

Assume:

Then the formulas can be written as:

The total salary, average salary, maximum salary and minimum salary paid by the company can be calculated from the Net Salary column.

In spreadsheet form, these can be calculated by:

Thus, the worksheet contains 50 employee records with designation, basic salary, DA, HRA, PF, gross salary, IT and net salary. The spreadsheet also calculates the total salary, average salary, maximum salary and minimum salary automatically. This makes the employee salary record systematic, accurate and easy to analyse.

In the loan repayment worksheet, the original setup takes these values as input:

Then the spreadsheet computes the Yearly Instalment Amount by using the PMT function, and the Monthly Instalment Amount is obtained by dividing the yearly instalment by 12. This layout should be followed in the loan repayment schedule table.

The PMT function is a financial function used to calculate the periodic payment for a loan. Its parameters include Rate, Nper, Pv, Fv, and Type. Here, Rate is the interest rate per period, Nper is the total number of payments, Pv is the loan amount, Fv is taken as 0, and Type = 1 means payment at the beginning of the period.

In this modified activity, instead of keeping the loan period fixed, we keep the instalment amount fixed and then find the loan period. So the worksheet must be rearranged in such a way that the known values are:

and the unknown value is the loan period. This is simply a modified financial analysis of the same loan worksheet.

The worksheet can be prepared in the following format:

In this structure:

Below is a sample modified worksheet with assumed values.

These sample records are arranged in the same spirit as the original worksheet where loan amount, rate, future value and repayment schedule are used together.

In the original worksheet, the formula used is:

This means:

So, in the modified worksheet, we enter a fixed instalment amount and then choose the period value so that the PMT formula returns that same instalment amount.

For example, if:

then for 2 years:

So, if the fixed yearly instalment is 57619.05, the loan period will be 2 years. This matches the PMT logic used in the worksheet.

Similarly, for:

if:

then the loan period is 3 years.

This verification shows that when the fixed instalment amount matches the PMT result, the corresponding number of years is the required loan period.

Once the yearly instalment is known, the monthly instalment can be calculated as:

This is the same logic used in the original worksheet, where the monthly instalment amount is computed by dividing the yearly instalment amount by 12.

For example:

So the monthly repayment amount can also be shown along with the computed period.

Thus, the modified worksheet keeps the instalment amount fixed and computes the loan period by checking which period makes the PMT result equal to the fixed instalment. The worksheet continues to use the same PMT logic, the same financial parameters, and the same loan repayment structure as the original loan repayment schedule. This makes the modified exercise suitable for loan planning and repayment analysis in spreadsheet form.