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.