Keeping packaging material and photographs in sync with inventory

Oh man, that was some pretty good advice you gave me.

Based on that I managed to come up with this:

https://docs.google.com/spreadsheet/...EtwX0NrQk9pR2c (or see attachment)

What do you think? Where do we go from here?

Mikkel

(this is the second time I write this - it didn't get through the first time for some reason - so I apologize if it's a bit off)

Originally Posted by Russell Dawson

Which packaging is used for each item? The forecast sales are over what period?

Only the boxes are used for each item (if the customer chooses Premium delivery though, she gets a bag as well).

I guess whether it's the small, medium or large box depends on how many items the customer orders.

I added this "Likelihood" column though - hoping I could analyze my sales data (when I actually have some sales data) to find out the popularity of the box sizes / order sizes - so I can forecast the optimal quantity for my next order of packaging material. Think that'd work?

The sales forecast is (and sorry for not yet indenting things to make it more obvious) for January. I'm just about to begin work on February as you can see below the Photography section.

Originally Posted by Russell Dawson

Is this a live and working spreadsheet or a project for which you need formulae? I'm happy to help either way.

This spreadsheet is for my ecommerce startup basically.

Thanks again!

P.S. "Growing old is mandatory, growing up - optional" -- ******* brilliant ;-)

Actually there's a minimum order quantity of 200 from my packaging material supplier.

(seems I have to break this reply up into smaller parts for it to pass through)

Good morning Russel!

Originally Posted by Russell Dawson

Good morning Mikkel,

1 & 4 - Really, it's up to you. Whatever suits your personal preference.

Thanks - as long as you don't disagree it's cool.

Originally Posted by Russell Dawson

2 - It's not very useful to sum all the packaging as one unit due to different sizes and different ordering patterns envisaged. A total of what is in stock may be useful as a check against what is in the store cupboard. To sum everything you can find is not really a good thing.

Don't I need a final total though with which to calculate tax, shipping and duties to the warehouse, and to incorporate with the rest of my expenses budget?

Originally Posted by Russell Dawson

For instance, in P33 you have a sum of the gross margin which is only a copy of the cell above.

Isn't P33 "O33/N33"?

Originally Posted by Russell Dawson

It would be far better to have an average of the column above. In reality we know that it won't change things as they are but it allows for changes in the future and shows a better understanding. =AVERAGE(P8:P32) It's already becoming more dynamic!

Yeah, that one is much better!

Originally Posted by Russell Dawson

3 & 5 - As the s/s doesn't really DO anything much and doesn't seem to be set up to react to sales it's difficult to know where to start. Apologies to you if you have spent a lot of time on this project. Let me think about it.

No, not at all. I cherish constructive criticism so please - do not hold back!

The goal is to create the finest financial model ever created for international ecommerce. He he, silly right?

My actual model is basically a simplification of this one, plus this one, and ofcourse, the purchases budget we're discussing here.

My sister's boyfriend knows how to do macros in BASIC so he's gonna help me make it multilingual and multicurrency (with like a config sheet in front with forms and such), since I allow him to date my sister. I also got some cool data that I'm hoping to use as the basis for my forecasting. Don't yet know how though.

One thing I don't understand about the financial projections model I linked to above is (if you don't mind having a look): Is it only meant to be used prior to starting up? What about when I'm actually up and running?

1. If only prior to startup, should I copy that model into a new model and customize that for a "running business" - i.e. with all forecasting data and functions removed, maybe basic accounting features added etc.?

2. If it can, or should be, used for both:
2.1. Should I ask my sister's boyfriend to code me something like "Projection mode: (*) Actual ( ) Forecast" for each worksheet? Would that be a good idea? I'm sure it'd be nice with projections of the years ahead even though I'm up and running.

3. What about general accounting stuff - is that something a model like this should take care of too, or would it be best to keep planning and accounting apart? And perhaps employ something like Xero for the latter.

3. You mention reacting to sales - what do you mean exactly?
3.1. Should it have access to my shop's sales database and then slowly but surely merge this data with my forecasts data to create an even more precise model? Do you think this is a good idea?

Sorry, I notice we're going way off-topic here.

Originally Posted by Russell Dawson

UK starts 4 days of partying today - I am not a party animal so this project will be my relaxation!

I'll be back.

If I was there I'd buy you a drink for sure!

See you later man.

Originally Posted by Russell Dawson

Hi Mikkel,

I think some of my earlier questions were unnecessary but I've come up with a daily/weekly record to show how stock and packaging (that was really your first question) are turning over. I understand now from your recent posts that it is a bit bigger than I first realised and I would not wish to mislead you. I think you are right to get professional help. There are obviously many things for you to do at this time. You clearly need to be forecasting what your cash flow will be but I cannot help in this respect. I'm starting right from the beginning and visualising sales on a daily basis which are recorded for a weekly figure. That can then be translated into monthly amounts. I would think that you should consider a new sheet for each month and then have a summary page for the year.

I hope the attached gives some ideas. I've made some changes and will explain. I had to work the stock before the packaging to get my head into gear.

Suppliers name changed and products numbered in line with old product ref and size to give you unique ref for each product although not useful now it may assist in identification later.

Add new stock as it arrives to old stock count. Col D has minimum stock level, col E counts usage from daily record. Col O has reorder flag when stock falls below threshold. Col P input date when reorder submitted and flag goes. Clear date manually when stock arrives.

W/sheet is protected but no password set.

This is exactly what I've been dreaming of. Thank you so much - this is absolutely brilliant. I'll be back with some follow-ups after my mind has had a chance to process all of this.

Originally Posted by Russell Dawson

I think a litre of Brennevin is required now!! (Wonderful thing the internet) I'll settle for a pint of my favourite Saltaire Blonde but don't tell my wife!

Ha ha ha

Originally Posted by Russell Dawson

I'll tell the landlord at the pub that you will settle up next time you are in town. Cheers. http://saltairebrewery.co.uk/saltaire/?page_id=14

You got it man :-)

Cheers! :-)

Hello!

So let me see if I got this right:

1. Your design basically advocates having one sheet for purchases (Stock) and one for sales (Sales) - where the sales one keeps track of sales naturally, but also reorders - reorders which need to be recorded back into the purchases sheet before the order is made (to calculate shipping, duty etc., and so that the Expenses sheet has somewhere to refer to)?

2. Can product ref. be the SKU?

3. Why should I clear the reorder date when the new stock has been received and recorded? And, by recorded I assume you mean brought back into the purchases sheet?

Mikkel

Dear Russell,

Thanks man, this is gonna be one killer spreadsheet!

Forget the brennevin, how about an all-expenses-paid vacation to Bora-bora with unlimited access to the minibar? (bringing the wife is optional)

Mikkel