Essentially I am compiling an e-Recipe book for my son who is taking over the family restaurant. This is a bringing together of my 40 odd years experience (and my mothers 40 odd years as well) in an Excel project that sets up tables of ingredients, weights and measures etc etc, suppliers and their contacts and prices, then you combine ingredients and amounts in a series of formula driven (v Lookup, Index & Match), to cost and suggest selling prices worked on variable GP%'s. It controls our Food Costs (Production Costs) per dish as well as an overall trend. It also fetches pictures for each recipe (based on a drop down list and match formulas) from the photo library where there are several pictures of method and result for each recipe, so as not to clutter the actual working page with too many jpegs. It can create picking lists / requisition forms from the pantry based on the number of portions or size of the item being prepared, and these then form the base of the daily issues from stores to the *******s, bars etc
It then saves the outcomes in a series of tables that link to our POS (Invoices and quotes for Functions), brings back and records the daily sales in quantities (More important) and $$'s as well (but the POS worries about the money side). Once the sales are in it works out, based on trends, what our projected sales could be for the next period of time and via pivot tables and the recipe quantities and prices creates a shopping list broken down into suppliers (chosen via best price or quality), this, (a weighting they got way back in the input area) saves to PDF and prints out a separate shopping list on each order day (based on TODAY() settings for each supplier and produces a cash flow report so we know what we are expected to spend over the next period of time (based on income vs expenditure
OH yes, and it takes stock in and out based on sales and purchases as well as what has been produced but not yet sold (eg we make batches of Curry, portion them and freeze them for future use, we make jams and jellies in the summer months and preserve stewed fruit and this lasts us through the winter months, also we have hams etc in the smoking room that while they have been paid, for will not appear on the menu for a few months, so skewing our cashflow again.
And then, when it's finished I can retire gracefully knowing that my restaurant is in safe hands with my son, who need never bother me with questions while I sit on the porch, look at my mountains and drink with my other retired chef buddies!! FAT CHANCE but we can all dream..
There is probably a commercial version of what I'm doing out there but hey, I'm self teaching ( along with help from guys like you
) this old dog some new tricks in Excel & VBA other than If a = b then add c*45.2 and halve the difference of x+y...;or do "").
Thank you for your in put and any ideas from all and sundry are most welcome. Lets see how top heavy but user friendly we can make this ******!!
