Required:

1. Age the receivables in six 30-day buckets (similar to Lab 6-1 above), including 130 days, 3160 days, 6190 days, 91120 days, 121150 days, and 151180 days.

2. Detail the receivables from the 151180 days bucket.

Ask the Question: How can we age receivables using Excel pivottables?

Master the Data: Apply the same steps as Lab 6-1 to the Lab 6-1 Alt Data.xlsx dataset.

Software needed

• Excel
• Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

Perform the Analysis: Refer to Lab 6-1 Alternate in the text for instructions and Lab 6-1 steps for each of the lab parts.

Share the Story: Now that you have aged the receivables, the company is better able to estimate its allowance for doubtful accounts (the contra receivable accounts that reduces gross accounts receivable into net accounts receivable). As a rule, as accounts receivable age, they are less likely to be collected.

\begin{tabular}{|lrr|} \multicolumn{2}{c}{ A } & \multicolumn{1}{l|}{ B } \\ \hline Planet of the Grapes & 238.52 & 8/30/21 \\ \hline Soup 'r Salad & 419.62 & 8/31/21 \\ \hline Thai Tap & 170.13 & 9/1/21 \\ \hline The Godfather & 240.24 & 9/1/21 \\ \hline We Fry it All & 146.01 & 9/4/21 \\ \hline Planet of the Grapes & 146.91 & 9/4/21 \\ \hline Thai Tap & 265.42 & 9/4/21 \\ \hline We Fry it All & 251.98 & 9/5/21 \\ \hline The Godfather & 374.15 & 9/5/21 \\ \hline The Godfather & 129.08 & 9/6/21 \\ \hline One in a Million & 180.42 & 9/6/21 \\ \hline One in a Million & 218.29 & 9/6/21 \\ \hline We Fry it All & 83.27 & 9/8/21 \\ \hline Brewed Miracles & 297.8 & 9/8/21 \\ \hline Soup 'r Salad & 363.68 & 9/8/21 \\ \hline Wok Delight & 449.46 & 9/8/21 \\ \hline Pita Pan & 121.66 & 9/10/21 \\ \hline Taste of Thai & 463.07 & 9/10/21 \\ \hline Soup 'r Salad & 386.33 & 9/11/21 \\ \hline Brewed Miracles & 435.44 & 9/11/21 \\ \hline We Fry it All & 124.53 & 9/12/21 \\ \hline We Fry it All & 293.65 & 9/15/21 \\ \hline The Godfather & 333.08 & 9/15/21 \\ \hline Wok Delight & 113.31 & 9/16/21 \\ \hline One in a Million & 86.04 & 9/17/21 \\ \hline Planet of the Grapes & 256.71 & 9/20/21 \\ \hline \end{tabular} \begin{tabular}{|lrr|} \hline \multicolumn{1}{|c}{ A } & \multicolumn{1}{l|}{ C } \\ \hline Taste of Thai & 91.18 & 12/6/21 \\ \hline One in a Million & 125.14 & 12/6/21 \\ \hline The Godfather & 436.62 & 12/6/21 \\ \hline The Godfather & 87.37 & 12/8/21 \\ \hline Brewed Miracles & 257.38 & 12/8/21 \\ \hline Pita Pan & 394.56 & 12/8/21 \\ \hline Wok Delight & 387.63 & 12/11/21 \\ \hline Soup 'r Salad & 339.42 & 12/12/21 \\ \hline Wok Delight & 469.88 & 12/12/21 \\ \hline Taste of Thai & 278.25 & 12/13/21 \\ \hline Thai Tap & 340.4 & 12/13/21 \\ \hline Brewed Miracles & 219.35 & 12/14/21 \\ \hline Pita Pan & 266.82 & 12/14/21 \\ \hline We Fry it All & 327.29 & 12/14/21 \\ \hline One in a Million & 103.84 & 12/15/21 \\ \hline Pita Pan & 249.04 & 12/15/21 \\ \hline Wok Delight & 419.69 & 12/16/21 \\ \hline Brewed Miracles & 461.4 & 12/16/21 \\ \hline We Fry it All & 345.81 & 12/18/21 \\ \hline Thai Tap & 480.75 & 12/20/21 \\ \hline We Fry it All & 482.44 & 12/22/21 \\ \hline & & \\ \hline \end{tabular} \begin{tabular}{|lrr|} \multicolumn{1}{c}{ A } & \multicolumn{1}{c}{ C } \\ \hline Wok Delight & 340.19 & 10/9/21 \\ \hline Brewed Miracles & 87.35 & 10/10/21 \\ \hline Taste of Thai & 241.31 & 10/10/21 \\ \hline Pita Pan & 282.74 & 10/10/21 \\ \hline Pita Pan & 137.64 & 10/12/21 \\ \hline Brewed Miracles & 239.22 & 10/12/21 \\ \hline Grandma's Greasy Spoon & 379.87 & 10/12/21 \\ \hline We Fry it All & 427.37 & 10/13/21 \\ \hline Thai Tap & 76.99 & 10/16/21 \\ \hline One in a Million & 236.81 & 10/16/21 \\ \hline The Godfather & 274.98 & 10/16/21 \\ \hline Taste of Thai & 197.56 & 10/18/21 \\ \hline We Fry it All & 176.1 & 10/19/21 \\ \hline Tequila Taste & 217.37 & 10/19/21 \\ \hline Grandma's Greasy Spoon & 304.32 & 10/19/21 \\ \hline Planet of the Grapes & 182.14 & 10/20/21 \\ \hline One in a Million & 270.09 & 10/20/21 \\ \hline Tequila Taste & 289.5 & 10/20/21 \\ \hline Tequila Taste & 455.29 & 10/20/21 \\ \hline Tequila Taste & 345.41 & 10/21/21 \\ \hline Grandma's Greasy Spoon & 228.17 & 10/22/21 \\ \hline Soup 'r Salad & 137.09 & 10/23/21 \\ \hline Thai Tap & 365.47 & 10/23/21 \\ \hline Pita Pan & 367.17 & 10/23/21 \\ \hline Thai Tap & 116.37 & 10/24/21 \\ \hline Brewed Miracles & 351.37 & 10/24/21 \\ \hline \end{tabular} \begin{tabular}{|lrr|} \multicolumn{2}{c}{ A } & \multicolumn{1}{l|}{ C } \\ \hline Pita Pan & 174.26 & 11/17/21 \\ \hline The Godfather & 190.37 & 11/18/21 \\ \hline One in a Million & 433.62 & 11/18/21 \\ \hline The Godfather & 461.72 & 11/18/21 \\ \hline Planet of the Grapes & 434.34 & 11/19/21 \\ \hline Taste of Thai & 354.16 & 11/21/21 \\ \hline The Godfather & 108.52 & 11/22/21 \\ \hline We Fry it All & 475.93 & 11/22/21 \\ \hline Soup 'r Salad & 112.1 & 11/23/21 \\ \hline Planet of the Grapes & 105.68 & 11/24/21 \\ \hline Grandma's Greasy Spoon & 110.6 & 11/24/21 \\ \hline Planet of the Grapes & 372.54 & 11/25/21 \\ \hline Thai Tap & 232.33 & 11/26/21 \\ \hline The Godfather & 352.31 & 11/27/21 \\ \hline Grandma's Greasy Spoon & 355.08 & 11/27/21 \\ \hline Thai Tap & 471.92 & 11/27/21 \\ \hline One in a Million & 487.24 & 11/28/21 \\ \hline Wok Delight & 319.02 & 11/29/21 \\ \hline Planet of the Grapes & 408.65 & 11/29/21 \\ \hline We Fry it All & 102.88 & 11/30/21 \\ \hline Planet of the Grapes & 125.87 & 12/2/21 \\ \hline Taste of Thai & 334.91 & 12/2/21 \\ \hline Planet of the Grapes & 459.66 & 12/2/21 \\ \hline Tequila Taste & 78.59 & 12/3/21 \\ \hline Soup 'r Salad & 204.09 & 12/3/21 \\ \hline Planet of the Grapes & 218.76 & 12/3/21 \\ \hline \end{tabular} \begin{tabular}{|lrl|} \hline \multicolumn{2}{c}{ A } & \multicolumn{1}{l|}{ B } \\ \hline Brewed Miracles & 153.94 & 9/23/21 \\ \hline Tequila Taste & 121.82 & 9/24/21 \\ \hline One in a Million & 346.05 & 9/25/21 \\ \hline Pita Pan & 474.9 & 9/27/21 \\ \hline The Godfather & 156.21 & 9/28/21 \\ \hline One in a Million & 295.83 & 9/28/21 \\ \hline Thai Tap & 419.99 & 9/28/21 \\ \hline Grandma's Greasy Spoon & 135.53 & 9/29/21 \\ \hline Tequila Taste & 175.47 & 9/29/21 \\ \hline Planet of the Grapes & 296.67 & 9/29/21 \\ \hline We Fry it All & 403.37 & 9/29/21 \\ \hline Thai Tap & 137.21 & 9/30/21 \\ \hline Grandma's Greasy Spoon & 466.73 & 9/30/21 \\ \hline Wok Delight & 135.52 & 10/3/21 \\ \hline Pita Pan & 210.66 & 10/3/21 \\ \hline Tequila Taste & 235.76 & 10/3/21 \\ \hline Wok Delight & 279.39 & 10/3/21 \\ \hline Soup 'r Salad & 243.97 & 10/4/21 \\ \hline Brewed Miracles & 372.58 & 10/5/21 \\ \hline Brewed Miracles & 411.63 & 10/5/21 \\ \hline Pita Pan & 421.4 & 10/6/21 \\ \hline Tequila Taste & 481.53 & 10/6/21 \\ \hline Planet of the Grapes & 86.54 & 10/8/21 \\ \hline Tequila Taste & 139.95 & 10/8/21 \\ \hline Wok Delight & 284.57 & 10/8/21 \\ \hline Thai Tap & 21n10 & 10/9/21 \\ \hline inln nnlirht & 1n/0/21 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline A & B & C & D & F & G \\ \hline Customer & InvoiceAmount & Due Date & & Today's Date & 12/31/21 \\ \hline We Fry it All & 269.65 & 7/21/21 & & & \\ \hline We Fry it All & 456.16 & 7/22/21 & & & \\ \hline Taste of Thai & 157.11 & 7/25/21 & & & \\ \hline Grandma's Greasy Spoon & 243.05 & 7/25/21 & & & \\ \hline Brewed Miracles & 127.75 & 7/26/21 & & & \\ \hline Grandma's Greasy Spoon & 416.56 & 7/27/21 & & & \\ \hline Taste of Thai & 492.93 & 7/27/21 & & & \\ \hline Pita Pan & 98.73 & 7/28/21 & & & \\ \hline We Fry it All & 370.26 & 7/30/21 & & & \\ \hline We Fry it All & 235.79 & 8/1/21 & & & \\ \hline Wok Delight & 96.39 & 8/2/21 & & & \\ \hline One in a Million & 372.13 & 8/2/21 & & & \\ \hline Tequila Taste & 100.78 & 8/4/21 & & & \\ \hline We Fry it All & 217.81 & 8/5/21 & & & \\ \hline Pita Pan & 320.1 & 8/6/21 & & & \\ \hline Pita Pan & 480.96 & 8/6/21 & & & \\ \hline One in a Million & 256.1 & 8/8/21 & & & \\ \hline Planet of the Grapes & 487.73 & 8/9/21 & & & \\ \hline Grandma's Greasy Spoon & 160.61 & 8/10/21 & & & \\ \hline Pita Pan & 450.77 & 8/10/21 & & & \\ \hline Wok Delight & 168.81 & 8/11/21 & & & \\ \hline Wok Delight & 261.41 & 8/11/21 & & & \\ \hline Taste of Thai & 221.42 & 8/12/21 & & & \\ \hline Tequila Taste & 268.95 & 8/13/21 & & & \\ \hline \end{tabular} \begin{tabular}{|lrl|} \multicolumn{2}{c}{ A } & \multicolumn{1}{l}{ B } \\ \hline The Godfather & 220.19 & 8/14/21 \\ \hline The Godfather & 492.03 & 8/14/21 \\ \hline Wok Delight & 206.37 & 8/15/21 \\ \hline Tequila Taste & 475.72 & 8/15/21 \\ \hline Tequila Taste & 250.48 & 8/16/21 \\ \hline Brewed Miracles & 270.87 & 8/16/21 \\ \hline Soup 'r Salad & 96.17 & 8/17/21 \\ \hline Taste of Thai & 130.84 & 8/17/21 \\ \hline Taste of Thai & 299.84 & 8/17/21 \\ \hline Wok Delight & 480.01 & 8/18/21 \\ \hline Pita Pan & 77.93 & 8/19/21 \\ \hline Brewed Miracles & 188.93 & 8/20/21 \\ \hline One in a Million & 327.58 & 8/20/21 \\ \hline Tequila Taste & 401.58 & 8/20/21 \\ \hline One in a Million & 456.44 & 8/20/21 \\ \hline Grandma's Greasy Spoon & 93.14 & 8/21/21 \\ \hline Planet of the Grapes & 270.11 & 8/21/21 \\ \hline The Godfather & 298.01 & 8/21/21 \\ \hline Tequila Taste & 423.7 & 8/21/21 \\ \hline Thai Tap & 248.76 & 8/23/21 \\ \hline The Godfather & 260.56 & 8/23/21 \\ \hline Thai Tap & 393.55 & 8/24/21 \\ \hline Grandma's Greasy Spoon & 202.62 & 8/25/21 \\ \hline Soup 'r Salad & 164.42 & 8/28/21 \\ \hline Thai Tap & 207.96 & 8/28/21 \\ \hline Thai Tap & 97.89 & 8/29/21 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline A & B & C \\ \hline Brewed Miracles & 351.37 & 10/24/21 \\ \hline Pita Pan & 233.57 & 10/25/21 \\ \hline Soup 'r Salad & 316.33 & 10/25/21 \\ \hline Taste of Thai & 378.44 & 10/26/21 \\ \hline Brewed Miracles & 108.36 & 10/29/21 \\ \hline Wok Delight & 246.99 & 10/29/21 \\ \hline Brewed Miracles & 331.45 & 10/30/21 \\ \hline Grandma's Greasy Spoon & 337.14 & 10/30/21 \\ \hline Soup 'r Salad & 228.37 & 10/31/21 \\ \hline Taste of Thai & 108.87 & 11/3/21 \\ \hline One in a Million & 406.68 & 11/3/21 \\ \hline Grandma's Greasy Spoon & 501.78 & 11/3/21 \\ \hline Brewed Miracles & 489.69 & 11/4/21 \\ \hline One in a Million & 146.61 & 11/5/21 \\ \hline Taste of Thai & 413.67 & 11/6/21 \\ \hline Tequila Taste & 367.76 & 11/9/21 \\ \hline Planet of the Grapes & 331.28 & 11/10/21 \\ \hline Planet of the Grapes & 351.22 & 11/11/21 \\ \hline Thai Tap & 450.57 & 11/11/21 \\ \hline Wok Delight & 137.18 & 11/13/21 \\ \hline One in a Million & 478.15 & 11/13/21 \\ \hline Wok Delight & 231.43 & 11/15/21 \\ \hline Tequila Taste & 326.47 & 11/15/21 \\ \hline Pita Pan & 343.96 & 11/15/21 \\ \hline The Godfather & 412.24 & 11/15/21 \\ \hline Thai Tap & 319.79 & 11/16/21 \\ \hline \end{tabular}