Computer Learning Zone CLZ Access Excel Word Windows

I think we're going to the moon because it's in the nature of the human being to face challenges. It's by the nature of his deep inner soul... we're required to do these things just as salmon swim upstream.

-Neil Armstrong
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > Forums > Excel
Back to Excel Forum    Comments List
Forecasting Venue Upload Images   Link   Email  
Walter Hamilton 
I have an excel spreadsheet that lists all of our programs with the Period of Performance (PoP) Start, PoP End, Funded Amount, Invoiced Amount, and Funded Remaining.  I'm trying to forecast monthly revenue over the remaining PoP.  I'm currently using the following formula:

=IF($V3<0,0,IF(AND(AA$1>=$I3,AA$1<=$J3),($V3/$U3)*$T3,0))

The issue with this formula is when PoP Start and/or PoP End fall in the middle of the month.  We have several records that show an additional month of forecasted revenue.
Walter Hamilton 

Walter Hamilton 
As you can see from the screenshot, several of the rows have one too many out months with values in them.
Richard Rost 
This is more of a question for an accountant than a computer nerd. LOL. How do you WANT to resolve this? Do you want to divide up the amount based on the number of days in the month?
Walter Hamilton 
Richard,

I agree.  The problem is our VP of finance doesn't know the formula for excel.  Is there a way I could reference the Funded Remaining value and the values in the previous months field and only use the regular formula but the value can't exceed the amount remaining?
Walter Hamilton 
I'd rather do all this within Access instead of using Access as the external data source.
Richard Rost 
I'm sorry, but I'm lost. I don't understand what you're trying to do. Explain it to me like you would a 3rd grader.
Walter Hamilton 

Adam Schwanz 
So you're saying how like the second entry says rounded number of months 4 but has 5 months of entries? Making the total more then the total funded available too?
Walter Hamilton 
Richard,

Referring to the above screenshot.

Is there a ways to as to the current formula by having each out month, columns x - .........
calculate the remaining mount from column v and all previous columns between x and the current column.  Then use the same logic to figure the amount for that month, but cap it at the amount remaining if it's below what the amount should be.

For example. the second row from the bottom with column V value being $293,938.43

The values would be:

W: $41,991.20
X: $41,991.20
Y: $41,991.20
Z: $41,991.20
AA: $41,991.20
AB: $41,991.20
AC: $41,991.20
AD: $0.03

AD would be $0.03 because that is all that's remaining  if you take the calculate the value of column V - W - X - Y - Z - AA - AB
Richard Rost 
In Access, sure. You could do it with a simple loop. Add records to a subform, subtracting from a total. I suppose you could do the same thing in Excel too, but you'd need some VBA.

Question: where do you come up with the $41,991.20?

Walter Hamilton 
Column w on use the logic:

=IF($V6<0,0,IF(AND(W$1>=$I6,W$1<=$J6),$V6/$U6*$T6,0))

I = PoP Start
J = PoP End
T = a weighted value.  For Active Programs it's 1.  For opportunities it is based of what stage the opportunity is in.

Since V6 is greater than 0 and W1 is >= I6 and W1 <=J6 than V6/(U6*T6)

So.  $293,938.43/7 = $41,991.20

As far as the access piece goes.  I have the main data working.  I need to be able to export that data plus the calculations I'm currently trying to do in Excel.  The intent is to show the current month and 5 years worth of out months and show the projected revenue for each of those months.  I don't know how to create something that will automatically label all of those columns based of the current month.
Richard Rost 
Well, I can show you how to enter a start date, a total value and a payment amount in a parent form, and then load a subform with that many payments divided up over months.

So in the parent form, you would enter 8/4/2021 as the start date, $5000 for the total value, and $1025 as the payment amount, and then in the subform you would get:

9/5/21, $1025
10/5/21, $1025
11/5/21, $1025
12/5/21, $1025
1/5/22, $900

Would that be of help? You could then copy that data over to Excel if you want. In fact, I could even format it in a textbox so you could copy and paste it.

Richard Rost 
In fact, if you don't need to store the data in Access, you just want a form to format that data to be copied and pasted into Excel, that would be even easier. Won't even need a subform - just a textbox to copy the data into from a loop.
Walter Hamilton 
Richard,

The data doesn't need to be stored in Access.   This is a report my CFO has to send to our parent company on a weekly basis.  I'm trying to remove the manual labor piece.  We are just beginning to use the database I was working on and they've seen a huge efficiency increase already.  Ideally, I'd like to have a command button they can press which would export the data into excel with all the our months included.  Does that make sense?

Also, I purchased your AccessUpdater tool today and it works great.  I was beyond tired of remoting into everybody's computer to push the latest version.  thanks for everything you do!
Richard Rost 
Makes sense. Would what I suggested help you? I still don't fully understand all of your calculations, but I can do that payments step for you easily.

Updater: thanks. Glad you like it. So far everyone that has used it has said it saves them a lot of time.
Walter Hamilton 
I think it would help as long as I can export the results.  I don't want my VP of Finance to have to copy and paste.
Richard Rost 
OK, so I could have a continuous form where you enter in the total value and the payment amount and then it creates a CSV with the data in a format like this:

Total Value, Payment Amount, 9/5/21, 10/5/21, 11/5/21, 12/5/21, 1/5/22, 2/5/22
5000, 1025, 1025, 1025, 1025, 1025, 900, 0
1000, 500, 500, 500, 0, 0, 0, 0
10000, 1500, 1500, 1500, 1500, 1500, 1500, 1000


Is that what you're looking for?
Walter Hamilton 
Can we have the form get the total value from the [Funded Remaining] field? Ideally, a form that has all the stored fields with these calculated fields would be perfect.  That could be exported and then they could just tie the Active Programs export and the Opportunities export together.
Richard Rost 
Sure, you could have the form get the value from whatever field you like. I'm trying to come up with a simple solution that I can make a video out of for TechHelp. What I outlined above may be of interest to other people as well, however I can't make it custom and specific to your database... but I can show you the techniques to run the loop and space out the payments. Integrating it precisely with your existing spreadsheet is beyond what I can do in a TechHelp video. I'm happy to help provided it's something that works as a video. If you want specific consulting assistance beyond that, you can post in my Access Developer Network and perhaps one of my other students or Access Veterans will integrate it more for you. My job is to show you how to do the tough stuff. You can flesh it out further for your needs. :)
Walter Hamilton 
Richard,

I can easily do the formula for the starting figure.  I really appreciate it.  Will this be on an Access TechHelp?
Richard Rost 
Yep. Give me a couple of days. Since you're both a Platinum Member and an MYOLP Developer Member, and I haven't made a video for you yet, you get very high priority... plus this is a pretty cool project and I haven't done anything like this yet. :)  I'll try to remember to post a link here to let you know.
Walter Hamilton 
Richard,

No need to post a link.  I watch all your Access videos on YouTube.  Thanks for everything.
Scott Axton 
OK sorry for trying to throw a monkey in the wrench works here.

Why not make up an Excel spread sheet - linked to a table in Access where you do all the work?  Format it all pretty like you want it then do a "save as" and break the connection so you can email it out to the people that don't have the link?
Richard Rost 
Much easier to do the math calculations for the payments in Access and then export to Excel.
Scott Axton 
That's what I said :)  
" linked to a table in Access where you do all the work"

Problem is Each time you do the export you have to import it into Excel Manipulate column headers, titles, dates, etc.  If you link the table then you can set it where your data come in and it's all pre-formatted.
Richard Rost 
My bad. Not enough coffee yet today. :/
Richard Rost 
I'm going to put this together with a question I had from another member a few weeks back about how to track loan payments, very similar to what you're doing. Quick question: for that remainder, would you really want an extra payment for 3 cents at the end? Wouldn't you rather just add it to the final full payment?
Richard Rost 
Walter, your part is in the Extended Cut of Loan Payments

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Excel Forum.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 8:59:37 AM. PLT: 0s