The excel sheet below, shows different Items that are either purchased and paid up front, not paid or purchased through layby from AGRIQUIP. If Column C of the sheet shows different Item prices and Column D shows the Value Added Tax (VAT) of 16.5% and column E shows VAT of 30% that are taxed to the Items respectively. If an item is fully paid, it is then awarded 16.5% tax, if an Item is acquired through layby it is then awarded 30% tax and If an Item is not purchased, it is awarded zero TAX.
Note: Once column B is blank, the excel function should regard the tax as zero
A B C D E F
1 Staff Name Purchased Unit Price VAT (%) VAT (%) Total
2 16.5 30
3 Generator Paid 650,000.00
4 Irrigation kit Paid 250,000.00
5 Wheel barrow Not Paid 85,000.00
6 GPS kit Layby 450,000.00
a. Write an excel function for cell F3 and describe (in not more than three lines) how it can compare the item results of column C and Tax the Items accordingly to the right (correct) column tax.
(8 marks)
b. If your formula for cell F3 was auto filled to Cell F6, what will be the total amount in Cell F6, if the row for tax in column E was absolute?
(4 marks)
c. If Cell D2 was the only absolute cell in your formula, what will be the total amount in Cell F6? (4 marks)
d. What if the row of cell E2 was made absolute cell in your formula,
i. what will be the result of cell F6. (4 marks) ii. Write the excel formula that will result in cell F6 and explain why you think it will yield a true result. (4 marks)
e. Write an excel formula that could add the taxes only when the items are paid.
Explain exactly how your formula will carry out the operation. (4 marks)
