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.
Bookmarks