Question

1 Approved Answer

Posted on Oct 02, 2024

Make a copy of the 2016 AR-Original sheet and name it AR Aging Data. Place this new sheet to the right of the Gross Margin

Make a copy of the 2016 AR-Original sheet and name it AR Aging Data. Place this new sheet to the right of the Gross Margin Analysis PT sheet. Place the text "Applied Amount" in cell 11. In cell 12 create a formula that uses an appropriate function from the lookup and reference category of functions that will return the amount of cash paid on a Sale or Open Balance based on the data in the AppliedToTransactionNumber column of all Cash Receipt transactions. If there is no payment, then the formula should retum an empty string. Copy this formula down to the last Sale row. Hint: Hint: The table array in the lookup function will be a subset of the data in this worksheet (i.e.. just the needed cash receipt data at the bottom of the worksheet). H A B D E 1 Type Transaction Number Applied To Transaction Number CustNum CustNamo Transaction Date Amount 2 peningBalance 1169 1836 Runners Market 28-Oct-15 $ 14,658.98 3 bening Balance 1172 1839 Southam Runners 29-Oct-15 $ 912.00 4 peningBalance 1178 1861 Family FL 5-Nov-15 $ 20,019.02 5 peningBalance 1183 1168 Bigmart 10-Nov-15 $ 14,816.13 6 peningBalance 1186 1839 Southom Runners 11-Nov-15 S 12.116.81 7 peningBalance 1194 1839 Southem Runners 18-Nov.15 S 15.195.18 8 peningBalance 1195 1168 Bigmart 20-Nov-15 $ 23,462 23 9 peningBalance 1198 1839 Southom Runners 30-Nov-15 $ 13,618.43 10 pening Balance 1204 1160 Bigmat 4-Dec-15 S 13,464.67 11 peningBalance 1205 1839 Southam Runners 11-Dec-15 $ 14,306.15 12 peningBalance 1206 1836 Runner's Market 14-Dec-15 $ 13,464.67 13 peningBalance 1212 1861 Family Ft 21-Dec-15 $ 9,615.96 14 peningBalance 1213 1863 FN Fun 21-Dec-15 $ 12,780.27 15 peningBalance 1218 1836 Runner's Market 23-Dec-15 $ 23,293.06 16 peningBalance 1227 1168 Bigmart 28-Dec-15 $ 12,821.28 17 pening Balance 1228 1839 Southem Runners 20-Dec-15 $ 12,786.27 10 les 100124 1168 Bigmat 8-Jan-16 $ 40,300.00 19 los 100130 1838 Runner's Market 11 Jan 16 S 80,637,00 20 los 100120 1863 FIL N Fun 20-Jan-18 S 14,666.00 21 les 100132 1839 Southem Runners 23 Jan 16 5 99,800.00 22 les 100136 1861 Family F 30-Jan-16 $ 31.217.60 23 100546 1863 FIN Fun 1-Feb-16 $ 14,000.00 24 Bles 100142 1160 Bigmart 5-Feb-16 531,218.00 25 les 100163 1168 Bigmart 5- Fob.10 $ 22,000.00 20 los 100174 1863 FN Fun 8-Feb-10 5 14,661.00 27 100172 1861 Family Ft 8-Feb-16 5 10.148.00 28 los 100106 1168 Bgmart 11-Feb-10 $ 22.860.00 29 les 100196 1861 Family 13-Feb-16 $ 16.140.00 30 100215 1863 FN Fun 15-Feb-18 5 14,061.00 31 100219 1166 Bimart 19-Feb-10 $ 22,860.00 32 bis 100245 1861 Family Ft 20-Feb-10 $ 16,148.00 33 100247 1003 FN Fun 22.Feb.10 $ 14,661.80 34 100249 1836 Runner's Market 25-Feb.10 $ 40,393.00 35 100250 1100 mart 25-Feb-10 5 22,800.00 30 100270 1839 Southem Runnen 26-Feb-10 550,000.00 3 ss 100300 1036 Runner's Market 4.Mam 16 540,303.60 30 s 100310 1100 Omar 4.M16 5 22,800.00 30 los 100315 1830 Southom Runners 5- M16 $50,000.00 100320 1861 Family Ft 7.Mar 16 5 16,301.00 41 los 100102 1936 Runners Market 7. Mar 16 5 40,203,60 42 100322 1863 FANFun B.Mar 16 5 15.121.60 2016 Sales Shipped 2017 PT Gross Margin Analysis PT AR Aging Data Invoice Date Applied An Balance 28-Oct-15 #NA 29-Oct-15 5-Nov-15 10-Nov.15 11.Nov.15 18-Nov-15 20-Nov-15 30-Nov-15 4-Dec-15 11-Dec-15 14-Dec-15 21-Dec-15 21-Dec-15 23-Dec-15 28-Dec-15 28.000-15 8-Jan-16 11 Jan-16 20-Jan-16 23-Jan-16 30-Jan-16 1-Feb-10 5-Feb-16 5-Fob.18 8-Feb-10 B-Fob-16 11-Feb-16 13-Feb-16 15-Feb-18 19-Feb-16 20-Feb-10 22-Fob.10 25-Feb-10 25-Feb-18 28-Feb-16 4-Man 18 4.M16 5-Mar-10 7-Man 18 7. Man10 3-Man 10 Ready 100 Make a copy of the 2016 AR-Original sheet and name it AR Aging Data. Place this new sheet to the right of the Gross Margin Analysis PT sheet. Place the text "Applied Amount" in cell 11. In cell 12 create a formula that uses an appropriate function from the lookup and reference category of functions that will return the amount of cash paid on a Sale or Open Balance based on the data in the AppliedToTransactionNumber column of all Cash Receipt transactions. If there is no payment, then the formula should retum an empty string. Copy this formula down to the last Sale row. Hint: Hint: The table array in the lookup function will be a subset of the data in this worksheet (i.e.. just the needed cash receipt data at the bottom of the worksheet). H A B D E 1 Type Transaction Number Applied To Transaction Number CustNum CustNamo Transaction Date Amount 2 peningBalance 1169 1836 Runners Market 28-Oct-15 $ 14,658.98 3 bening Balance 1172 1839 Southam Runners 29-Oct-15 $ 912.00 4 peningBalance 1178 1861 Family FL 5-Nov-15 $ 20,019.02 5 peningBalance 1183 1168 Bigmart 10-Nov-15 $ 14,816.13 6 peningBalance 1186 1839 Southom Runners 11-Nov-15 S 12.116.81 7 peningBalance 1194 1839 Southem Runners 18-Nov.15 S 15.195.18 8 peningBalance 1195 1168 Bigmart 20-Nov-15 $ 23,462 23 9 peningBalance 1198 1839 Southom Runners 30-Nov-15 $ 13,618.43 10 pening Balance 1204 1160 Bigmat 4-Dec-15 S 13,464.67 11 peningBalance 1205 1839 Southam Runners 11-Dec-15 $ 14,306.15 12 peningBalance 1206 1836 Runner's Market 14-Dec-15 $ 13,464.67 13 peningBalance 1212 1861 Family Ft 21-Dec-15 $ 9,615.96 14 peningBalance 1213 1863 FN Fun 21-Dec-15 $ 12,780.27 15 peningBalance 1218 1836 Runner's Market 23-Dec-15 $ 23,293.06 16 peningBalance 1227 1168 Bigmart 28-Dec-15 $ 12,821.28 17 pening Balance 1228 1839 Southem Runners 20-Dec-15 $ 12,786.27 10 les 100124 1168 Bigmat 8-Jan-16 $ 40,300.00 19 los 100130 1838 Runner's Market 11 Jan 16 S 80,637,00 20 los 100120 1863 FIL N Fun 20-Jan-18 S 14,666.00 21 les 100132 1839 Southem Runners 23 Jan 16 5 99,800.00 22 les 100136 1861 Family F 30-Jan-16 $ 31.217.60 23 100546 1863 FIN Fun 1-Feb-16 $ 14,000.00 24 Bles 100142 1160 Bigmart 5-Feb-16 531,218.00 25 les 100163 1168 Bigmart 5- Fob.10 $ 22,000.00 20 los 100174 1863 FN Fun 8-Feb-10 5 14,661.00 27 100172 1861 Family Ft 8-Feb-16 5 10.148.00 28 los 100106 1168 Bgmart 11-Feb-10 $ 22.860.00 29 les 100196 1861 Family 13-Feb-16 $ 16.140.00 30 100215 1863 FN Fun 15-Feb-18 5 14,061.00 31 100219 1166 Bimart 19-Feb-10 $ 22,860.00 32 bis 100245 1861 Family Ft 20-Feb-10 $ 16,148.00 33 100247 1003 FN Fun 22.Feb.10 $ 14,661.80 34 100249 1836 Runner's Market 25-Feb.10 $ 40,393.00 35 100250 1100 mart 25-Feb-10 5 22,800.00 30 100270 1839 Southem Runnen 26-Feb-10 550,000.00 3 ss 100300 1036 Runner's Market 4.Mam 16 540,303.60 30 s 100310 1100 Omar 4.M16 5 22,800.00 30 los 100315 1830 Southom Runners 5- M16 $50,000.00 100320 1861 Family Ft 7.Mar 16 5 16,301.00 41 los 100102 1936 Runners Market 7. Mar 16 5 40,203,60 42 100322 1863 FANFun B.Mar 16 5 15.121.60 2016 Sales Shipped 2017 PT Gross Margin Analysis PT AR Aging Data Invoice Date Applied An Balance 28-Oct-15 #NA 29-Oct-15 5-Nov-15 10-Nov.15 11.Nov.15 18-Nov-15 20-Nov-15 30-Nov-15 4-Dec-15 11-Dec-15 14-Dec-15 21-Dec-15 21-Dec-15 23-Dec-15 28-Dec-15 28.000-15 8-Jan-16 11 Jan-16 20-Jan-16 23-Jan-16 30-Jan-16 1-Feb-10 5-Feb-16 5-Fob.18 8-Feb-10 B-Fob-16 11-Feb-16 13-Feb-16 15-Feb-18 19-Feb-16 20-Feb-10 22-Fob.10 25-Feb-10 25-Feb-18 28-Feb-16 4-Man 18 4.M16 5-Mar-10 7-Man 18 7. Man10 3-Man 10 Ready 100