Excel 2010-2019 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 Home > Forums > Excel
Excel Forum
Walter Hamilton
11 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 11 months ago
 Walter Hamilton 11 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 11 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 11 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 11 months ago I'd rather do all this within Access instead of using Access as the external data source.
 Richard Rost 11 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 11 months ago
 Adam Schwanz 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 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 11 months ago Richard, No need to post a link.  I watch all your Access videos on YouTube.  Thanks for everything.
 Scott Axton 11 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 11 months ago Much easier to do the math calculations for the payments in Access and then export to Excel.
 Scott Axton 11 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 11 months ago My bad. Not enough coffee yet today. :/
 Richard Rost 11 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 11 months ago 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, below.

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.

 You may want to read these articles from the 599CD News:
 6/30/2022 Sales Chart 6/17/2022 Fast AutoSum 2/22/2022 ISO Date Format 1/27/2022 Work Days 1/11/2022 Excel Fast Tips 1/7/2022 Count Cells by Color 1/7/2022 Excel VBA 1/7/2022 Excel Count by Color 12/26/2021 Conditional Formatting 12/9/2021 Excel 2003 Quick Start 1

 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 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