Coca$-$Cola Canada buys household panel data from a market research company. A set of quarterly summary data for Ontario is included in the next worksheet in this Excel file. The total Ontario market is split to four geo$/$demographic segments: Greater Toront Area $($GTA$)$ vs$.$ Remaing Ontario $($Rem$.$ Ont.$)$ and households with children vs$.$ without children. The category of focus is carbonated soft drinks consisting of $($total$)$ Coca Cola Brands, $($total$)$ Pepsi Cola Brands, and $($total$)$ Remaining Brands for simplicity. The data has been projected to $5,500,000$ households in Ontario.

Your task is to analyze the data, identify issues and opportunities, and make marketing recommendations. Specifically, Calculate the following metrics from the data $($in terms of Liters$)$ for each of the four market segments, Total GTA, Total Remaining Ontario, and Total Ontario:

$1.$ Market Share and BDI for Total Coca Cola Brands. $(3$ marks$)$

$2.$ CDI for the Carbonated Soft Drink category. $(2$ marks$)$

$3.$ Category Penetration $\%,$ and Penetration $\%$ and Penetration Share for Total Coca Cola Brands. $(3$ marks$)$

$4.$ Share of Requirement for Total Coca Cola Brands. $(4$ marks$)$

$5.$ Usage Index for Total Coca Cola Brands. $(4$ marks$)$

Finally, based on your calculated metrics, what marketing recommendations would you make? $(4$ marks$)$

need assistance with question $3$ and what formulas to use $-$ thank you $-$ reattaching the excel data$\backslash$table$[[$Keys: Time $-$ Quarterly, Category $-$ Carbonated Soft Drinks, Brands, households,$\backslash$table$[[$Projected$],[$Household$]],,,],[$Client: Coca$-$Cola Canada, Household Buyers ${?}_{(}),$Quantities Purchased $($Liters$)],[$Geography$,$Demographic,Counts,Coca Cola,Pepsi Cola,Remaining Brands$],[$GTA$,$with children,Buyers of Coca Cola Brands only,$266805,10088194,0,0],[$GTA$,$with children,Buyers of Pepsi Cola Brands only,$281628,0,8774920,],[$GTA$,$with children,Buyers of Remaining Brands only,$29645,0,0,7114800],[$GTA$,$with children,Buyers of Coca Cola and Pepsi Cola Brands only,$474320,9596087,8994293,0],[$GTA$,$with children,Buyers of Coca Cola and Remaining Brands only,$103758,2952642,0,2134440],[$GTA$,$with children,Buyers of Pepsi Cola and Remaining Brands only,$88935,0,1974357,2276736],[$GTA$,$with children,Buyers of Coca Cola and Pepsi Cola and Remaining Brands,$44468,1968428,2193730,2703624],[$GTA$,$with children,Total Households,$1482250,,,],[$GTA$,$without children,Buyers of Coca Cola Brands only,$194040,8656610,0,0],[$GTA$,$without children,Buyers of Pepsi Cola Brands only,$254678,0,6723486,0],[$GTA$,$without children,Buyers of Remaining Brands only,$24255,0,0,6684678],[$GTA$,$without children,Buyers of Coca Cola and Pepsi Cola Brands only,$375953,7448711,6243237,0],[$GTA$,$without children,Buyers of Coca Cola and Remaining Brands only,$72765,2617115,0,1765764],[$GTA$,$without children,Buyers of Pepsi Cola and Remaining Brands only,$84893,0,1600830,1891890],[$GTA$,$without children,Buyers of Coca Cola and Pepsi Cola and Remaining Brands,$48510,1409216,1440747,2270268],[$GTA$,$without children,Total Households,$1212750,,,],[$Rem$.$ Ont.,with children,Buyers of Coca Cola Brands only,$325100,10410028,0,0],[$Rem$.$ Ont.,with children,Buyers of Pepsi Cola Brands only,$307989,0,9362866,0],[$Rem$.$ Ont.,with children,Buyers of Remaining Brands only,$17111,0,0,8685290],[$Rem$.$ Ont.,with children,Buyers of Coca Cola and Pepsi Cola Brands only,$513315,10143104,9855648,0],[$Rem$.$ Ont.,with children,Buyers of Coca Cola and Remaining Brands only,$136884,3470009,0,2587108],[$Rem$.$ Ont.,with children,Buyers of Pepsi Cola and Remaining Brands only,$136884,0,2463912,3326281],[$Rem$.$ Ont.,with children,Buyers of Coca Cola and Pepsi Cola and Remaining Brands,$34221,2669238,2956694,3880661],[$Rem$.$ Ont.,with children,Total Households,$1711050,,,],[$Rem$.$ Ont.,without children,Buyers of Coca Cola Brands only,$207851,7351344,0,0],[$Rem$.$ Ont.,without children,Buyers of Pepsi Cola Brands only,$207851,0,6279273,0],[$Rem$.$ Ont.,without children,Buyers of Remaining Brands only,$10940,0,0,5688540],[$Rem$.$ Ont.,without children,Buyers of Coca Cola and Pepsi Cola Brands only,$350064,6476184,6126120,0],[$Rem$.$ Ont.,without children,Buyers of Coca Cola and Remaining Brands only,$65637,2100384,0,1531530],[$Rem$.$ Ont.,without children,Buyers of Pepsi Cola and Remaining Brands only,$76577,0,1531530,1859715],[$Rem$.$ Ont.,without children,Buyers of Coca Cola and Pepsi Cola and Remaining Brands,$32819,1575288,1378377,1859715],[$Rem$.$ Ont.,without children,Total Households,$1093950,,,]]\_$