This Data is part of a bigger project, I need to find the Account Receivable Turnover Ratio, I think I know how to find the receivables (my function is Sales*(1-Trade Discount)) that is :Q2*(1-S2). I still need help to find the Net Sales for 2015 (is the gross sale=sales - (sales* trade Discount))? this is the question:

1. Complete the highlighted portions of the Accounts Receivable turnover ratio table included in the second worksheet (tab) in file "Superstore Data.xls" including calculating the SuperStore's A/R turnover ratios for 2015, 2016, and 2017.

The accounts receivable turnover ratio is calculated as follows:

Net Sales/((Beginning Net A/R + Ending Net A/R)/2)

Therefore, in order to calculate the A/R turnover ratio you will need to calculate net sales for the years 2015 - 2017 and net A/R as of December 31^stfor the years 2014 - 2017.

Net sales is the gross sales (after reducing the "Sales" amount in column Q by the trade discount) less an allowance for sales returns. Based on industry experience, SuperStore estimates an allowance for returns each year that is 5% of their gross sales. Superstore does not grant sales allowances to customers so you will not need to make any adjustment for sales allowances.Net A/R is gross A/R outstanding on December 31^stof each year less the Allowance for Doubtful Accounts. Superstore applies a single percentage-of-receivables rate to establish the balance in the allowance for doubtful accounts, and has used an estimate of 2% since inception. Therefore, the balance in Allowance for Doubtful Accounts should be 2% of the gross A/R outstanding on December 31^stof each year.

SuperStor... AR Turnov... + Q R S T U V Sales (in$) Quantity Trade Discount Gross Profit ARStatus AR Close Date 261.96 2 o 41.91 Paid 12/4/2016 731.94 219.58 Paid 12/4/2016 14.62 oo 6.87 Paid 8/14/2016 957.57 22.36 VNUNW 0.45 -383.03 Paid 12/15/2015 0.2 2.51 Paid 12/15/2015 48.86 o 14.16 Paid 7/17/2014 7.28 0 1.96 Paid 7/17/2014 907.15 0.2 90.71 Paid 7/17/2014 18.50 0.2 5.78 Paid 7/17/2014 114.90 AWWOUTWNOONWNYWNWNVJAWNNOWUWWAY o 34.47 Paid 7/17/2014 1706.18 0.2 85.30 Paid 7/17/2014 911.42 0.2 68.35 Paid 7/17/2014 15.55 0.2 5.44 Paid 6/27/2017 15 407.97 0.2 132.59 Paid 1/30/2017 16 Filter 68.81 0.8 -123.85 Paid 1/24/2016 2.54 0.8 -3.81 Paid 1/24/2016 665.88 13.31 Paid 1/26/2015 55.50 9.99 Paid 9/20/2014 8.56 2.48 Return 10/12/2014 213.48 0.2 16.01 Return 10/12/2014 22.72 0.2 7.38 Return 10/12/2014 19.46 5.05 Paid 12/28/2016 60.34 15.68 Paid 12/28/2016 71.37 0.3 1.01 Paid 8/28/2017 1044.63 O 240.26 Paid 11/30/2015 11.64 0.2 4.22 Paid o 3/6/2016 90.57 11.77 Paid 3/6/2016 3083.43 0.5 -1665.05 Paid 11/18/2015 9.61 0.7 -7.05 Paid 11/18/2015 124.20 0.2 15.52 Paid 11/18/2015 3.26 0.2 1.10 Paid 11/18/2015 86.30 0.2 9.70 Paid 11/18/2015 6.85 0.7 -5.71 Paid 11/18/2015 15.76 0.2 3.54 Paid 11/18/2015 29.47 0.2 9.94 Paid 11/2/2017 1097.54 0.2 123.47 Writeoff 3/15/2017 190.92 0.6 -147.96 Writeoff 3/15/2017 113.32 0.2 35.41 Paid 1/16/2016 532.39 0.32 -46.97 Paid 1/16/2016 212.05 0.3 -15.14 Paid 1/16/2016 371.16 0.2 41.75 Paid 1/16/2016 43 147.16 44 77.88 45 awers 95.61 46 45.98 17.46 NNUMOUTHNOONWANNNNA. 0.2 16.55 Paid o 11/5/2017 3.89 Paid 9/21/2016 0.2 9.56 Paid O 11/22/2017 47 48 211.96 49 45.00 50 Apple Iphone 5c Mini Lite 21.80 51 38.22 52 75.18 53 6.16 54 89.99 55 15.26 1029.95 208.56 32.40 319.41 oooooooooooooo 19.77 Paid 5/11/2016 8.20 Paid 5/11/2016 8.47 Paid 12/13/2014 4.95 Paid 8/5/2016 6.10 Paid 8/5/2016 17.96 Paid 6/22/2015 35.33 Paid 6/22/2015 2.95 Paid 6/22/2015 17.09 Paid 6/22/2015 6.25 Paid 2/12/2017 298.68 Paid 2/12/2017 52.14 Return 8/31/2016 15.55 Return 8/31/2016 7.09 Return 8/31/2016 14.56 6.98 Return 8/31/2016 30.00 3.30 Return 8/31/2016 48.48 16.36 Return 8/31/2016