PDA

View Full Version : Accounting stats, month/year to date, and Feb 29

Tog
2012-Jan-02, 09:42 AM
I hope I can explain this well enough on here. The boss has an obsession with knowing statistics, and if he asks us what the % change was from October 2009, he expects us to not have t look it up. This is annoying, as he's only curious and does nothing with the information except see it as a sign of doom.

To keep this all straight, I made a spread sheet that worked really well from mid-2008 on. Now I have an issue I don't know how to resolve. Leap day.

My sheet has two data columns for each of the relevant values, one beside the other. So column A is the date from Jan 1, 2007 to Dec 31, 2015. Column B are the rooms rented, C is the Total revenue for the day, and D is the average rate per room. This repeats on columns E, F, G, and H, but the year column goes from Jan 1, 2008 to Dec 31, 2016. We need a side by side breakdown of the month, so I can't do it with just the one set of columns.

The next set are the Month to Date values for total rents, total income, % change of rents, and % change of income. The set beside that is the same but for Year to date.

My question boils down to, what do I do with Feb 29 for both the leap year, and the year after?

I can add it into the moth to date by just doubling it up vs. the year before. I'm fine with that. But for the 2009 and 2013 values, I've got an extra day with no home.

The formula for Feb 28, 2009 looks at Feb 28, 2008, as it should.
The one for Mar 1, 2009 looks at Mar 1, 2008, as it should. This leaves me with nowhere to put Feb 29, 2008. How do people that know what their doing handle this?

Do I add a phantom day into 2009 and compare Feb 28, 2009 to Feb 29,2008?

Again, I know that it doesn't matter at all for where I am, but I'd still like to know the right way to handle it.

HenrikOlsen
2012-Jan-02, 10:23 AM
People who know what they're doing wouldn't dream of comparing individual days unless it's very special days such as e.g. last shopping day before Christmas, mainly because there are many areas where weekday has a strong influence so comparing different days of the week with each other is bound to overwhelm the difference between the years.

Best days to compare Feb 29, 2008 with would probably be Feb 23, 2007 and Feb 27, 2009 since they're the last Fridays of February of those years.

Tog
2012-Jan-02, 10:32 AM
I actually have a second sheet that looks at them by day of the week, but yeah. He really wants to know how we do against last year every single night.

It's sort of nice to know that were I somewhere else, this wouldn't be an issue.

kamaz
2012-Jan-02, 06:04 PM
I'd simply ignore Feb 29, i.e. not include it in the spreadsheet at all. If you try to do anything else, you WILL run into problems.

Jim
2012-Jan-02, 06:37 PM
I'd compare Feb 29, 2012, to Feb 29, 2008. This keeps the data in the system for your day-of-the-week, monthly and annual comparisons.

Hopefully your boss isn't so dense that he'll ask you how you did compared to Feb 29, 2011. (If he does, just say, "Infinitely better" and smile.)

HenrikOlsen
2012-Jan-02, 08:53 PM
Basically this sounds like a case of "You need a number real bad? Well, here's a real bad number!".

Not understanding what to ask for means he's getting useless numbers.

publius
2012-Jan-02, 09:14 PM
Tog,

If the guy wants to be that idiotic (what do you call these people who essentially can't see the forrest for the trees of detail), just define a year over year comparison to be 365 days. That year will be different from a calendar (or fiscal, if you do that differently) year, of course.

Note what will happen if you do that. Come a leap year, on Feb. 29th, you'll be comparing that to March 1st of the previous year. You'll then have that one day "offset" until the March 1 of the next year. On Feb 28th of the that next non-leap year, you'll compare to Feb 29th of the previous year (leap), and then the next day March 1 will then compare to March 1 of the previous year and you'll be back in sync with the calendar year until the next leap year.

That of course arbitrarily defines "year over year" as exactly 365 days, which is no more or less valid than letting calendar years have a variable number of days. :)

One way to handle these people who obsess over detail without seeing the big picture is just to razzle dazzle them with the very detail they're obsessed with. But of course to do that, you've got to understand the big picture yourself. :lol:

Tog
2012-Jan-02, 09:25 PM
I went with a phantom day and comparing both the 28th and 29th on leap years to the 28th on non. This is the thing, he's offsite, but calls every night to get the numbers. He doesn't track them or monitor trends, so I could make up anything I want, but I wanted to know how to really do it. I can't let the months get out of synch though, so leap day has to be in February, along with whatever I compare it to.

Also, there is no such thing as too much detail when it comes to stats. We call other places to get their numbers and send someone over three times a week to count the cars in their parking lots to see if the numbers they tell us match. They never do, so that "means" they're lying to us despite our not changing anything based on what they say, and them not getting any advantage over us by doing so. They have also "lied" in a very specific pattern over the last two years, which means it must be part of the training program there and everyone is involved.

HenrikOlsen
2012-Jan-02, 11:04 PM
Either that or you have assigned a different meaning to the numbers than they have.

Trebuchet
2012-Jan-03, 02:42 AM
How do you deal with weekends and holidays? Does the business operate 24/7/365?

Is this a spreadsheet? I'd just be inclined to compare to today()-365.25.

Second time I've said this today, the other was in some unrelated blog:

There's an old, bad, saying: "Anything worth doing is worth doing well". Trebuchet's corollary: Some things are not worth doing, but we're forced to do them anyhow. Your task is one of those. It's not worth doing particularly well.

GeorgeLeRoyTirebiter
2012-Jan-03, 06:59 AM
A serious question: why bother with the spreadsheet at all? Since he doesn't actually do anything with the data, it would be much easier to just make up a number when asked. It wouldn't surprise me if that's how the other sites are "counting" cars.

HenrikOlsen
2012-Jan-03, 07:20 AM
Are you seriously suggesting lying to an employer about something that can be independently confirmed is a good idea?

GeorgeLeRoyTirebiter
2012-Jan-03, 07:38 AM
That depends on whether the employer is actually bright enough to verify said data.

Edit: Based on the OP, I was assuming that the boss was, at best, completely incompetent.

Tog
2012-Jan-03, 09:13 AM
The boss lives well out of the area. He's never been to the property as far as I know, and likely never will. And yes, it's 24/7/365.2422. We're a hotel. I wouldn't say he's incompetent, but he's old and set in his ways, and if he thinks something is important than it is, no matter what.

Lying about the numbers would be bad. He can't actual verify them directly, and if I were to guess something close, he'd likely never know. The numbers I have tonight will be different in a day or so because of rate changes for different things, or the people that end up not showing up. The thing is, he'll call several times a day and ask everyone what the numbers are. We all need to match.

One of the other places had a very slow night. Since there is no such thing as a fluke data point, it must be a sign that the hotel industry is coming to an end. Never mind that it's a Sunday, the day after New Year's, and the hotel draws mostly business travel. None of that matters. Being able to go back to last year and say that they did have three days in a row that were single digits around this same time of year got him off that line of thought for the night.

And while "we" don't use the data, I do. I've graphed out things like trends for each day of the week which help when it comes to scheduling the housekeepers, and I used the data from the other hotels to show that I was wrong about their locations having an effect on ours, based on the days we are each at our peak. That gives us a better idea which areas to target.

As for the other hotel lying about things, I just don't see a point. They may give us a rough estimate rather than actually look it up, but I don't see that as the same thing. One place I know does lie. No one can be that consistently at 12 rooms. I think there are far too many variables to make counting the cars in the parking lot any more accurate than the rough estimate, if that's what they do.

Anyway, I think I've got the leap day thing worked out well enough for what we need to do. Thanks for the input.

Jim
2012-Jan-03, 06:08 PM
... We call other places to get their numbers and send someone over three times a week to count the cars in their parking lots to see if the numbers they tell us match. They never do...

There was a movie made in the Fifties that used the Berlin Airlift as a background. The lead character was visiting with a German national in the German's apartment, overlooking the airpoprt. The German was watching the planes land and taking notes. He admitted he had been hired by the Russians to collect the numbers.

"But, they're published in the newspaper every day. The Russians could just look there," said the hero.

"Yes," said the German, "but the Russians think you're lying, so they pay me to get the real numbers."

Just then, a plane flew over and landed. The German made no effort to write it down. The hero told him he had missed one.

"Oh," came the reply, "that one's propaganda."

NEOWatcher
2012-Jan-03, 06:35 PM
...The thing is, he'll call several times a day and ask everyone what the numbers are...
His name isn't Quark by any chance, is it?

I monitor my gross income hourly. My hourly figures become my indicators. My indicators become my projections. And my projections based on the last twenty six hours show an unprecedented decline in profits.

If my projections hold true, in a month I'll be out of business!

DonM435
2012-Jan-10, 03:36 PM
...
And yes, it's 24/7/365.2422. We're a hotel.
...

That would really argue against comparing April 1 this year to April 1 last year -- you will often be comparing a weekend day to a business day.

At the very least, slide one timeline so that the weekends line up correctly. I'd guess that nobody could argue with that. Well, not too much.

jfribrg
2012-Jan-10, 05:27 PM
When he asks for year over year numbers for 2/29, why not tell him that there was no 2/29/2011, but the comparison with 3/1/2011 is ....