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.
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:
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.
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.
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
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?
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.
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:
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.
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!
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:
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.
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?
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?
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.
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?
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
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.