5. At the bottom of that column, use the COUNT function to indicate how many months EC was higher than B&M.

6. The company will also need to collect shipping charges from the E-commerce customers. Put the total monthly shipping charges in a separate column. If the EC sales are higher than $500,000 the shipping charge will be 2% of the sales; otherwise it will be 5% of the sales. These charges should be absolute referenced.

7. Assume that ALL the B&M sales were on account and that the payments would be received monthly for one year. The interest rate is found in G1. Use the payment function to indicate how much income the business will receive each month for the B&M sales.

8. You wish to classify your Total Monthly Sales as either SUPER, NORMAL, POOR. Sales >1,300,000 are SUPER, Sales between $650,000 and $1,300,000 are NORMAL; and Sales below $650,000 are POOR. Create a Vlookup Table for this information. Then add a new column which uses the Vlookup function to give a rating to each month.

E2 X v fx B C D 2. Veerpal Brar Corporation difference 2 Business Analysis 3 2019 4 E-commerce Brick and Mortar 5 January $568,000 $540,000 6 February $452,000 $780,000 7 March $300,000 $250,000 8 April $ 267,000 $340,000 9 May $500,670 $420,000 10 June $430,000 $230,000 11 July $510,000 $455,000 12 August $980,000 $1,005,000 13 September $780,000 $900,500 14 October $500,000 $340,000 15 November $430,000 $275,000 16 December $976,000 $780,000 17 Monthly total 18 Annual Totals 19 20 21 22 23 24 A G . 1 L M N 1 8% Shipping % 2 2% 5% 3 4 5 January 6 February 7 March 8 April 9 May 10 June 11 July 12 August 13 September 14 October 15 November 16 December 17 Annual Total 18 19 20 21 B D E F Corporation Yearly Interest Rate Monthly receivable Business Analysis amount 2020 E-commerce Brick-and-Mortar Monehly To Difference Shipping Charges $568,000 $540,000 $1,108,000 $28,000 $452,000 $780,000 $1,232,000-$328,000 $300,000 $250,000 $550,000 $50,000 $267,000 $340,000 $607,000 $73,000 $500,670 $420,000 $920,670 $80,670 $430,000 $230,000 $660,000 $200,000 $510,000 $455,000 $965,000 $55,000 $980,000 $1,005,000 $1,985,000 $25,000 $780,000 $900,500 $1,680,500 -$120,500 $500,000 $340,000 $840,000 $160,000 $430,000 $275,000 $705,000 $155,000 $976,000 $780,000 $1,756,000 $196,000 $6,693,670 $6,315,500 8