1
00:00:00,810 --> 00:00:05,460
Now that we've reached the end of the course, it's time to put what we've covered to good use, there's

2
00:00:05,460 --> 00:00:10,980
no better way to solidify your knowledge of power pivot than to build a report and answer some questions

3
00:00:10,980 --> 00:00:12,480
using the tools that you've learned.

4
00:00:12,990 --> 00:00:18,480
This exercise file has a series of instructions with a few hints you'll be taking the data from a nearly

5
00:00:18,480 --> 00:00:24,390
raw stage in Excel all the way through the process of creating the dimensional tables, the relationships,

6
00:00:24,540 --> 00:00:28,980
the measures, the KPIs and finally, a power pivot pivot table.

7
00:00:29,200 --> 00:00:30,360
Oh, what a tongue twister.

8
00:00:31,320 --> 00:00:35,340
You'll use that to answer the questions that I'll pose in the following quiz.

9
00:00:36,370 --> 00:00:42,610
The background for this exercise is this you're evaluating an insurance companies cash flow, you have

10
00:00:42,610 --> 00:00:47,920
the policies that have been sold along with the claims that have been paid and the salary costs of the

11
00:00:47,920 --> 00:00:48,580
personnel.

12
00:00:49,030 --> 00:00:53,610
Obviously, this is a very simplified version of a real insurance data set.

13
00:00:53,950 --> 00:00:58,120
However, it serves as a nice little stage for our exercise.

14
00:00:58,660 --> 00:01:03,700
You need to map your data sets together and construct a cash flow analysis that's not unlike the one

15
00:01:03,700 --> 00:01:05,360
we created for the grocery data.

16
00:01:06,070 --> 00:01:10,570
There are a few assumptions that you'll need to make about the data to construct the proper cash flow.

17
00:01:11,080 --> 00:01:16,030
You should treat the wages as paid on the date shown, but the wages should be applied to the different

18
00:01:16,030 --> 00:01:21,690
insurance lines of business based upon the relative percentage of policies issued to each type.

19
00:01:21,940 --> 00:01:27,970
So 50 percent of the business in a month was homeowners and 50 percent of the businesses automobile.

20
00:01:28,360 --> 00:01:33,400
You would split the wages and half premium payments have a type of lag involved.

21
00:01:33,820 --> 00:01:38,410
The payment is received in full three months after the policy effective date.

22
00:01:38,710 --> 00:01:41,290
That's a nice little wrench for you to handle with your measure.

23
00:01:42,550 --> 00:01:47,950
Finally, go ahead and ignore the claim coverage type, you should build it into your hierarchy, but

24
00:01:47,950 --> 00:01:50,350
don't worry about the measures relating to it.

25
00:01:50,860 --> 00:01:55,650
If you want more of a challenge, I have added some advanced assumptions you can swap in.

26
00:01:56,560 --> 00:02:01,240
I will not quiz you on these and there will be a separate solution video specific for them.

27
00:02:02,350 --> 00:02:07,840
For these advanced assumptions, you should assume that premium is paid in 12 equal installments for

28
00:02:07,840 --> 00:02:12,730
each policy, with the payment always made on the last day of each calendar month.

29
00:02:13,540 --> 00:02:20,320
In addition to that, you should construct to target cash flow measure that assumes 70 percent of premium

30
00:02:20,650 --> 00:02:25,210
should be allocated for losses to be paid for claims on a daily basis.

31
00:02:25,780 --> 00:02:31,480
From this, you can create a pivot chart that will compare the actual cash on hand against your target

32
00:02:31,480 --> 00:02:34,680
cash on hand to see how well the projections line up.

33
00:02:35,200 --> 00:02:40,900
As I said, the quiz will not ask you any questions that rely on implementing these advanced assumptions.

34
00:02:41,740 --> 00:02:47,410
I would expect this exercise to take you about one to two hours as you'll likely want to revisit prior

35
00:02:47,410 --> 00:02:52,590
lessons to see how something was done and make modifications to suit this particular problem.

36
00:02:53,050 --> 00:02:59,080
However, this is entirely doable in under 30 minutes once you become comfortable with the tool and

37
00:02:59,080 --> 00:03:00,790
even faster once you master it.

38
00:03:01,810 --> 00:03:06,490
So go give it your best shot and then check out the guided solution video if you get stopped.
