Insurance$-$To$-$Go is a small insurance company in Upstate NY$.$ They currently offer three different policies $(1,2,$ and $3),$ and insurance premiums are based on both the policy level and the age of the client. Insurance$-$To$-$Go has hired you to analyze their business and to determine, using the forecast, whether altering the distribution of ages in their client pool might impact the financial outcomes.

$1$ Calculate the actual versus forecasted insurance premium values for Columns E and I. The formulas are based on a flat rate of $$100$ per policy, plus an additional $$35$ for each policy level, plus $$1$ for $60\%$ of remaining years under the age of $100.$ For example, if the client is $67$ years old, then an additional $$33$x$\mathrm{.}6$ is added $(100-67$ x $\mathrm{.}60).$ I.E$.$ Policy $2$ at $67$ years old equates to $$100+$$$70+($$$33*\mathrm{.}6)=$$$189\mathrm{.}80$

$2$ Identify the number of clients with Insurance$-$To$-$Go policies. Begin by counting the total number of clients, making reference to Column C in Cell L$3.$

$3$ The company is also interested in knowing how many clients in specific age ranges own policies. Use a COUNTIF to calculate the number of policyholders ages $57$ and under and $76$ or older in L$4$ and L$6,$ respectively.

$4$ In Cell L$5,$ you will have to use the COUNTIFS function to find the number of policyholders ages $58-75.$ The COUNTIFS function allows you to have multiple criteria $($over one age, under another age$),$ whereas the COUNTIF function only interprets one criteria. See the supplemental page in the course for help!

$5$ In Cell L$7,$ do a quick check of the formulas by summing up the number of policyholders in each age range. This should be the same as the overall number reported in Cell L$3.$

$6$ Since premiums depend on the different policies, Insurance$-$To$-$Go is interested in learning the average premiums for each policy. In Table $2,$ first calculate the total average premium of all clients. Then, in Cells L$12$:L$14,$ calculate the average premium by policy type and reference the policy numbers in K$12$:K$14.$

$7$ Now calculate the total revenue overall and revenue by policy type. Check that the sum of the values in Cells M$12$:M$14$ is the same as M$11.$

$8$ Complete Tables $1$ and $2,$ with both actual and forecast data to allow for a comparative analysis.