+ Reply to Thread
Results 1 to 11 of 11

How to manage and reference ongoing sales data, i.e. one huge file or reference old ones?

  1. #1
    Registered User
    Join Date
    02-28-2022
    Location
    New York
    MS-Off Ver
    Office 365 Mac/iOS/PC
    Posts
    5

    How to manage and reference ongoing sales data, i.e. one huge file or reference old ones?

    This is a very basic question. I am working on a sheet for a bakery, it lists all of the products the bakery sells and the data is how many of each was made that day, and how many of each were left over at the end of the day (or if it sold out then it notes down just for manual review later on the time of day it sold out). The time it is sold out is just so I can look at it later and manually decide "hey this product sold out at 11am, I'm going to have to decide to bake a lot more each day until we have leftovers". This leftover data then informs how much to bake of a particular product. The sales staff can enter the leftover/when sold out data straight into the shared spreadsheet themselves.

    For example if last Wednesday 82 chocolate donuts were made and 7 were left over at the end of the day, then for the bake list I want to make sure we bake 75 chocolate donuts this week, plus 2 as a buffer. So the formula would be something like (B12-C12+2), where I've filled in the relevant data. Then next Wednesday I baked 77 and had 3 leftover, so I would then bake 76 the next Wednesday and so on.

    I get this basic stuff no problem, basic formulas, how to reference other cells and cells in other sheets. What I don't know is how do I record that data using the same file week after week. If I keep on adding data each one to one file then the data will keep getting longer and longer and eventually my staff will need to scroll through lots of old data just to add the daily sales data. If I have a new spreadsheet file for each day then I need to reference a sheet from a week ago somehow. This is what I am a total noob at. I can only imagine how to set a project up like this once, rather than for a system that is endlessly useful.

    At the moment I'm copying and pasting the same blank template each week in Dropbox and copying and pasting the cells between this weeks file and last weeks file by hand to get it ready every day, but I know there has to be a better way. I'm just too much of a noob to know what it is.

    EDIT:

    I have a file here showing the kind of file I am working with. It's quite messy and I was planning on redoing it from scratch. I've tried to remove identifying information and just left two rows. The third sheet, decorate sheet, is used because we have like Chocolate Iced Donut and Strawberry Iced Donut, and they are all baked in one store, decorated in that store, then some are trucked to the other store. So for example bake 200 donuts, 90 of them put chocolate icing on them (and truck 50 of them to the Queens store), 110 of them put strawberry icing on them (and truck 40 of them to the Queens store). And for the production sheets the "to make" column is manually entered, with "suggestion" the total number made the same day the week before, minus the number left unsold at the end of the day, plus 2 more (because we want a few left over rather than running out). Temperature is noted because we find daily temperature affects sales so if it is unusually hot last week then that might create unusual sales data we need to be aware of.

    I understand this is all a mess but I didn't originally make this system, I just need to try and fix it.
    Attached Files Attached Files
    Last edited by ExcellentMonster; 03-02-2022 at 10:05 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    I am a fan of keeping all old data. What you could do is hide all the data older than, say, 6 months, simply by highlighting those rows and hiding them. The data is still there, is still available should you need it, but is no longer visible (unless you UNhide those rows), and no longer "in the way"
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    If you keep a weekly or monthly workbook with the same column heads AND in the same folder, you could use Power Query to merge them all together for a report. You would simply add the Excel files to this folder and run the report on the folder. Any new files in the folder would be included...

    See https://support.microsoft.com/en-us/...el%20workbooks. for a start.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    02-28-2022
    Location
    New York
    MS-Off Ver
    Office 365 Mac/iOS/PC
    Posts
    5

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    Hiding the old data is a good idea. All the old data would be right there in the same file just in case it is ever needed.

    I still don't know how I would set it up though. Say Sheet 1 is the list of all products, with the data being the starting inventory, inventory at the end of the day, and the time it sold out if inventory at the end of the day is zero. This will be for sales staff to fill out at the end of the day and as things go out of stock. Ideally this form is as simple as possible because this will probably be the first spreadsheet they have ever directly used in their lives (at the moment they are filling the printed spreadsheet in by hand).

    Sheet 2 will take that data and tell the early morning kitchen staff how much of each product to make before opening the next day.

    So my noob question is suppose I wanted Sheet 1 to have all days hidden except the current day. Would I have to then at the end of each night or start of each day hide the previous day and unhide the next day?

    And for Sheet 2, is it possible for it to show the previous week on the same weekday automatically, or would I also each night have to manually set it to reference that particular day?

    I guess it wouldn't take too much time to hide and unhide a column on Sheet 1 every day. And while I'm at it also changing which column Sheet 2 references from Sheet 1 manually as well. I'd just have to open the file and change a J to a K (for example) in a single formula and then select and drag that cell down to update all the other cells in the same column.
    Last edited by ExcellentMonster; 03-01-2022 at 02:17 AM.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    ExcellentMonster: "At the moment I'm copying and pasting the same blank template each week..."
    Hello and Welcome to Excel Forum.
    The above statement suggests that there is a spreadsheet(s) that we could look at and perhaps help automate.
    Information as to how to attach an Excel file to your next post are in the banner at the top of the page.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    02-28-2022
    Location
    New York
    MS-Off Ver
    Office 365 Mac/iOS/PC
    Posts
    5

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    Good idea. I have attached a file that represents what is being used at the moment, as well as an edit to the original post with information about the file.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    In most cases it appears that the To Make column could be filled using: =G5
    This breaks when looking at the Eclairs on the Queens production sheet where the Suggestion is 53 but the To Make is 10. Is there a reason?
    As you are planning to redo, perhaps something along the lines of the data set shown in columns J:Q on the Queens Production sheet.
    Note that column Q is populated using: =O2-P2+2
    Note that starting with row six column O is populated using: =INDEX(Q$2:Q5,AGGREGATE(14,6,(ROW(Q$2:Q5)-ROW(Q$1))/(L$2:L5=L6)/(N$2:N5=N6),1))
    Note that I didn't see that the time had made a difference in the calculation of row five on the Brooklyn Production sheet, so I didn't include time in the proposed table.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    02-28-2022
    Location
    New York
    MS-Off Ver
    Office 365 Mac/iOS/PC
    Posts
    5

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    > In most cases it appears that the To Make column could be filled using: =G5 This breaks when looking at the Eclairs on the Queens production sheet where the Suggestion is 53 but the To Make is 10. Is there a reason?

    That was just a mistake. And yes most of the time the suggested number is exactly what you want, but sometimes you'll want to override it like if you knew last Wednesday was the last week of school before holidays, and that today kids are out of school so twice the number of donuts will probably be sold.

    > Note that starting with row six column O is populated using: =INDEX(Q$2:Q5,AGGREGATE(14,6,(ROW(Q$2:Q5)-ROW(Q$1))/(L$2:L5=L6)/(N$2:N5=N6),1))

    Thank you for providing that. I will have to spend some time thinking about what that is doing.

    > Note that I didn't see that the time had made a difference in the calculation of row five on the Brooklyn Production sheet, so I didn't include time in the proposed table.

    The is just a manual reminder that they sold out early, so the number of items sold that day (all of them) may not be useful. If 50 donuts sold out in the middle of the lunch rush and there were no more donuts for the rest of the day then no formula can tell you how many you will need. You just need to think about what time they sold out and how many more you probably would have sold while mentally factoring in that you sell 20 times as many per hour during the lunch rush. The sales people note down when it sold out, hopefully they remember to do it anyway.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    I had overlooked that the formula would need to reference the same day of the week from the past week.
    Please modify the formula in O6 to read as follows and then copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    One way to find out what a formula is doing is to utilize the Evaluate Formula feature on the Formulas tab.
    I'll be away from my computer until at least sometime tomorrow afternoon, however there are other contributors here that may be able to help if you have questions/comments.

  10. #10
    Registered User
    Join Date
    02-28-2022
    Location
    New York
    MS-Off Ver
    Office 365 Mac/iOS/PC
    Posts
    5

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    Unfortunately Evaluate Formula isn't available on Excel for Mac. I'll need to dust off the old PC to try that out.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to manage and reference ongoing sales data, i.e. one huge file or reference old on

    Did not know that the Mac version lacked the Evaluate Formula feature.
    Another strategy would be to select the segment (L$2:L5=L6) and press the F9 key and then Ctrl + z.
    Next select (L$2:L5=L6)/(N$2:N5=N6) and again press the F9 key and then Ctrl + z
    Continue the process with (L$2:L5=L6)/(N$2:N5=N6)/(WEEKDAY(J$2:J5)=WEEKDAY(J6)) and then AGGREGATE(14,6,(ROW(Q$2:Q5)-ROW(Q$1))/(L$2:L5=L6)/(N$2:N5=N6)/(WEEKDAY(J$2:J5)=WEEKDAY(J6)),1)
    Looking at this again I am going to suggest three things.
    1. Paste the following into cell O2 and copy down: =IF(R2="",INDEX(Q$1:Q1,AGGREGATE(14,6,ROW(Q$1:Q1)/(L$1:L1=L2)/(N$1:N1=N2)/(WEEKDAY(J$1:J1)=WEEKDAY(J2)),1)),R2)
    2. Add an Override column (R) for those cases when the suggested amount will not be used.
    3. Convert the range J1:R6 into an Excel table so that when a new row is added the formulas in columns O and Q will automatically copy down.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] CountifS for Column Reference, Row Reference and Data Validation Reference
    By pavanbhoyar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-24-2018, 02:37 AM
  2. Forecasting current sales according to ongoing trend and history data
    By manchoon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2017, 04:31 PM
  3. Replies: 1
    Last Post: 08-14-2015, 02:49 AM
  4. [SOLVED] Cross reference with multiple instances in reference data
    By Nick F in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-08-2013, 10:31 AM
  5. Display data reference from another worksheet based on cell reference
    By Drew123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2011, 03:52 PM
  6. How do I manage reference changes on a worksheet copy?
    By Joe Miller in forum Excel General
    Replies: 3
    Last Post: 01-18-2009, 10:36 AM
  7. Macro for huge input/output problem using relative reference?
    By frogman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2008, 10:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1