Kyle Hardware Store

You are enrolled in a computer course in which the students are given projects in the local community. You have been assigned to assist Kyle Hardware Store. They want to generate a report that summarises their monthly accounts receivable balance. The monthly information below in the table is available as a trial for test purposes.

Create the spreadsheet below. Save the file with the name KYLE. (A tip - when typing money into a spreadsheet:  do not type in commas or rand signs.)

Account Number Customer Name Opening Balance Purchases Payments Returns Service Charge New Balance

3180

Moodley

2,480.00

824.62

1,100.00

422.60

3200

Smith

3,150.00

223.00

2,155.00

223.65

1589

Peer

223.15

500.00

110.00

83.50

5916

David

759.16

112.68

445.00

245.00

1123

Abraham

145.50

29.65

55.00

25.80

  1. Insert a footer with your full name and class.
  2. Enter today's date in the top left-hand corner of the spreadsheet. The date must change to always show the current date.
  3. Insert a formula for Service Charge (Note to non-accounting persons - it means interest for late payment) for Moodley: Service Charge = 25% * (Opening Balance - Payments - Returns). Copy the formula down that would complete the spreadsheet for Service Charge.
  4. Insert a formula for New Balance for Moodley: New Balance = Opening Balance + Purchases - Payment - Returns + Service Charge. Copy the formula down that would complete the spreadsheet for New Balance.
  5. Below the Purchases column insert a function that will find the highest amount of Purchases.
  6. Below the Returns column insert a function that will find the lowest amount of Returns.
  7. Insert an additional column called Type of Customer after the column Returns.
  8. In the Type of Customer column insert the IF statement that will ensure that if a person owes more than R200 at the end of the month, the words "Poor payer" shows. If a person owes less than R200 at the end of the month, the words "Good payer" shows. Copy the IF statement down to complete the spreadsheet.
  9. Centralise all the column headings.
  10. Format all currency columns to Rand.
  11. Format the column for New Balance to 0 decimal places.
  12. Sort the spreadsheet according to Customer Name in alphabetical order.
  13. In order to see the largest creditor create a pie chart showing the Names and New Balances of all Customers.
  14. Give the pie chart the legend (heading) Customer Balances.
  15. The data labels of the chart must be the names of the creditors.
  16. Print and hand in, fitted to ONE sheet, the data and pie chart.

    /16/

/43/