Saturday, August 24, 2013

Google Spreadsheet Create a self-updating Labor Calculator for small business

Question:

( by Xander Legg )


Hi there, I'm the General Manager of a small restaurant.  The owners and I have been trying to work in incentive based pay (such as food/labor) in addition to their hourly wages.  We want to enforce this by creating a spreadsheet that will show the labor percentage of the last 7 running days, and give them a visual aid as to how they're doing, and how close they are to hitting bonuses.  We already have a well-organized spreadsheet with Sales, Labor Dolars, etc. saved, but to keep those sheets less accessible and manipulable I'd like to create a separate spreadsheet that uses the IMPORTRANGE function. I guess my main question is, how can I show labor (Sales/Labor $) for the past 7 days in a consistent place that updates as we update the sales and labor records?

Spreadsheet:



Solution:

Have a look at the following screenshot:



I have the following formula in Cell B2:
=sum(query(C2:E;"select D order by C desc limit 7";0))

I have the following formula in Cell B3:
=sum(query(C2:E;"select E order by C desc limit 7";0))

I have the following formula in Cell G1:
=query(C1:E;"select * order by C desc limit 7";1)


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

No comments:

Post a Comment