Now that we've reached the end of the course, it's time to put what we've covered to good use, there's
no better way to solidify your knowledge of power pivot than to build a report and answer some questions
using the tools that you've learned.
This exercise file has a series of instructions with a few hints you'll be taking the data from a nearly
raw stage in Excel all the way through the process of creating the dimensional tables, the relationships,
the measures, the KPIs and finally, a power pivot pivot table.
Oh, what a tongue twister.
You'll use that to answer the questions that I'll pose in the following quiz.
The background for this exercise is this you're evaluating an insurance companies cash flow, you have
the policies that have been sold along with the claims that have been paid and the salary costs of the
personnel.
Obviously, this is a very simplified version of a real insurance data set.
However, it serves as a nice little stage for our exercise.
You need to map your data sets together and construct a cash flow analysis that's not unlike the one
we created for the grocery data.
There are a few assumptions that you'll need to make about the data to construct the proper cash flow.
You should treat the wages as paid on the date shown, but the wages should be applied to the different
insurance lines of business based upon the relative percentage of policies issued to each type.
So 50 percent of the business in a month was homeowners and 50 percent of the businesses automobile.
You would split the wages and half premium payments have a type of lag involved.
The payment is received in full three months after the policy effective date.
That's a nice little wrench for you to handle with your measure.
Finally, go ahead and ignore the claim coverage type, you should build it into your hierarchy, but
don't worry about the measures relating to it.
If you want more of a challenge, I have added some advanced assumptions you can swap in.
I will not quiz you on these and there will be a separate solution video specific for them.
For these advanced assumptions, you should assume that premium is paid in 12 equal installments for
each policy, with the payment always made on the last day of each calendar month.
In addition to that, you should construct to target cash flow measure that assumes 70 percent of premium
should be allocated for losses to be paid for claims on a daily basis.
From this, you can create a pivot chart that will compare the actual cash on hand against your target
cash on hand to see how well the projections line up.
As I said, the quiz will not ask you any questions that rely on implementing these advanced assumptions.
I would expect this exercise to take you about one to two hours as you'll likely want to revisit prior
lessons to see how something was done and make modifications to suit this particular problem.
However, this is entirely doable in under 30 minutes once you become comfortable with the tool and
even faster once you master it.
So go give it your best shot and then check out the guided solution video if you get stopped.