Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with organising info
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) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Organising data | Excel Discussion (Misc queries) | |||
Need some help in re-organising data from external source | Excel Worksheet Functions | |||
organising reciepts | Excel Discussion (Misc queries) | |||
Organising Info | Excel Discussion (Misc queries) | |||
Organising Info | Excel Discussion (Misc queries) |