PDA

View Full Version : [SOLVED] Ordering against a forecast according to lead-time



K. Georgiadis
02-18-2005, 09:11 PM
I cannot yet visualize whether this absolutely requires VBA code so please bear with me:

I will have a worksheet with years running across Row 4; Row 5 will have quarters 1 to 4, as subdivisions of each year. In Row 6 the user will enter a sales forecast by quarter, as many as 5 years at a time In cell B2 the user will enter the lead time for obtaining the product, say 12 weeks. And now for my challenge: Row 7 will have a calculated "ordering schedule", indicating when the product must be ordered to be available for sale, based on the lead time entered. I shall make the assumption that product required in Qrt 3 must be in the warehouse at the beginning of the quarter, therefore if the lead time is only a fraction of a quarter, it still has to be ordered in the previous quarter.

I have attached a simple worksheet illustrating the contemplated design (devoid of any calculations of logical formulas!)

My question is:

Does this require VBA code or can you think of a way to solve the problem with built-in Excel functions?

Thanks for your help!

Paleo
02-18-2005, 09:25 PM
I think you must use VBA because according to the leading time it might require you to order 2 or more quarters in advance and its hard to code using only regular excel formulas.

Paleo
02-18-2005, 10:18 PM
Hi,

use this macro at Sheet1 Module.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lead As Integer, Retorno As Integer, Coluna As Variant
Lead = Range("B2")
Coluna = Array("B", "C", "D", "E", "F", "G", "H", "I")
Retorno = Range("D2")
For Each col In Coluna
Range(col & 6).Activate
If Range(col & 6) > ActiveCell.Offset(1, -Retorno) Then
z = MsgBox("No products available due to lead time for " & Range(col & 5), vbOKOnly)
End If
Next
End Sub

K. Georgiadis
02-19-2005, 07:10 AM
I thought so. Can you (or someone) give me some suggestions?

Paleo
02-19-2005, 10:53 AM
Doesnt my code from the last post helped you out?

K. Georgiadis
02-19-2005, 11:19 AM
I'm sorry, I didn't see the code. I'll try it now. Thanks!

Paleo
02-19-2005, 11:23 AM
Ok, no problem if I didnt address your problem correctly just let me know and I will try to adapt it as needed.

K. Georgiadis
02-19-2005, 11:29 AM
I tried it, but it doesn't seem to be the answer. I'm hoping for a macro that will auto-populate row 7 according to the lead-time entered in cell B2. I have also thought of giving the user the option of using this auto-calculation or entering the numbers manually in row 7. Perhaps a checkbox "Do you wish to auto calculate the ordering schedule?". Checking the box activates the auto-populate macro.

Paleo
02-19-2005, 11:39 AM
Ok, I will made the changes and submit the file then.

Paleo
02-19-2005, 11:58 AM
Try this one.

K. Georgiadis
02-20-2005, 07:48 PM
Thanks so much for the effort, Paleo. I tried your file and ran into an error condition after cycling through the program 3 times (by entering different numbers in the lead time cell). In the meantime, though, I put my traditional Excel thinking cap on and was able to resolve this problem by using multiple IF and AND statements, as shown in the attached modified file. It seems to work well, even though an unexpected flaw can still arise!

What I need to do next with this file is to:

1. Add Row 7 labeled "Manual entry of ordering schedule"
2. add a checkbox asking if the user wants to use auto-calculation for the ordering schedule.
2. If the checkbox is checked, Row 6 is unhidden, Row 7 is hidden
3. If the checkbox is NOT checked, the reverse occurs: Row 6 is hidden, Row 7 is unhidden

I believe I have sample code somewhere for a checkbox functioning in that fashion, but any suggestions you may have are most welcome.

Also, do you think that it would be more functional if, instead of a checkbox, I provided two option buttons, one for auto, the other for manual operation, hiding or unhiding the appropriate rows?

Again, many thanks for your help.

Paleo
02-21-2005, 06:15 AM
Hi, I think you might use both approaches, I will make it using the option for you first and then I post it here.

K. Georgiadis
02-21-2005, 06:28 AM
Thanks!!

Paleo
02-21-2005, 06:41 AM
Check if this solves your problem.

K. Georgiadis
02-21-2005, 05:40 PM
perfect. I'm marking SOLVED. Thanks again

K. Georgiadis
02-21-2005, 06:40 PM
Carlos, I have a follow-on question. The example I have provided you with covered just one product, whereas the actual worksheet will cover 10 products. Thus, the headings, rows, formulas, etc. will copied and pasted down 9 times. What would be the syntax if I wanted those two Option Buttons to hide/unhide not only rows 6 and 7, but all appropriate rows for the remaining 9 products, e.g. 19 and 20; 27 and 28 and so on...

Paleo
02-21-2005, 07:21 PM
Hi you just need to insert that in the event, the better way is by using a loop. If you can post a worksheet with this specs (can of course be with no real data in it, just the rows and cols you will use) I can create it for you.

K. Georgiadis
02-21-2005, 07:47 PM
Here it is (Version 5). I see this as a valuable lesson; I hope to be able to do this on my own next time:think:

Paleo
02-22-2005, 05:58 AM
Hi I have just downloaded it. Soon will be posting your solution.

Paleo
02-22-2005, 06:42 AM
Hi, as you used always the same text in column A I have used a different approach.

K. Georgiadis
02-22-2005, 07:10 AM
great! and many thanks...
:friends:

Paleo
02-22-2005, 07:14 AM
You are very welcome and if need something else just ask and I will try to help you out.