NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. In this lab, you will: 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) Data: Excel File Lab 6-1 Alt Data.xlsx. Perform the Analysis: Refer to Lab 6-1 Alternate in the text for instructions and Lab 6-1 steps for each the of 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. 1 Customer InvoiceAmount Due Date 2 We Fry it All 269.65 7/21/21 3 We Fry it All 456.16 7/22/21 4 Taste of Thai 157.11 7/25/21 5 Grandma's Greasy Spoon 243.05 7/25/21 6 Brewed Miracles 127.75 7/26/21 7 Grandma's Greasy Spoon 416.56 7/27/21 8 Taste of Thai 492.93 7/27/21 9 Pita Pan 98.73 7/28/21 10 We Fry it All 370.26 7/30/21 11 We Fry it All 235.79 8/1/21 12 Wok Delight 96.39 8/2/21 13 One in a Million 372.13 8/2/21 14 Tequila Taste 100.78 8/4/21 15 We Fry it All 217.81 8/5/21 16 Pita Pan 320.1 8/6/21 17 Pita Pan 480.96 8/6/21 18 One in a Million 256.1 8/8/21 19 Planet of the Grapes 487.73 8/9/21 20 Grandma's Greasy Spoon 160.61 8/10/21 21 Pita Pan 450.77 8/10/21 22 Wok Delight 168.81 8/11/21 23 Wok Delight 261.41 8/11/21 24 Taste of Thai 221.42 8/12/21 25 Tequila Taste 268.95 8/13/21 26 The Godfather 220.19 8/14/21 27 The Godfather 492.03 8/14/21 206.37 475.72 250.48 270.87 96.17 130.84 299.84 480.01 77.93 188.93 327.58 401.58 456.44 28 Wok Delight 29 Tequila Taste 30 Tequila Taste 31 Brewed Miracles 32 Soup 'r Salad 33 Taste of Thai 34 Taste of Thai 35 Wok Delight 36 Pita Pan 37 Brewed Miracles 38 One in a Million 39 Tequila Taste 40 One in a Million 41 Grandma's Greasy Spoon 42 Planet of the Grapes 43 The Godfather 44 Tequila Taste 45 Thai Tap 46 The Godfather 47 Thai Tap 48 Grandma's Greasy Spoon 49 Soup 'r Salad 50 Thai Tap 51 Thai Tap 52 Planet of the Grapes 53 Soup 'r Salad 54 Thai Tap 93.14 270.11 298.01 8/15/21 8/15/21 8/16/21 8/16/21 8/17/21 8/17/21 8/17/21 8/18/21 8/19/21 8/20/21 8/20/21 8/20/21 8/20/21 8/21/21 8/21/21 8/21/21 8/21/21 8/23/21 8/23/21 8/24/21 8/25/21 8/28/21 8/28/21 8/29/21 8/30/21 8/31/21 9/1/21 423.7 248.76 260.56 393.55 202.62 164.42 207.96 97.89 238.52 419.62 170.13 240.24 146.01 146.91 265.42 251.98 374.15 129.08 180.42 218.29 83.27 297.8 363.68 449.46 55 The Godfather 56 We Fry it All 57 Planet of the Grapes 58 Thai Tap 59 We Fry it All 60 The Godfather 61 The Godfather 62 One in a Million 63 One in a Million 64 We Fry it All 65 Brewed Miracles 66 Soup 'r Salad 67 Wok Delight 68 Pita Pan 69 Taste of Thai 70 Soup 'r Salad 71 Brewed Miracles 72 We Fry it All 73 We Fry it All 74 The Godfather 75 Wok Delight 76 One in a Million 77 Planet of the Grapes 78 Brewed Miracles 79 Tequila Taste 80 One in a Million 121.66 9/1/21 9/4/21 9/4/21 9/4/21 9/5/21 9/5/21 9/6/21 9/6/21 9/6/21 9/8/21 9/8/21 9/8/21 9/8/21 9/10/21 9/10/21 9/11/21 9/11/21 9/12/21 9/15/21 9/15/21 9/16/21 9/17/21 9/20/21 9/23/21 9/24/21 9/25/21 9/27/21 463.07 386.33 435.44 124.53 293.65 333.08 113.31 86.04 256.71 153.94 121.82 346.05 81 Pita Pan 474.9 156.21 295.83 419.99 135.53 175.47 296.67 403.37 137.21 466.73 135.52 82 The Godfather 83 One in a Million 84 Thai Tap 85 Grandma's Greasy Spoon 86 Tequila Taste 87 Planet of the Grapes 88 We Fry it All 89 Thai Tap 90 Grandma's Greasy Spoon 91 Wok Delight 92 Pita Pan 93 Tequila Taste 94 Wok Delight 95 Soup 'r Salad 96 Brewed Miracles 97 Brewed Miracles 98 Pita Pan 99 Tequila Taste 100 Planet of the Grapes 101 Tequila Taste 102 Wok Delight 103 Thai Tap 104 Wok Delight 105 Brewed Miracles 106 Taste of Thai 107 Pita Pan 108 Pita Pan 210.66 235.76 279.39 243.97 9/28/21 9/28/21 9/28/21 9/29/21 9/29/21 9/29/21 9/29/21 9/30/21 9/30/21 10/3/21 10/3/21 10/3/21 10/3/21 10/4/21 10/5/21 10/5/21 10/6/21 10/6/21 10/8/21 10/8/21 10/8/21 10/9/21 10/9/21 10/10/21 10/10/21 10/10/21 10/12/21 372.58 411.63 421.4 481.53 86.54 139.95 364.57 282.43 340.19 87.35 241.31 282.74 137.64 239.22 379.87 427.37 76.99 236.81 274.98 197.56 176.1 217.37 304.32 182.14 270.09 109 Brewed Miracles 110 Grandma's Greasy Spoon 111 We Fry it All 112 Thai Tap 113 One in a Million 114 The Godfather 115 Taste of Thai 116 We Fry it All 117 Tequila Taste 118 Grandma's Greasy Spoon 119 Planet of the Grapes 120 One in a Million 121 Tequila Taste 122 Tequila Taste 123 Tequila Taste 124 Grandma's Greasy Spoon 125 Soup 'r Salad 126 Thai Tap 127 Pita Pan 128 Thai Tap 129 Brewed Miracles 130 Pita Pan 131 Soup 'r Salad 132 Taste of Thai 133 Brewed Miracles 134 Wok Delight 135 Brewed Miracles 289.5 455.29 345.41 228.17 10/12/21 10/12/21 10/13/21 10/16/21 10/16/21 10/16/21 10/18/21 10/19/21 10/19/21 10/19/21 10/20/21 10/20/21 10/20/21 10/20/21 10/21/21 10/22/21 10/23/21 10/23/21 10/23/21 10/24/21 10/24/21 10/25/21 10/25/21 10/26/21 10/29/21 10/29/21 10/30/21 137.09 365.47 367.17 116.37 351.37 233.57 316.33 378.44 108.36 246.99 331.45 337.14 228.37 108.87 406.68 501.78 489.69 146.61 413.67 367.76 331.28 351.22 450.57 137.18 136 Grandma's Greasy Spoon 137 Soup 'r Salad 138 Taste of Thai 139 One in a Million 140 Grandma's Greasy Spoon 141 Brewed Miracles 142 One in a Million 143 Taste of Thai 144 Tequila Taste 145 Planet of the Grapes 146 Planet of the Grapes 147 Thai Tap 148 Wok Delight 149 One in a Million 150 Wok Delight 151 Tequila Taste 152 Pita Pan 153 The Godfather 154 Thai Tap 155 Pita Pan 156 The Godfather 157 One in a Million 158 The Godfather 159 Planet of the Grapes 160 Taste of Thai 161 The Godfather 162 We Fry it All 478.15 231.43 326.47 343.96 412.24 319.79 174.26 190.37 433.62 461.72 434.34 10/30/21 10/31/21 11/3/21 11/3/21 11/3/21 11/4/21 11/5/21 11/6/21 11/9/21 11/10/21 11/11/21 11/11/21 11/13/21 11/13/21 11/15/21 11/15/21 11/15/21 11/15/21 11/16/21 11/17/21 11/18/21 11/18/21 11/18/21 11/19/21 11/21/21 11/22/21 11/22/21 354.16 108.52 475.93 B 112.1 105.68 110.6 372.54 232.33 352.31 355.08 471.92 487.24 319.02 408.65 102.88 125.87 163 Soup 'r Salad 164 Planet of the Grapes 165 Grandma's Greasy Spoon 166 Planet of the Grapes 167 Thai Tap 168 The Godfather 169 Grandma's Greasy Spoon 170 Thai Tap 171 One in a Million 172 Wok Delight 173 Planet of the Grapes 174 We Fry it All 175 Planet of the Grapes 176 Taste of Thai 177 Planet of the Grapes 178 Tequila Taste 179 Soup 'r Salad 180 Planet of the Grapes 181 Taste of Thai 182 One in a Million 183 The Godfather 184 The Godfather 185 Brewed Miracles 186 Pita Pan 187 Wok Delight 188 Soup 'r Salad 189 Wok Delight 334.91 11/23/21 11/24/21 11/24/21 11/25/21 11/26/21 11/27/21 11/27/21 11/27/21 11/28/21 11/29/21 11/29/21 30/21 12/2/21 12/2/21 12/2/21 12/3/21 12/3/21 12/3/21 12/6/21 12/6/21 12/6/21 12/8/21 12/8/21 12/8/21 12/11/21 12/12/21 12/12/21 459.66 78.59 204.09 218.76 91.18 125.14 436.62 87.37 257.38 394.56 387.63 339.42 469.88 469.88 278.25 189 Wok Delight 190 Taste of Thai 191 Thai Tap 192 Brewed Miracles 340.4 219.35 193 Pita Pan 266.82 327.29 194 We Fry it All 195 One in a Million 103.84 12/12/21 12/13/21 12/13/21 12/14/21 12/14/21 12/14/21 12/15/21 12/15/21 12/16/21 12/16/21 12/18/21 12/20/21 12/22/21 249.04 419.69 461.4 196 Pita Pan 197 Wok Delight 198 Brewed Miracles 199 We Fry it All 200 Thai Tap 201 We Fry it All 345.81 480.75 482.44 202 Required: 1. What is the gross amount of receivables owed? O $53,179.57 O Cannot be determined O $34,499.08 O $56,381.41 O $37,732.45 2. What is the total receivables due in the 31-60 day A/R aging bucket? es O $11,880.62 O $12,574.47 $11,499.66 O $7,986.14 3. Accountants and auditors age receivables to primarily do which task? To help estimate the total amount of gross receivables To determine the most profitable customers O To determine the most profitable products To help estimate the allowance for doubtful accounts 4. What is the total receivables due in the 151-180 day A/R aging bucket? O $11,499.66 O $3,336.51 O $7,986.14 O $9,104.01 5. In the detailed 151-180 A/R aging bucket, what is the amount owed by the customer named Brewed Miracles? $372.13 $1,331.86 O $127.75 O $659.61 NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. In this lab, you will: 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) Data: Excel File Lab 6-1 Alt Data.xlsx. Perform the Analysis: Refer to Lab 6-1 Alternate in the text for instructions and Lab 6-1 steps for each the of 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. 1 Customer InvoiceAmount Due Date 2 We Fry it All 269.65 7/21/21 3 We Fry it All 456.16 7/22/21 4 Taste of Thai 157.11 7/25/21 5 Grandma's Greasy Spoon 243.05 7/25/21 6 Brewed Miracles 127.75 7/26/21 7 Grandma's Greasy Spoon 416.56 7/27/21 8 Taste of Thai 492.93 7/27/21 9 Pita Pan 98.73 7/28/21 10 We Fry it All 370.26 7/30/21 11 We Fry it All 235.79 8/1/21 12 Wok Delight 96.39 8/2/21 13 One in a Million 372.13 8/2/21 14 Tequila Taste 100.78 8/4/21 15 We Fry it All 217.81 8/5/21 16 Pita Pan 320.1 8/6/21 17 Pita Pan 480.96 8/6/21 18 One in a Million 256.1 8/8/21 19 Planet of the Grapes 487.73 8/9/21 20 Grandma's Greasy Spoon 160.61 8/10/21 21 Pita Pan 450.77 8/10/21 22 Wok Delight 168.81 8/11/21 23 Wok Delight 261.41 8/11/21 24 Taste of Thai 221.42 8/12/21 25 Tequila Taste 268.95 8/13/21 26 The Godfather 220.19 8/14/21 27 The Godfather 492.03 8/14/21 206.37 475.72 250.48 270.87 96.17 130.84 299.84 480.01 77.93 188.93 327.58 401.58 456.44 28 Wok Delight 29 Tequila Taste 30 Tequila Taste 31 Brewed Miracles 32 Soup 'r Salad 33 Taste of Thai 34 Taste of Thai 35 Wok Delight 36 Pita Pan 37 Brewed Miracles 38 One in a Million 39 Tequila Taste 40 One in a Million 41 Grandma's Greasy Spoon 42 Planet of the Grapes 43 The Godfather 44 Tequila Taste 45 Thai Tap 46 The Godfather 47 Thai Tap 48 Grandma's Greasy Spoon 49 Soup 'r Salad 50 Thai Tap 51 Thai Tap 52 Planet of the Grapes 53 Soup 'r Salad 54 Thai Tap 93.14 270.11 298.01 8/15/21 8/15/21 8/16/21 8/16/21 8/17/21 8/17/21 8/17/21 8/18/21 8/19/21 8/20/21 8/20/21 8/20/21 8/20/21 8/21/21 8/21/21 8/21/21 8/21/21 8/23/21 8/23/21 8/24/21 8/25/21 8/28/21 8/28/21 8/29/21 8/30/21 8/31/21 9/1/21 423.7 248.76 260.56 393.55 202.62 164.42 207.96 97.89 238.52 419.62 170.13 240.24 146.01 146.91 265.42 251.98 374.15 129.08 180.42 218.29 83.27 297.8 363.68 449.46 55 The Godfather 56 We Fry it All 57 Planet of the Grapes 58 Thai Tap 59 We Fry it All 60 The Godfather 61 The Godfather 62 One in a Million 63 One in a Million 64 We Fry it All 65 Brewed Miracles 66 Soup 'r Salad 67 Wok Delight 68 Pita Pan 69 Taste of Thai 70 Soup 'r Salad 71 Brewed Miracles 72 We Fry it All 73 We Fry it All 74 The Godfather 75 Wok Delight 76 One in a Million 77 Planet of the Grapes 78 Brewed Miracles 79 Tequila Taste 80 One in a Million 121.66 9/1/21 9/4/21 9/4/21 9/4/21 9/5/21 9/5/21 9/6/21 9/6/21 9/6/21 9/8/21 9/8/21 9/8/21 9/8/21 9/10/21 9/10/21 9/11/21 9/11/21 9/12/21 9/15/21 9/15/21 9/16/21 9/17/21 9/20/21 9/23/21 9/24/21 9/25/21 9/27/21 463.07 386.33 435.44 124.53 293.65 333.08 113.31 86.04 256.71 153.94 121.82 346.05 81 Pita Pan 474.9 156.21 295.83 419.99 135.53 175.47 296.67 403.37 137.21 466.73 135.52 82 The Godfather 83 One in a Million 84 Thai Tap 85 Grandma's Greasy Spoon 86 Tequila Taste 87 Planet of the Grapes 88 We Fry it All 89 Thai Tap 90 Grandma's Greasy Spoon 91 Wok Delight 92 Pita Pan 93 Tequila Taste 94 Wok Delight 95 Soup 'r Salad 96 Brewed Miracles 97 Brewed Miracles 98 Pita Pan 99 Tequila Taste 100 Planet of the Grapes 101 Tequila Taste 102 Wok Delight 103 Thai Tap 104 Wok Delight 105 Brewed Miracles 106 Taste of Thai 107 Pita Pan 108 Pita Pan 210.66 235.76 279.39 243.97 9/28/21 9/28/21 9/28/21 9/29/21 9/29/21 9/29/21 9/29/21 9/30/21 9/30/21 10/3/21 10/3/21 10/3/21 10/3/21 10/4/21 10/5/21 10/5/21 10/6/21 10/6/21 10/8/21 10/8/21 10/8/21 10/9/21 10/9/21 10/10/21 10/10/21 10/10/21 10/12/21 372.58 411.63 421.4 481.53 86.54 139.95 364.57 282.43 340.19 87.35 241.31 282.74 137.64 239.22 379.87 427.37 76.99 236.81 274.98 197.56 176.1 217.37 304.32 182.14 270.09 109 Brewed Miracles 110 Grandma's Greasy Spoon 111 We Fry it All 112 Thai Tap 113 One in a Million 114 The Godfather 115 Taste of Thai 116 We Fry it All 117 Tequila Taste 118 Grandma's Greasy Spoon 119 Planet of the Grapes 120 One in a Million 121 Tequila Taste 122 Tequila Taste 123 Tequila Taste 124 Grandma's Greasy Spoon 125 Soup 'r Salad 126 Thai Tap 127 Pita Pan 128 Thai Tap 129 Brewed Miracles 130 Pita Pan 131 Soup 'r Salad 132 Taste of Thai 133 Brewed Miracles 134 Wok Delight 135 Brewed Miracles 289.5 455.29 345.41 228.17 10/12/21 10/12/21 10/13/21 10/16/21 10/16/21 10/16/21 10/18/21 10/19/21 10/19/21 10/19/21 10/20/21 10/20/21 10/20/21 10/20/21 10/21/21 10/22/21 10/23/21 10/23/21 10/23/21 10/24/21 10/24/21 10/25/21 10/25/21 10/26/21 10/29/21 10/29/21 10/30/21 137.09 365.47 367.17 116.37 351.37 233.57 316.33 378.44 108.36 246.99 331.45 337.14 228.37 108.87 406.68 501.78 489.69 146.61 413.67 367.76 331.28 351.22 450.57 137.18 136 Grandma's Greasy Spoon 137 Soup 'r Salad 138 Taste of Thai 139 One in a Million 140 Grandma's Greasy Spoon 141 Brewed Miracles 142 One in a Million 143 Taste of Thai 144 Tequila Taste 145 Planet of the Grapes 146 Planet of the Grapes 147 Thai Tap 148 Wok Delight 149 One in a Million 150 Wok Delight 151 Tequila Taste 152 Pita Pan 153 The Godfather 154 Thai Tap 155 Pita Pan 156 The Godfather 157 One in a Million 158 The Godfather 159 Planet of the Grapes 160 Taste of Thai 161 The Godfather 162 We Fry it All 478.15 231.43 326.47 343.96 412.24 319.79 174.26 190.37 433.62 461.72 434.34 10/30/21 10/31/21 11/3/21 11/3/21 11/3/21 11/4/21 11/5/21 11/6/21 11/9/21 11/10/21 11/11/21 11/11/21 11/13/21 11/13/21 11/15/21 11/15/21 11/15/21 11/15/21 11/16/21 11/17/21 11/18/21 11/18/21 11/18/21 11/19/21 11/21/21 11/22/21 11/22/21 354.16 108.52 475.93 B 112.1 105.68 110.6 372.54 232.33 352.31 355.08 471.92 487.24 319.02 408.65 102.88 125.87 163 Soup 'r Salad 164 Planet of the Grapes 165 Grandma's Greasy Spoon 166 Planet of the Grapes 167 Thai Tap 168 The Godfather 169 Grandma's Greasy Spoon 170 Thai Tap 171 One in a Million 172 Wok Delight 173 Planet of the Grapes 174 We Fry it All 175 Planet of the Grapes 176 Taste of Thai 177 Planet of the Grapes 178 Tequila Taste 179 Soup 'r Salad 180 Planet of the Grapes 181 Taste of Thai 182 One in a Million 183 The Godfather 184 The Godfather 185 Brewed Miracles 186 Pita Pan 187 Wok Delight 188 Soup 'r Salad 189 Wok Delight 334.91 11/23/21 11/24/21 11/24/21 11/25/21 11/26/21 11/27/21 11/27/21 11/27/21 11/28/21 11/29/21 11/29/21 30/21 12/2/21 12/2/21 12/2/21 12/3/21 12/3/21 12/3/21 12/6/21 12/6/21 12/6/21 12/8/21 12/8/21 12/8/21 12/11/21 12/12/21 12/12/21 459.66 78.59 204.09 218.76 91.18 125.14 436.62 87.37 257.38 394.56 387.63 339.42 469.88 469.88 278.25 189 Wok Delight 190 Taste of Thai 191 Thai Tap 192 Brewed Miracles 340.4 219.35 193 Pita Pan 266.82 327.29 194 We Fry it All 195 One in a Million 103.84 12/12/21 12/13/21 12/13/21 12/14/21 12/14/21 12/14/21 12/15/21 12/15/21 12/16/21 12/16/21 12/18/21 12/20/21 12/22/21 249.04 419.69 461.4 196 Pita Pan 197 Wok Delight 198 Brewed Miracles 199 We Fry it All 200 Thai Tap 201 We Fry it All 345.81 480.75 482.44 202 Required: 1. What is the gross amount of receivables owed? O $53,179.57 O Cannot be determined O $34,499.08 O $56,381.41 O $37,732.45 2. What is the total receivables due in the 31-60 day A/R aging bucket? es O $11,880.62 O $12,574.47 $11,499.66 O $7,986.14 3. Accountants and auditors age receivables to primarily do which task? To help estimate the total amount of gross receivables To determine the most profitable customers O To determine the most profitable products To help estimate the allowance for doubtful accounts 4. What is the total receivables due in the 151-180 day A/R aging bucket? O $11,499.66 O $3,336.51 O $7,986.14 O $9,104.01 5. In the detailed 151-180 A/R aging bucket, what is the amount owed by the customer named Brewed Miracles? $372.13 $1,331.86 O $127.75 O $659.61