H
Hitesh Patel
I run a small business and have weekly spreadsheet that has all the
information I input manually from the printout from the cash register.
name of the weekly file is weekending date ie greenbook 09262009.xls
( Although I have 2007 I use 2003 extention so CPA can read. I have Office
2007 enterprise BTW)
Week before the file was greenbook 09192009.xls
Below is copy of the main column, sorry about formatting. What I would like
is to track sales, food costs, labor costs, sales tax, etc etc and may be
even plot a graph.
Only way I know is to create another spreadsheet and do physical links from
each individual file. This is very tedious and time consuming, if I then
want to compare vs prev years then even more so.
So can I automate it? Is there a better way. I need to do use this
spreadsheet as it is required by franchise.
1. Net 1 (Unadjusted Gross Sales)
2a. - Manual Voids
2b. - Credit Cards Rejected
3. Net 2 (Adjusted Gross Sales before Discounts)
(line 1) - (line 2a + line 2b)
4. WTD Net 2 Sales (Total Net 2 Sales for the week)
5. - Net CPN TL (Nationally printed coupons)
6. - Net AT Combo (Combo Discounts)
7. - Comps or Promos
8. - $ Discounts
9. - % Discounts
10. - Manager Meals
11. - Employee Meals
12. Net 3 (Adjusted Gross Sales - Total Discounts)
(line 3) Â (lines 5 thru 11)
13. Tax 1 (Sales Taxes)
14a. - Manual Voids Tax
14b. - Credit Card Rejected Tax
15. Tax 2 (Tax 1 - Manual Voids)
(line 13) - (line 14a + line 14b)
16. Net 3 + Tax 2 (Actual Gross Receipts)
(line 12 + line 15)
17. Issue Add Value (Gift Card Sales)
Add receitps; verify with Detail and Settlement reports
18. Paid-outs (purchases using cash from register)
- Paper Purchased (include in Wkly Paper Cost)
- Food Purchased (include in Wkly Food Cost)
- Office Supplies
- Operating Supplies
- Operating Services
- Laundry
- Maintenance
- General Ledger Payout
(Enter GL Code from Quiznos Chart of Accounts)
19. - Driver Reimbursement
20. - House Accounts (Post to Accounts Receivable)
21. Should Have (line 16 + line 17) - (lines 18 thru 20)
22. Bank Deposit Checks/Cash (AM)
Bank Deposit Checks/Cash (PM)
23. Total Credit Card Deposit
24. Gift Card (GC) Redemption Receipts
(Add receitps; verify with Detail and Settlement reports)
25. Total Deposit (line 22 + line 23 + line 24)
26. Cash Over / (Short) (line 25 - line 21)
27. Average Daily Variable Costs
This number is the same for each day of the week
Line P from Weekly Profit Planner Worksheet (pg 4)
28. Gift Card Distribution Fee (line 24 x 0.03)
29. Daily Gross Profit (line 12) - (line 27 + line 28)
30. WTD Gross Profit
Store projected to Break-even when Line 30 ≥ Line 31
31. Weekly Fixed Costs
This number is the same for each day of the week
Line R from Weekly Profit Planner Worksheet (pg 4)
32. Gift Card Net Redemptions (line 24 - line 28)
33. Gift Card Net ACH Deposit / (Deduction)
(line 32 - line 17)
34. Other Deposits
Enter GL Code ( from Quiznos Chart of Accounts)
35. Other Deposit (Credit Cards Reprocessed)
(Previous charges rejected that are now approved)
36. Total Team Labor Hours
(from QPOS Z1 Time Keeping report)
37. Avg. Hourly Wage (line 38 ÷ line 36)
38. Actual Team Labor $ Spent . ( from
QPOS Z1 Time Keeping report)
39. Management Daily Labor $
40. Total Labor $ Spent (line 38 + line 39)
(Actual Team Labor $ + Management Daily Labor $)
41. Labor Projection % (transfer from wkly schedule)
42. Labor % Calculation (line 40 ÷ line 3)
43. WTD Labor $ (Total Labor Spent for the week)
44. WTD Labor % (line 43 ÷ line 4)
45. Last WTD Net 2
(Transfer Line 4 from previous week's Daily Worksheet)
46. Difference in WTD Sales (over previous week)
(line 4 - line 45)
information I input manually from the printout from the cash register.
name of the weekly file is weekending date ie greenbook 09262009.xls
( Although I have 2007 I use 2003 extention so CPA can read. I have Office
2007 enterprise BTW)
Week before the file was greenbook 09192009.xls
Below is copy of the main column, sorry about formatting. What I would like
is to track sales, food costs, labor costs, sales tax, etc etc and may be
even plot a graph.
Only way I know is to create another spreadsheet and do physical links from
each individual file. This is very tedious and time consuming, if I then
want to compare vs prev years then even more so.
So can I automate it? Is there a better way. I need to do use this
spreadsheet as it is required by franchise.
1. Net 1 (Unadjusted Gross Sales)
2a. - Manual Voids
2b. - Credit Cards Rejected
3. Net 2 (Adjusted Gross Sales before Discounts)
(line 1) - (line 2a + line 2b)
4. WTD Net 2 Sales (Total Net 2 Sales for the week)
5. - Net CPN TL (Nationally printed coupons)
6. - Net AT Combo (Combo Discounts)
7. - Comps or Promos
8. - $ Discounts
9. - % Discounts
10. - Manager Meals
11. - Employee Meals
12. Net 3 (Adjusted Gross Sales - Total Discounts)
(line 3) Â (lines 5 thru 11)
13. Tax 1 (Sales Taxes)
14a. - Manual Voids Tax
14b. - Credit Card Rejected Tax
15. Tax 2 (Tax 1 - Manual Voids)
(line 13) - (line 14a + line 14b)
16. Net 3 + Tax 2 (Actual Gross Receipts)
(line 12 + line 15)
17. Issue Add Value (Gift Card Sales)
Add receitps; verify with Detail and Settlement reports
18. Paid-outs (purchases using cash from register)
- Paper Purchased (include in Wkly Paper Cost)
- Food Purchased (include in Wkly Food Cost)
- Office Supplies
- Operating Supplies
- Operating Services
- Laundry
- Maintenance
- General Ledger Payout
(Enter GL Code from Quiznos Chart of Accounts)
19. - Driver Reimbursement
20. - House Accounts (Post to Accounts Receivable)
21. Should Have (line 16 + line 17) - (lines 18 thru 20)
22. Bank Deposit Checks/Cash (AM)
Bank Deposit Checks/Cash (PM)
23. Total Credit Card Deposit
24. Gift Card (GC) Redemption Receipts
(Add receitps; verify with Detail and Settlement reports)
25. Total Deposit (line 22 + line 23 + line 24)
26. Cash Over / (Short) (line 25 - line 21)
27. Average Daily Variable Costs
This number is the same for each day of the week
Line P from Weekly Profit Planner Worksheet (pg 4)
28. Gift Card Distribution Fee (line 24 x 0.03)
29. Daily Gross Profit (line 12) - (line 27 + line 28)
30. WTD Gross Profit
Store projected to Break-even when Line 30 ≥ Line 31
31. Weekly Fixed Costs
This number is the same for each day of the week
Line R from Weekly Profit Planner Worksheet (pg 4)
32. Gift Card Net Redemptions (line 24 - line 28)
33. Gift Card Net ACH Deposit / (Deduction)
(line 32 - line 17)
34. Other Deposits
Enter GL Code ( from Quiznos Chart of Accounts)
35. Other Deposit (Credit Cards Reprocessed)
(Previous charges rejected that are now approved)
36. Total Team Labor Hours
(from QPOS Z1 Time Keeping report)
37. Avg. Hourly Wage (line 38 ÷ line 36)
38. Actual Team Labor $ Spent . ( from
QPOS Z1 Time Keeping report)
39. Management Daily Labor $
40. Total Labor $ Spent (line 38 + line 39)
(Actual Team Labor $ + Management Daily Labor $)
41. Labor Projection % (transfer from wkly schedule)
42. Labor % Calculation (line 40 ÷ line 3)
43. WTD Labor $ (Total Labor Spent for the week)
44. WTD Labor % (line 43 ÷ line 4)
45. Last WTD Net 2
(Transfer Line 4 from previous week's Daily Worksheet)
46. Difference in WTD Sales (over previous week)
(line 4 - line 45)