hi there if you’re watching this video it just probably one of two people even one of my friends we’ve taught about the blog or someone who looked at my CV or cover like a potential employer and seen that God blog and you know it’s like turned up here basically the gist of this is just to show you my excel skills and what I can do in Excel and the way I’ve done I’ve gone about doing this is that web client database for casino I was giving this by one of my teachers when I did my Master’s it’s got almost about forty thousand entries in it and basically you know will the tasks will give them when we’re doing this exercise which is basically to find out whether you know find out all about the casino the casino industry from the client database so basically at the revenues you know the visitations the clients exactly you know just to find out information that could be you know worthwhile irrelevant to the casino board you know so we had to create executive dashboard and so on this will had to be done in Excel so yeah that’s this looks up with the revenues for the casinos from the slot machines to the tables to you know the show’s drinks fancy dress balls it takes in all these things into account and the interesting thing about it is that Scott log entries but there were some problems within a database which had to be as like first of all taken into account something that didn’t make sense and some of a few woods need to be added so basically we went about doing that and this is the database and this is the broader actually to be honest as you can see pretty raw it’s called site IEDs there it’s got these basically each one belongs to each cited site ID is a casino we had a key so what we did was we would use a key in class but obviously i decided i wouldn’t want to use a number to be representative casinos so what we did was really clean the data but first thing i want to do is check if there was problems with it one thing i noticed was the buffett dates were a bit iffy if he affects oh and as you can see here there’s someone with day above 20 2916 me so that’s the highest birth date and that person shouldn’t be here right now so unless he’s playing you know in the future and coming back I I don’t know but yeah the debate didn’t really make sense obviously that well I noticed is this going to be problems i thought okay you know which clean the data and there’s a lot of blanks as well i’ll give you an example this 2020 well there’s some blanks here and i’m pretty sure yep and there’s someone from 2020 so there’s a lot of them those entries need to be removed and cleaned so we did that that’s over here as you can see the things i mentioned before we had the site IDs so will the blanks removed all the dodgy buffer day to remove so we basically got the site ID is linked properly each site ID has an absolute textual form text next to it so basically Silas 6 equals the bellagio we put in some additional revenue fields to basically utilize the pivot function a lot more for example we just wanted to know how much revenue was generated from each transaction on each client we could do that we create the revenue fuel and then to the basic some grabbing oil development revenue streams you needed slot table room fancy dress ball shows and others would be drinks and you know women etc so yeah we did that and we added those in and yeah that’s that’s the revenue stream then we had to do with 14 year which basically nice year formula poured out the year which they visited the casino as an age formula basically takes data puts into today’s age and gives us their age of the lion and we did this for all the entire database really did help when using the pivot function and I can show you that now as we go for the pivot tables these are the casino revenue just giving a general gist go to casinos here on the left so you can see that the MGM grand in las vegas and a Bellagio bought majority of revenue 3733 rest bit smaller revenues not hires these two these will be the kingpin no casinos top dogs this basically shows the revenue earning from 0 to 2 o-3 obviously was up and from 03 04 although there’s more it

was really overall probably down down decline and then you’ve got in some casinos and then you’ve got the revenue increase between 0 to 20 for so basically has seven percent increase from owed from there to there overall could be 3.5 a year deny you won’t look at it really up to you you’ve got the casino revenue stream string which streams and how much they’ve been hiring so the slots from tables the room to fan shows the others you know some may be doing better and others than others sorry for example Detroit does really well in terms of you know setting drinks probably at the bar but you know in terms of them whole biggest wars are poor people the tables at the Bellagio and Las Vegas as well yeah that’s Vegas e.m Jim brings another as well so yeah this gives you a good idea on you know which casinos performing well where they performing well many with the annual casino revenues how they’re doing over the years so they showed you the Bellagio is adding a lot then you know that destroy not so much in 05 because over five state was a bit dodgy and you’ve got lats Vegas and so on disrespecting some conditional formatting just showing you know the change in revenue over the years or so the blogger is there anyone who’s been going up consistently 05 wasn’t considered because dates will cease to end at the end of over 400 500 much they run it so that’s why I wasn’t really counted here what I’ve also got down here on the side of these calculated fields which are correct you know performed give me a better idea or you know how much revenue is generated from so on I show this pretty well later on but it’s quite quite interesting to do a calculated field just makes everything look what a lot more integrated plus always charter dynamics we changed at their or change their one thing I did is realize that we have a lot of clients I bring a lot of money that’s most businesses as well evaporators principle which is the 8020 rule and obviously that means that eighty percent of revenue or your profits come from twenty percent your clients so it’s really interesting well you know interesting but also vital that you look after your twenty percent that bring in most your revenue I clearly under twenty percent in here some people do the top 5% depends but and on the industry but one thing I did notice is a hundred top 109 clients which the database is comprised of almost you know for 4101 clients once I remove you do because I got that number so only a hundred and nine of them bringing a lot of the money so you really didn’t look like look after 109 so why did his device the life-lord his game up here just basically shows the customer analysis and it shows which which which client brings in how much revenue they bring internet specific casino as you can see here 1699 brings in almost fifty two percent of the revenue at Las Vegas MGM one guy brings on almost a quarter at the Bellagio you know so it’s really important that you look after these guys at which every casino they work out because they were obviously if you can look after them you’re going to have continuous growth as long as their bank accounts stay good as well obviously you can see were you having problems where 1699 is doing very well and then last vagueness but then he’s also taking he’s bringing a lot of revenue there but then he’s obviously hitting the mirage as well they they’re actually having a loss of their so you’re actually doing a free percent decline on their revenue that the college is basically sick show where they belong in the revenue categories obviously black brings the highest amount gold rings in bit less those clients so far as if wings and consider less but you really should need to look after this top because customers and you know you will have it’s just a really high sustained for amount of your revenue would consume mystic consistently keep on coming if you’re crafting top 109 and I’ll go a bit further in that a bit later but this is a loyalty plan pivot basically the same thing just in pivot table very easy to do not so complicated no it’s all very fun because revenue streams you know you can see how much revenue is coming from which area from which casino so you can see that the bellagio love is revenue comes from the table wins we did calculate fields that was quite fun to do up here in and do calculated fields and you can basically create the extra fields so we could just find out what percentage how much percentage each fulfilled was bringing in so table table wind brings a lot of the revenue brings in almost seventy percent and I think also for the glass Vegas it’s also one of the high areas where they bring most of the revenue from some do better in others in some areas i mentioned before

all good very good information then you’ve got a customer visitor to the casino visitor traffic basically just shows which is a hammock customers went in bellagio 2003 2002 obviously ensuring an increase but then obviously not much of a decrease between these two highest one would probably be the MGM Grand Detroit but does it bring in a lot of revenue you know you can always do that do the maps on the site and work that out there’s more visit seek more revenue probably not it’s Indian casino and then you’ve also got this just the annual casino member visits this basically shows how much revenue is generated by the members that you select so basically in a villager you can see that he visited 18 x and 0 2 13 and 17 04 total times and as we go down here you can see how much revenue is generated from them how many times they visited that pacificus you know so it’s quite good to know this obviously there may be one maybe some specific clients that only go to one casino and that that’s that’s always very interesting there though I think that’s been shown actually up here you know as you can see here the blacks the black clients only go only need to go to the bellagio quite a lot more than would oblige you but they don’t visit the revised law that it was in New York long as there is a treasure island so if you’re really going to care to them you may want to kiss at the casino to visit not the ones that don’t visit so this is how you can use all the information in strap you know trends and patterns for your benefit yeah so then you got the client client customer segmentation just basically where the age looping these are your clients belong to and this looks at you know shows there’s a the highest highest amount already in the 60s and above and also the 50s as well you know and how much revenue each grouping brings you know the 50s and 59 s below the revenue sort of the sixties and 69 s the least it’s 30 thing I was probably saving to buy their houses and so on but um yeah it basically shows that the old-timers are spending quite a lot of money inc like the demographic age groups you know how many they visit I have which age groups how many of them visited which casino there may be some that prefer visiting some casinos more some prefer visiting other casinos more you can see here that probably take the MGM Grand Detroit as a lot as big fan of 60s or the 60 year olds and yeah yeah you can idea then it’s basically shows a yearly count and revenue generated from each age group p.m. at which year you know maybe you can also see that maybe in certain certain age groups maybe we’ll consider of some professional some professionals you know it could be bonus period you can see if they actually spent more on those years unless years you know so it’s all very cool very fun looking at this and make it all you can make it all file a lot of pans out then you’ve got the membership age and distribution of revenue this is just basically the loyalty scheme I produce before I showed you showing you web page groupings it belonged to so there’s only six of them which belong to the 50s and above categories and then you’ve got the gold obviously that belongs to more the age groupings and silver blancs all the edge groupings you can see how much they are it’s a hundred nine of them as I mentioned before you can also see which groupings bringing out which revenue how much revenue you bring in which casino so obviously a certain casinos like I mentioned before the Bellagio MGM Grand Detroit love lyric that lower my visit to buy a black clients the black members so you can see that boom I’m born a bringer of the revenue here obviously they’ve got very high revenues in total there the casinos overall so just basically shows where your cost where your members are spending you know you can see even you could produce marketing campaign specifically for them cool thing I did it was a it shows that seventy percent of revenue generated for the casinos comes from 109 of his clients very simple formula showed me that that’s interesting so that basically means you really need to look after 109 of your clients just out 4101 which is not that many you can look after that you’ve got consistent revenue basically because it make sure they maintain and look after them and you know not to upset them because that’s where most of your money is coming from we don’t really want to make them angry so that’s that’s the deal that’s the 8020 Pareto principle you know that’s a lot of things so basically you need to look after 109 um a lot of people sometimes complain about doing this but I don’t know why but I just did this because it’s a sexy bit easy when you want to look at some information exactly

just separate all the sites for the casinos you know see where you want if you’re doing this by region you will have all the regions in one place but you don’t have them separately slow either soft on the same thing with the casinos separate the day also just a bit more easier to look at then you go the data sets without the duplicates I had to do that fun we’re going to find out the exact count of how many customers within existed in the database if you have multiple transactions obviously never have multiple player IDs so I had to remove the multiple player ideas find out how me one’s really do because I’m fine how many exactly unique customers exists then I’ve got vlookup the membership you because you basically had the membership list I wanted to find out exactly how many I mean I wanted a sign in their membership membership levels so I basically did a vlookup formula very simple and it looked up the player ID and linked it to a members list which had the members levels put on it and this is very shown here so this is basically all the player IDs and i used the vlookup formative basically extracted from here and pop it there this is the membership without duplicates you know just did this to basically see how many members alone see how they were spending amongst themselves so yeah it’s all very interesting I’ve shown a lot of pivot tables in the beginning and that’s just a good table to fun but they do make your life a bit too easy sometimes but it’s very powerful so what I did is sometimes you can’t always use pivot tables because why need to constantly refresh them too sometimes there are some things you cannot do in the pit function here you could probably do it the pivot function but i prefer doing it with the array from it because one is always constantly updated and i’m a bit more of a person who likes to look at raw raw formulas than just pure functions because pivot tables can also always get messed up as well it’s quite messy business sometimes pivot tables but i just use the sum product formula nested a lot text text formula and date formulas and so on and basically just extracts revenue for you what year on what month so if you wanted to find out the revenue 4024 with the casinos and what specific month you could do that so if you want to look at much Oh much larger got 279 you know for 2002 let’s see how I did in 2003 good oh that’s March all these updates and Oh March it did very bad you know the palladio you know but the rest of the mumps is oh very bad as well so you can see this very easily the array formulas I’ve created here I like a rain formless same thing up here so I’m product formula putting out the years nest of the year from ear formula warm oh there you go updates and obviously to copy them down there this isn’t just another player nice as I did with array formulas just wanna see what client brings in what money you can just see it like for example 1701 brings in almost sixteen thousand dollars or seventeen thousand dollars if you want to see where he’s spending it then I’ve quite a number little formula down here you know using the Sunport up and basically look you only spent at the MGM Grand Las Vegas so it’s pretty good information in that sense using formulas to find out information same thing here you know if you want to see where and what month are spending there may be some clients to spend on certain months when they get their bonuses or you know their year salaries etc just maybe Christmas for them or their birthday so you can do that really well you can see that in June he spends a lot and November he spends a lot so you can just do it seasonally you can cater TMC’s leave because by you can see which moms is spending the money in Sutton mom she’s not spending the money and then if you got there may be one client he only spends look look spends in July inspecting it makes it loss he took a hit we took a hit in July but then also in September you made a loss so it’s all very good and fine and dandy and by all means if you guys have any questions about what my formulas or where I got the database bombo means I don’t mind setting at you if you can’t i’ll put on the website so you’re free to download it there’s no copyrights attached if you got any suggestions any things i can do better i’m always looking to improve so send it send it my way to remain email don’t have facebook so not possible but you can just drop me an email when always goodbye and yeah also one more thing as well I’ve got this handy wheel walkway by using a macro always had trouble deciding where I’m going to eat so i’ll just create this we’re in a macro and you basically hit ctrl + Q and it spins the wheel it tells you where you can eat any we will do is update this down here this pie chart and it will change there and it’s all very good you can just fold it twice and it spins more so it’s all random um still fine i like macros one

of the things I’m gonna try and get better at more and more the codes up there by all means any want to modify have fun and yeah thanks for watching um thanks