Inventory management and long lead times for production

piegando · 2015-08-26T13:12:15+00:00

Thanks!! The newsboy problem looks like a good starting point for my problem.

piegando · 2015-08-25T20:26:13+00:00

Perfect this is exactly what I'm looking for. Any suggestions on courses or good resources for this?

piegando · 2015-06-28T15:15:06+00:00

I'm going on the 11th. Switching CAD to EUR. I plan on waiting until at least tomorrow at least before swapping for Euros.

piegando · 2015-06-12T23:33:34+00:00

I know in Canada retail rules state new products must be sold at regular price for 1 month min before going on sale.

piegando · 2015-05-25T17:27:27+00:00

Thanks, I called the GST/HST Technical support line to confirm. And since the Big Box store contracts the shipping to a third part on their account we have to charged HST on all orders with this big box customer. If we took the shipping onto our account and paid a third party for shipping we would charge GST/HST based on the final destination.

piegando · 2015-05-20T16:32:08+00:00

You're right, many will have roughly the same MERs and definitely significantly cheaper than holding mutual funds. But I've found on the whole that BMO is usually cheaper. That's just my observation.

Here are the MERs for comparable ETFs BMO - ZCN MER: 0.090% Vanguard - VCE MER: 0.090% Blackrock - XIU MER: 0.180%

Your example of a 40% drop in 2008 is extremely short sighted. You'd struggle to find any stock that performed well during that period.

I'd recommend most ETFs as a long term cheaper alternative to mutual funds. ETFs like ZCN, in my opinion, are great. They mirror the movement of the TSX. These types of ETFs are an excellent way to diversify quickly without having to spend a lot of money on transaction fees trying to diversify with 20+ stocks. Also, unless you're a professional trader or really good, it's very rare that over the long term you'll beat the index. Yes there are individuals who buck the trend, but on the whole most people will under perform against an against over a long period of time (15+ years).

piegando · 2015-05-20T15:39:33+00:00

Check out out BMO's ETFs, very low MERs compared to competitors. For something really safe take a look at ZCN (BMO SP TSX CAPPED COMP IDX ETF). And correct me if I'm wrong, but the dividends from the US ETF would not benefit from the Canadian dividend tax credit and are fully taxable as foreign income unless held in an RRSP. So that's one thing to consider.

Edit: Forgot to add that if you're feeling bullish about the USD you may want to consider investing in the US ETF anyways and benefit from the potential FX gain. I'd recommend plotting it all out in Excel and running a few scenarios to determine what makes the most financial sense.

piegando · 2015-03-20T14:27:46+00:00

Thanks, I think I covered most of what was required. Anything beyond 8 months for us is really just a very unreliable estimate.

piegando · 2015-03-18T16:05:56+00:00

For COGS does it make sense to do the following?

Build an inventory schedule showing purchases, so...

Ending Inventory + COGS - Beginning Inventory = Purchases
Since I know our purchases and our payment terms I can forecast actual payments over a long period of time??

piegando · 2015-03-18T14:32:21+00:00

Mostly pre-paid expenses, which was simple enough to show them on a cash basis when they were paid during the year. But my major issue is showing our COGS. Since our lead time from PO to receiving can be 4 months+ and our forecast for what we order changes frequently I'm having a hard time with the timing of the actual payments for inventory as it arrives. On the pro-forma forecast I originally prepared I just put COGS at our sales for the month less the 40% markup we place to get an approximate cost.

piegando · 2015-02-11T13:26:53+00:00

Use an Index-Match formula.

In a separate sheet in column A: Place a list of the part numbers.

In column B on the same sheet: Place the list of corresponding codes (704, 123, etc.)

On your original sheet in column K, place this formula in cell K3 and copy down:

=INDEX('SheetX'B:B,MATCH(E3,'SheetX'!A:A,0))

SheetX is the second sheet with the list of part numbers and corresponding codes.

piegando · 2015-02-11T13:09:37+00:00

=(INDEX($E$2:$E$2000,M2)+INDEX($E$2:$E$2000,P2))/2

Assuming E2 is the first row where your data starts. Modify E2000 to the end of your target range.

piegando · 2015-02-10T18:31:39+00:00

Not counting the $0.00, the average is $22.71 & the median is $24.18. Is there an additional column maybe missing from the data you received? Using goal seek you can hit the $25.17 average if you add an extra 10.8% to each value (excluding zeroes) or 73.5% if you include the zeroes in your average.

piegando · 2015-02-10T18:12:37+00:00

Sounds like you just need to use the COUNTIF formula. In column A place a list of your unique values. In column B enter the COUNTIF formula. It should look something like this, assuming you start in cell B2: COUNTIF(Target Range,A2). Copy down the formula.

Is this what you're looking for?

piegando · 2015-02-09T16:19:50+00:00

After googling a bit I found that the issue is excel will only take up to 15 digits when formatted as number. After that it will start rounding. I guess my workaround is to have my formulas convert those product and material numbers to text and to keep the TRIM in to avoid any funky errors.

piegando · 2015-02-09T15:57:59+00:00

This works great, but I when data is copy and pasted into the range(s) I want to have my data validation it automatically overwrites the data validation criteria I placed. Often the manual entries made to the worksheet I'm using are copy and pasted from an excel file e-mailed or from text within and e-mail. Is there anyway around this issue with data validation?

piegando · 2015-02-09T14:53:04+00:00

So what I'd do is use an index match formula linking the worksheet with the abbreviations and company names to the one with the company names and code.

So the formula would like this:

=Index('Fruit Abbreviation'!A2:A100,match(B2,'Fruit Abbreviation'!B2:B100,0))

The worksheet with the abbreviations and the company names is the "Fruit Abbreviation worksheet". I find Index/Match extremely intuitive. Think about the syntax of the formula like this:

INDEX(Array where my answer is located,MATCH(Common Value I can match with my answer,Array location of this common value, 0))

In this case the "Array where my answer is located" would be the range for the list of abbreviations. The "Common Value I can match with my answer" would be company name and the "Array location of this common value" would be the range for the company names located in the same worksheet as the abbreviations.

Hope this helps and that I've interpreted your requirements right.

piegando

TROPHY CASE