Need help with percentage algorithm

[Excelisnotmyfriend]

Good day excel wizzes,
I have absolutely no previous excel knowledge and my boss has given me a task of trying to fix our problem of overspending on hours at the store level. Here is how the problem occurs:
Corporate sends down projected hours we can use for a week. We will call these hours A
The manager plans his employees schedules around these projected hours. We will call these B
After the week is up, corporate will tell us how many hours we actually earned by products sold. These are hours C.
Rarely do hours A and C match and so B is usually under or over. What I want to do is find the average difference in % between A and C for a 12 week span. Then take that % and Aply it back to the new 'A' corporate passes down to come up with the managers new B. ultimately, this should rectify the vast difference between overage and underage in hours scheduled.
Now, let me reiterate that I really have no previous excel experience so we might have to start at ground zero. Any help would be greatly appreciated!!!
Sincerely,
ExcelIsNotMyFriend

[BadlySpelledBuoy]

Could you provide examples of hours A, B and C to give us some idea of what you're dealing with?

Also, to comply with forum rules you will have to change your thread title so something more descriptive of the problem.

BSB

[Excelisnotmyfriend]

Thanks for the prompt response.
Here is an example:
Corporate sends hours down the week prior to the tune of let's say 437 hours.
Then the manager will take those hours and schedule his employees for the week using those 437 as a guideline. Let's say he schedules 450 hours for the week.
Then at the end of the fiscal week, corporate will tell you how many hours you actually earned. Let's say because it was a slower week you only earned 427 hours. Now you didn't miss hours by only 13 hours, but by 23 hours. Do that for an entire fiscal year and you can see how overspending occurs.
Now it could happen where you bank hours too.
Let's say you are projected 437 and you schedule 450, but you ean 460. It can swing both ways, but typically is not in your favor. What I need is a formula on a Spreadsheet or 2 where a manager can simply enter his previous 12 weeks projected hours and earned ours to find the % difference. Then use that new % difference to apply to his newly passed down projected hours for the future week to come up with a realistic goal. Does that make anymore sense? Please let me know if this helps at all!
Also, how do I change the thread name?
Thanks!

[shg]

The forum rules (link in the menu bar) explain everything you need to know.

[BadlySpelledBuoy]

Have a look at the attached and see if this works for you.

12 weeks, projected and earned hours. Then the percentage difference between the two.
By averaging them out you should be able to apply that to the future weeks projected hours to give a more realistic figure to plan for.

[Excelisnotmyfriend]

thats it exactly! I know the math is pretty simplistic, but because of my tenuous grasp on excel i could have never done it! You rock! can you whip up one where you enter your projected and then enter your percentage difference to show what you should be scheduling for the week? Keep in mind that I want many different managers to use this and some will have a tough time trying to calculate there own hours to use. Again, I really do appreciate the help!
Thanks!

[BadlySpelledBuoy]

Is this what you mean? Put your projected hrs for the week in the yellow cell and the green cell will work out the value after taking into account the average percentage difference to give the number of ours that should be scheduled.

[Excelisnotmyfriend]

Yes sir! You just made my day! Thank God for forums like this and people like you because I would have been better off beating my head against the wall! Thanks again!

[BadlySpelledBuoy]

Happy to help!

Please mark the thread as "SOLVED" if you're happy you have the answer.

Take care.

BSB.