TEST II. Application. Answer each item by either: 1.) giving the complete Excel algorithm/formula; or 2.)

shortly narrating the small procedures for each small situation leading to the generation of a complete

Excel model.

1. The raw data for a cosmetics supplier's "Aging Accounts Receivable" worksheet, which screenshot is

shown as Figure 1 on the next page, are written on columns C to G, rows 11 to 16.

a. Give the algorithm for each cell to be written on B11 to B16 for the total accounts receivable

(without interest) from each client salon (3 points);

b. Give the algorithm for each cell to be written on B17 to G17 to for the grand total of

accounts receivable, and to total the not due, 1-30 days past due, 31-60 days past due, 61-

90 days past due, and the more than 90 days past due, all without interest (3 points);

c. Give the algorithm for each cell to transfer the totals in item 1-b to its proper position,

according to label, at cells D23 to D28 (3 points);

d. Give the algorithm for each cell to be written on F23 to F28 to estimate amount of

uncollectible accounts, which rates are given as cells E23 to E28 (3 points); and

e. Narrate the procedure such that the cells with monetary amounts will all have the dollar

symbol (3 points).

A B C D E F G W N 5 Date: October 31, 2012 6 7 Analysis of Accounts Receivable by Age 8 Not 1-30 31-60 61-90 Over 90 9 Yet Days Days Days Days 10 Customer Total Due Past Due Past Due Past Due Past Due 11 Attitudes For Hair $4,600 $2.600 $700 $1,300 12 Beauty Box 7,500 5,600 1.900 13 Hair Affair 10,000 3,000 3,000 2,000 2.000 14 Headliners Salon 3,800 3,800 15 Ooh La La Hair 800 800 16 Shear Paradise 3,500 1.000 640 1,400 460 17 Totals $30,200 $15,000 $6,600 $2.640 $3,400 $2,560 18 19 Estimate of Probable 20 Uncollectible Accounts Expense 21 Total Percent Total 22 Amount Uncoll. Uncoll 23 Not yet due FORMULA1 1% FORMULA7 24 1-30 days past due FORMULA2 3% FORMAULB 25 31-60 days past due FORMULA3 5% FORMUAL9 26 61-90 days past due FORMULA4 10% FORMULA10 27 Over 90 days past due FORMULA5 25% FORMULA11 28 Totals FORMULA6 FORMULA12 29 Figure 1