Help with organising info

[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)

 

[OssieMac]

Try copying all of your data into another workbook and onto separate
worksheets for each year.

Insert 2 additional columns at the left. (Columns A and B).
In column A enter the Header "Year" and in all rows enter the year (Like
2008).
In column B enter the header "Week Ending" and enter the week ending date
for each worksheet of data copied. (The week Ending date needs to be in all
rows of data for the specific week.)

Insert some blank rows at the top of the first worksheet. (Above the column
headers). Probably 6 to 10 rows to start with but this can be made larger or
smaller later if required.

Click on the cell in column A immediately below the column header and Freeze
Panes. (See Help for how to do this.)

Click somewhere in the data and apply AutoFilter. (See Help for how).

Copy the column headers at the top of the data to row 1 in the frozen pane
section. (You will have column headers at the top of the data in the pane
that will scroll and a second set of headers in the frozen pane at the top.)

Now in the frozen pane space space between the 2 sets of column headers
insert SUBTOTAL functions to gather data after applying the AutoFilter. See
Help for all the variations of what you can get with SUBTOTAL function. Use
separate lines for Sum, Average etc. You can also apply subtotal on the same
page for other worksheets in the same workbook so if you have a worksheet for
2008 data and another for 2009 data then you can have the subtotals on
separate lines on the one worksheet.

The subtotal function for the year and week ending should be Average so that
the correct year and weekending are displayed.

Each time you alter filters the subtotals update.

You can now use the subtotals to create a chart (graph) on a separate page
and the chart will be dynamic to the data in the subtotals. Note the chart
must be on a separate worksheet because of the rows that get hidden when
AutoFilter is applied.

It is complex to try to explain all this in a few words but I think that
Autofilter used in conjunction with SUBTOTAL function is quite a good way for
the new user to analyze data. Depending on exactly what you want to chart
(graph), you can also create charts with the actual Autofilterd data rather
than the subtotals.

If you are unable to follow my instructions then perhaps you should think
about getting yourself a book on Excel and learn how to do these things. I
can't specifically recommend any but look for one that includes information
on AutoFilter and Charts.