Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
Home > Forums > Excel
 
Excel Forum

Welcome to the Microsoft Excel Forum. Excel is one of the more popular topics here at Computer Learning Zone. This forum is for everyone to read but only students (paid customers) can post here. If you'd like to become a student, you can watch Excel Beginner Level 1 for free, and then purchase Level 2 for just $1.00 here. After you create a logon, you will have full access to the Forums.

Subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receieve new comments.

Click Here to Post a comment and start a new discussion

Please read the FORUM RULES before posting.

 

Forecasting Venue Upload Images   Link 
Walter Hamilton 
2 months ago
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
2 months ago

Walter Hamilton
2 months ago
As you can see from the screenshot, several of the rows have one too many out months with values in them.
Richard Rost
2 months ago
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
2 months ago
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
2 months ago
I'd rather do all this within Access instead of using Access as the external data source.
Richard Rost
2 months ago
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
2 months ago

Adam Schwanz
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
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
2 months ago
Richard,

I can easily do the formula for the starting figure.  I really appreciate it.  Will this be on an Access TechHelp?
Richard Rost
2 months ago
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
2 months ago
Richard,

No need to post a link.  I watch all your Access videos on YouTube.  Thanks for everything.
Scott Axton
2 months ago
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
2 months ago
Much easier to do the math calculations for the payments in Access and then export to Excel.
Scott Axton
2 months ago
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
2 months ago
My bad. Not enough coffee yet today. :/
Richard Rost
2 months ago
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
2 months ago
Walter, your part is in the Extended Cut of Loan Payments
Add a Reply

Show All Comments

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

9/11/2021Circular References
8/25/2021Stock History
8/23/2021Concatenation
8/17/2021Flash Fill
8/5/2021Stock Portfolio
8/4/2021Stock Portfolio
6/23/2021Import Data
5/14/2021Quick Queries #4
4/4/2021Text to Columns
3/15/2021VLOOKUP in Access
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
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
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
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: microsoft excel forum  PermaLink