Question

Critical Thinking 223 Critical Thinking EA7-C1 Discuss Using the IF Function The IF function is one of the most versatile functions available in Excel. Even the simplest IF function can add great benefit to a worksheet. In this chapter, you saw how the IF function can be used in a payroll register to ensure that correct earnings and withholding amounts are entered for each employee. However, the payroll register is not the only accounting-related worksheet within which the IF function can be used. Identify three types of accounting worksheets (other than the payroll register) that can benefit from the use of the IF function. Write a paragraph of at least five sentences in which you iden tify these worksheets. Write a second paragraph of at least five sentences to discuss how the IF function can be used within each worksheet and why it would be beneficial to do so. boot Doadability

Answer 1

Solution:

If condition can be used where conditional situation arises. Here are 3 situations where IF unction can be used:1

1. If function can be used to calculate the discount to be provided based on the certain amount or qty or on cash payment terms. In every business, the seller provides discount to customers if they purchase product beyond certain amount/quantity. Like, for 1-10 qty, the price of product is $30, for qty 11-25 it is $28 with a discount of $2, for qty 26 to 65 price is $25 with a further discount of $ 3, for qty 65 and above price is $22 with further disc of $ 3. or based on certain amounts like if the product purchased is more than $30000, there will be discount of 10% on the original price. Giving large no of data it is not possible to calculate each discount so we can use IF function to calculate the discount.

Using multiple IF Function, we can create a formula to check multiple conditions and perform different calculations depending on what amount range the specified quantity falls in, or what will be the discount if the amount to be paid exceeds certain limit. This can be shown with the help of one example given below.

Description	Qty(i)	Rate(ii)	Amount without discount(iii=i*ii)	Discount(iv)=IF(column 4>30000,10%,0)	Discount amount(v=iii*iv)	Amount net discount(vi=iii-iv)
Printer	2	$    5,000.00	10000	0	0	10000
Computer	3	$ 25,000.00	75000	10%	7500	67500
Camera	15	$ 15,000.00	225000	10%	22500	202500
Condition: 10% discount if amount without discount is greater than 30000

2. IF function can be used to prepare accounts receivable Aging report. So if I want to know the amounts that are past due for 30,60,90 and over 120 days, and to see the total amounts of each categories. So given a large set of data, it is difficult to calculate each and every line item. In this sheet we will calculate the days outstanding and then using if function we will bifurcate them into days and thus total amount will be calculated.

If function can be used in the debtors ageing sheet and it is very helpful. Given large set of data aging the debtors manually is very difficult. With the help of IF function it becomes very less time consuming. We can use the if function to know the total amount of debtors with 30, 60 ,90 or more than 90 days. It is sown with the help of an example.

Today's Date	28-Oct-19
					=IF(column4 row2<=30,column3 row 2,0)	=IF(AND(column 4 row 214<=60,column 4 row2>30),column 3 row 2,0)
Invoice date	Due date	amount due	Days O/s	1-30 days	31-60 days	61-90 days	>90 days
28-Jun-19	28-Jul-19	$    4,000.00	92	$                 -	$                          -	$                                   -	$ 4,000.00
12-Dec-19	11-Aug-19	$ 10,050.00	78	$                 -	$                          -	$                   10,050.00	$               -
5-Aug-19	4-Sep-19	$    6,789.00	54	$                 -	$             6,789.00	$                                   -	$               -
30-Aug-19	29-Sep-19	$    2,457.00	29	$    2,457.00	$                          -	$                                   -	$               -
4-Sep-19	4-Oct-19	$    7,890.00	24	$    7,890.00	$                          -	$                                   -	$               -
9-Sep-19	9-Oct-19	$    1,235.00	19	$    1,235.00	$                          -	$                                   -	$               -
				$ 11,582.00	$             6,789.00	$                   10,050.00	$ 4,000.00

3. If function can be used to know the over budget and under budget status. Suppose we have large sheet of data containing the details of Budgeted and actual expenses. Now we want to know the status of over budget and under budgeted data and the total amount of over budget. Here we can use if function in that work sheet.

An IF function has 2 outcomes. Either true or false. In a sheet containing details of budgeted and actual expenses we can easily know the status of expenses which are over budgeted. This can be shown with the help of an example:

Budgeted	Actual	Status=IF(column2row2>column1 row 2,"over budget","under budget")	Amount over budgeted=IF(column2row2>column1 row 2,column2row2-column1 row 2,0)
80000	95000	over budget	15000
15000	14000	under budget	0
37500	27000	under budget	0
16000	22000	over budget	6000