Forecast units

[renenger]

I am drawing a blank on how to proceed with this requirement and was hoping someone out there could help me.

I am creating a graph that shows all of the orders we have taken, shipped, and what we have bid on. There is a table that holds the customer, no of units ordered, no of boxes per unit, number of units to ship each week and start date.

I need a formula that will calculate all of the week ending dates until the no of units ordered is 0.

For example, Capital ordered 100 units, 12 boxes per unit, 2 units to ship per week and shipments are scheduled to start on 2/19.


I would need it to display:

W/E Units Boxes
2/23 2 24
3/1 2 24
3/8 2 24 etc....

Until the total units ordered equals 0

This information is diplayed on a graph. The graph shows all of our open bids and based on shipments per week, displays a forecast of what's coming up.

Any help would be greatly appreciated.

 

[ajetrumpet]

I need a formula that will calculate all of the week ending dates until the no of units ordered is 0.

For example, Capital ordered 100 units, 12 boxes per unit, 2 units to ship per week and shipments are scheduled to start on 2/19.


I would need it to display:

W/E Units Boxes
2/23 2 24
3/1 2 24
3/8 2 24 etc....

This is impossible without a picture or two to go off of. Need to see structure here (the tables).

 

[renenger]

Bid Sample

I am attaching a sample of what I am using. If you run the report through Fiscal April of 08, you will see that it generates 8000 cabinets for week ending 4/1.

I need to actually need to forecast out the weekending dates for the unit to ship each week.

for example

Project Start = 2/1/08
Units to ship each week = 10
No of Units (bid) = 100

Week ending 2/1 should be 10 units
Week ending 2/8 should be 10 units

and so forth until no of units = 0

This should be displayed on the graph so we can see what our total capacity looks like over the given period.

Does that make sense?

 

[ajetrumpet]

Reneger,

I will look at your file, and see what I can do with it. But, as for now, I have one question: How far is Helena from Bosman? A small company wants to fly me out to Bosman to look at their facility. I am going to go obviously, but being from Iowa, I'm wondering if all of the people will be wearing cowboy hats....or weather I might just meet a college girl there.

 

[renenger]

Thank you

Any info you can provide would be greatly appreciated. I can't seem to wrap my head this query.

Bozeman is about an hour away Helena. It's actually more of a yuppie town than a bunch of cowboys. It's a big college town so college girls should be in abundance! Good luck with that!!

It's actually very beautiful there.

 

[renenger]

Forecasting

Ok. I think I know what I want to do, I just don't know how to do it.

I was able to create a query that displays the project, project start date, no of units bid, no of units to deliver each week, and calculate the project end date based on the results of no of units/no of deliveries.

What I need to be ablet to do now is run a module that will calculate each weekending date between the start date and end date (Including those dates) with the no of deliveries and project name and insert them into a temp table.

I know I need something like

Dim WeekDate as Date
WeekDate = StartDate

Do While WeekDate <= EndingDate
WeekDate = WeekDate + 7
Units = NoDel (Number of Deliveries)
Project = Project
DoCmd.RunSQL "Insert into tmptblForecast (WeekDate, Units, Project) values (" & WeekDate & ", " & Units & ", " & Project &")


I am just not sure I am approaching this right. I would also need to No of Units to decrease by the Number of deliveries each week.


If I a contract with 14 units due to start on Feb 25 with 4 units shipping each week, the data should read:

Week Ending Ship Units Project Units left
3/1 4 HGFT 10
3/8 4 HGFT 6
3/15 4 HGFT 2
3/22 2 HGFT 0

The only information provided to the database is start date, no of units bid, no of units to deliver each week. A query or module needs to calculate each week ending date to include the starting week and ship the number of units to deliver each week.

Please HELP!!

 

[ajetrumpet]

Questions first:

Your bottom query (SOPTotalUnitsBid) takes fields from the BIDS table, and displays them, plus two other relevant criteria:

1) Only OPEN Bids
2) A field that lists [NoBoxes]*[NoUnits]. What is this? Total no. of what for the bid? Boxes? Are there a certain number of boxes in a unit?

Then, in your make-table query, you SUM that column first. Why? I thought you were trying to get data for each Bid (Project) separately?

The point I'm trying to make is that I think you want either one of two things here:



1) The SUM of [NoBoxes]*[NoUnits], divided evenly among weekly timeframes (starting at a date, but I don't know which date), until that figure reaches 0.
2) The SUM of [NoBoxes]*[NoUnits] Per Project (bid), with the same process as #1.

Am I right about this? If so, tell me which one it is! If the stuff I have mentioned here is accurate, you're going to have to use a recordset to get this done. You're trying to complete a "vertical" process with a query, and I've been all through that with other people here on the forum. It's virtually impossible.

 

[renenger]

Basically, there are a certain number of boxes per unit. That is the number actually displayed on the graph. However, I can figure that out later once I have the units forecasted properly.

The graph is designed to sum all of the units scheduled to deliver through a week-ending date. That's the reason for the sum. The start date needs to be reformatted to a Week-Ending date.

So I may have the following information:


Week Ending Project No of units
3/1 HGJF 4
3/1 PROJ 6
3/8 HGJF 4
3/8 EMPJ 2


On the graph, week-ending date 3/1 would show 10 units and 3/8 would show 6.


This part I can do. I just need to figure out how to either create a table or insert into the temp table the forecasted deliveries.

I have a project with 100 units. The contract reads that we will ship 4 units per week until the contracted units = 0. So if the project is due to start on 3/3 then the week of 3/3 we will ship 4 units. The following week will will ship 4 units. etc....

 

[renenger]

Forecast Figured out

I finally figured it out. I decided to create a permanent table and house the forecast there. Anytime a key field is updated on the bid form, I will have all of the previous entries deleted with a delete query and the new figures entered.

So everytime the start date, number of units, number of deliveries or boxes is changed, the following code will run.

Private Sub ForecastMod()
Dim WorkDate As Date
Dim addCount As Long
Dim tmpDate As Date
Dim Project As String
Dim tmpCount As Integer
Dim Deliveries As Integer
Dim Weeks As Integer
Dim boxes As Integer

WorkDate = Me.WeekDate
tmpDate = WorkDate
tmpCount = Me.NoUnits
addCount = 0
Weeks = Me.NoUnits / Me.NoDel

Do Until addCount = Weeks
If Me.NoDel < tmpCount Then
Deliveries = Me.NoDel
tmpDate = tmpDate
Project = Me.Project
boxes = Me.NoDel * Me.NoBoxes
Else
Deliveries = tmpCount
tmpDate = tmpDate
Project = Me.Project
boxes = tmpCount * Me.NoBoxes
End If
DoCmd.RunSQL "Insert into tblScheduleForecast(Project, WeekEnding, Units, Boxes) values ('" & Project & "','" & tmpDate & "','" & Deliveries & "', '" & boxes & "');"
addCount = addCount + 1
tmpCount = tmpCount - Me.NoDel
tmpDate = tmpDate + 7

Loop

End Sub

 

[renenger]

Recordset Overflow

Rather than trying to update a table through the form, I would rather just calculate all the information when necessary. However, I am getting an overflow error with this code. I am trying to calculate the future weeks for a project and then move to the next one and repeat.

Private Sub Forecasting()
Dim rs As DAO.Recordset
Dim db As DAO.database
Dim strSQL As String
Dim WorkDate As Date
Dim addCount As Long
Dim tmpDate As Date
Dim Project As String
Dim tmpCount As Integer
Dim Deliveries As Integer
Dim boxes As Integer



Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [tmptblOpenBids]")

WorkDate = rs!WeekDate
tmpDate = WorkDate
tmpCount = rs!NoUnits
addCount = 0


Do While Not rs.EOF

Do Until addCount = rs!NoUnits
If rs!NoDel < tmpCount Then
Deliveries = rs!NoDel
tmpDate = tmpDate
Project = rs!Project
boxes = rs!NoDel * rs!NoBoxes
addCount = addCount + rs!NoDel
Else
Deliveries = tmpCount
tmpDate = tmpDate
Project = rs!Project
boxes = tmpCount * rs!NoBoxes
addCount = addCount + tmpCount
End If
DoCmd.RunSQL "Insert into tblScheduleForecast(Project, WeekEnding, Units, Boxes) values ('" & Project & "','" & tmpDate & "','" & Deliveries & "', '" & boxes & "');"
tmpCount = tmpCount - Me.NoDel
tmpDate = tmpDate + 7

Loop

rs.MoveNext
Loop

End Sub