Monthly Budgeting & Forecasting Model [Template Included]
Вставка
- Опубліковано 21 сер 2024
- In this lesson, we build a corporate monthly budgeting & forecasting template in Excel, creating internal department-level budgets that we integrate into the income statement, with a downloadable template included.
✅ Download the Excel template: www.ericandrew...
🚀 If you want to master the finance skills & frameworks to successfully scale technology startups, secure your spot in my "Finance for Startups" program, today: www.ericandrew...
Communities:
💼 Linkedin: / eric-andrews-1624b656
☑️ Twitter/X: / eric__andrews
🌟 Instagram: / ericandrews_startups
🔥 Discord: bit.ly/discord...
💻 Newsletter: bit.ly/joineri...
🎥 Clips: / @ericandrewsclips7818
Related Financial Modeling Videos:
🚩 Build a 3-Statement Financial Model: • 3 Statement Financial ...
🚩 Finance Interview Prep Series | Crush Your Interview: bit.ly/fin_inte...
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
In this video, we first build a monthly budgeting Excel template that we can send out to department heads at the company (for them to build budgets). Then we receive the budgets and integrate them back into our financial model. Finally, we build out the full income statement model to see our monthly financial plan for the full year.
Also included is a link (in the "resources & links" section) where you can download the monthly budgeting template for free.
When working in a small company, it's very important to have a straightforward budgeting process so that your department leaders can submit to you their financial plans (assuming you are on the finance team).
For that reason, you need to have a well-constructed model that can pull in and integrate their internal budgets straight into your P&L.
One other important part is that you should also always first build the revenue model & COGS so that you have a good idea of what your gross profit will be. Keeping that number in mind, you will have an easier time giving guidance to the company on how much money they have for their budgets.
Sections:
0:26 Create an internal budget template for departments
1:49 Create drop-down lists using data validation
5:16 Integrating budget data back into the forecasting model
9:40 Building the full budgeting & forecasting income model
11:56 Building customer acquisition & revenue assumptions off the advertising spend
15:39 Building assumptions for cost of goods sold
16:45 Writing the formulas for the monthly forecasting model
21:15 Formatting financial models
I hope that you can now feel confident building a corporate budgeting model in Excel. If you have questions (I'm sure you do) - please leave comments below and I'll try to help. Cheers!
#financialmodel #budget #forecasting #startups
ERIC is the GOAT of my financial career... Thanks Eric for all you do...
Haha you're very welcome 🙌
I rarely comment on sites, but ive been watching a ton of excel vids on UA-cam. Thank you for what you do, the constant reminders of shortcuts like F4 ect. Please don't stop making content. You are the GOAT at this.
🙏
This was so helpful; I am trying to do a budget for my company for the 1st time. You are a great teacher
One of the few channels which I don’t skip ads so I could at least payback the knowledge he provided. Subbed and likedyour videos!!!
🙏🙏🙏 really appreciate the support Crissa thank you !
Hey Eric, you are a good tutor/teacher. I have a FP&A interview coming up and your video is extremely helpful. I thank youtube, you, god and my luck to come across this video. I usually don't comment on any video. But here I am not only commenting but also about to like and subscribe to your video.
Appreciate the comment, best of luck in your interview. I also have a finance interview prep series here on youtube, I'm sure this would be helpful for you: ua-cam.com/play/PLdoBuNNnCmdXom4YXwTID-W1obBftGEEE.html
Thank you Eric. Its been 3 years and the file is still relevant.
Yep this stuff is pretty evergreen, the basic ideas haven't changed in decades
Thank you Eric. These days I am seeing all your tutorials on financial modeling. its really helpful. Thanks
Love from India :)
Wow what a nice compliment. Love from US !! 😁😁😁🙏
Thank you brother ! Just received a job as a jr operations analyst and I start Monday ! Really wanted to brush up on this before starting to have a better understanding! I feel confident going into work now!
My pleasure, I'm really happy to hear that. good luck!
Hello. It's been a year since you commented, but can I know your qualifications and skills to get that job? I mean you work only in Excel or any other software?
Thanks for also mentioning the details of the format and common formulas. The video is really helpful!
You are very welcome glad it was useful Youhui
I just started my company and this worked like a charm thank you so much.
Hey Saana - makes me so happy to hear that. Good luck with your business and its an honor to have helped support you!
Omg its my first time to hear a lecture explaning in this marvels easiest & more detailed .. im lucky today to get this lecture thanks 🌹🌹🌹
You are very welcome 🙌
I'm doing all 28 Vids, thanks for the courses really helpful! Thanks Eric!!
No way! You are going to learn a ton, good luck!
AWESOME!! IT HELPED ME MORE THAN TEACHERS IN COLLEGE
That's my goal Deivid - cheers!
Hats off to you Eric! You are an amazing teacher, god bless
You're probably going to get yourself arrested for putting this out for free! Great job!
They'll never find me !! 😁😁😁 Cheers
Some of us don't have money 💰 to go school he is teaching me ... I pray to God to bless you abundantly since am an orphan and I learn from you
Fantastic! Super helpful, clear and well thought out. Great presentation style. :)
Glad it was helpful! Thank you very much!
this is so helpful and so convenient for work purposes.
Very helpful, Eric! Thanks for putting it together for us. I appreciate it.
My pleasure Nidhi!
This is very educative Eric, Thank you
My pleasure
Hi Eric, I really enjoyed this tutorial. Thank you, I will need to watch it a few times though. 👍
Really helpful! Thank you very much! ❤
You are very welcome!
Great stuff! Just what I needed. U are my hero! 🔥👌
We are now married😭😂 Seriously, thank you.
Thanks Eric, this video is really helpful.
you are very welcome!
Very good information and really helps. Thank you so much. Eric.
My pleasure to help Yau! Cheers
very helpful and convenient for newbies.
Brilliant video as usual ! Thanks for sharing your knowledge.
A Sharma - really appreciate that. Cheers!
Wow!! Thank you. This clarifies a lot of confusion !!
Well that is awesome to hear 😁
Great work mate.... Kudos
Great video, you're good at this!
Thank you for this video and your knowledge sharing. Super appreciated!
You are very welcome!!
Hi Eric, you have the best content!! Thank you for sharing with us so much knowledge!!
Great Presentation Plus Well Taught 👍
Fantastic mate!
Thank you very much Eric.
Really helpful.
Glad to hear it! Thanks Mohd
Thank you, that was very helpful!
Thanks a lot for the video.
You got it!
Thank you very very much! This video was very helpful. I subscribed immediately!
Amazing thank you so much Soma!!
Great effort and it’s really helpful , thank you .
Amar - it's really my pleasure. Thanks for letting me know.
Hi Eric,
Keep post more videos with examples
Will do!
Thank you! Thank you! Thank you!
You are so great brthr like seriosly you solved my so many issues
Haha happy to hear it Shreya, cheers 👍
I've waiting for this bruh. U are the awesome.
I really, really appreciate that. Thanks for watching all my videos lately bocobox!
Excellent presentation
Thanks Farhan! Glad it was useful for you 👍👍👍
Excellent
My pleasure Atta! Cheers!
Thank you so much!!!! You’re awesome
Great work ❤
Thanks ✌️
Really helpful and insightful 👌
My pleasure, happy it was helpful!
Thanks a lot for the great video. my question is: for example, I expect you to in JAN to minus returning(churn amount) as $21,250 from new revenue as $25,000 to get the net revenue, while you add it up!! why? what is the point?
Great job!
Thank you! Cheers!
Thanks, Dude; appreciate this.
Glad it helped!
thanks man!
you're welcome!
Thanks Eric.
My pleasure Ngoc!
Hi Eric, could you please make a video on sales forecasts of physical products based on sales cycles? Thanks
Bless you! Great help. Thnx a lot.
My pleasure Ting. Glad it was helpful !!
You are hero 💫🙏
Thank you for the video! Can you make more videos about budgeting and forecasting?
Hey Diego - yes I'll keep making them. I also have two other lessons about forecasting you can check out:
=> Building a 3 statement financial model ua-cam.com/video/xlXDZyZ9azk/v-deo.html
=> E-commerce financial model
ua-cam.com/video/bsmyakxOPLY/v-deo.html
excellent
Thank you from india🙏
My pleasure Raj! Thanks from the US for watching 🙏
Eric Dios lo bendiga ❤️❤️
Y a usted tambien 🙏
very interesting
Nice explanation
@Raja sekhar thanks !!
Can you show us how to create a forecast budget for department? Like corporate that is not related to sale. I wanted to track our department budget such as budget allocation, operating expenses, payrolls, salary & benefit, non cap equipment, office supplies. Thanks
Great!
Hi Eric, thanks for the video. is it new and returning customers or unit?
how to set the forecast percentage for a group of restaurant company (is it monthly or yearly)
Hi Eric, the link to download the template seems to be broken. If you could kindly fix it! Love the video though, great explanation.
Eric can you make a video on lbo model and M&A model too
I love the typing errors, it honestly be like that sometimes
thank you
Hey Tarek - glad it was helpful. Appreciate you checking out my stuff 😁
great video however the text in the sample excel file is blurring
Account Code
Some Department budget items, some are fixed asset e.g. computer , software, chairs, table etc, but not PL
Is it no need to do in monthly PL budgeting model?
Damn! This is a gem.
Nikolai - awesome to hear it. Thanks for the comment!
Kindly create this kind of model for manufacturing company with multiple products
Super simplified presentation of Monthly budgeting etc, however, although I owuld like the template to follow through, I do not see any link. Otherwise great and helpful.
Download link is right in the description 👍
I found your content very helpful. however kindly assist, how to convert this, if we have service based business, and there are several activities ( revenue streams) such as in cleaning, we are getting revenue from General Cleaning, Windows cleaning, Carpet cleaning. Kindly assist. Thanks
Hi Eric, thank you so much for ur efforts, i am havin a new small company , and i need ur advise about the financial issue, since to years the business is not covering the expenses, i want to creat a weekly following up, inorder verify the expenses, orders, profit...etc to avoid seeing big number lost at end of the year😁. Thanks
Hi - thanks for the comment. Happy to give some advice. If the business is losing money, your revenues must be lower than your expenses. You have two options: (1) is to lower your expenses, (2) increase your revenues. Are you able to do either of these things?
@is forecasting of this analysis / Net sales also possible further? And what methods will be most suitable?
I'm 17, why am I watching this at 2 am lol? Hope to learn something!
That's awesome! You're way ahead of the game. I didn't learn this stuff until after I graduated college.
@@eric_andrews hey thanks for the inspiring words! Your videos are amazing for young learners.
@@tasfia5904 keep going !! You're gonna be unstoppable 👍👍👍 my pleasure to help!!
❤❤
Thank you for providing such an informative video. Will this template help me forecast future growth/revenue? As we add additional team members and provide more services I would like to be able to determine what our revenue and profit margins will look like.
Free template
So how do we setup budget cost? What is the formula to determine a budget amount for each task?
Hi Maisha - yes happy to help. There is no formula, but ultimately you have certain financial goals for the future that you want to hit around revenue and profitability for the entire company. So in real life what you first would need to do is first try to calculate revenue and then you would know how much in expenses you could spend but still hit your profit goals. You could give a certain amount (in budget) to each department as well, but it is up to you to design a process that makes sense. Ultimately you would want to add up all the "proposed" budgets and check to see if the plan makes sense and you hit the goals in your plan that are important.
Great video Eric, However, you mentioned that there is a link for a video on how to lock cells. What is the name of the video?
sure here it is ua-cam.com/video/LbQdW39eomM/v-deo.html
Hi Eric, after applying the sumif formula on the month of Jan and got the amount of 15,000, when I drag or paste this amount along the rest of the months, I face Zero in these cells instead of their related expenses!!! what's the solution?
Attached spreadsheet format is compatible...please share the other one
How to prepare company budgets?? Please share a video if u have
What do you mean? This is a video about that
what is the part of forecasting ?
Hey Eric! Trying to understand this best because I help operate a fitness facility, and we don't have a ton of "departments" right now. Would I break down my general expenses by different account and leave the department to the 1-2 we have?
Yes exactly! If you only have "1" department (in terms of the model), that's no problem to just list all the expenses under it.
@@eric_andrews Thank you!
Sometimes forecasts are made off the Trial Balance. is that the 3 statement model or that's something else entirely?
Hey Daphne - trial balances just mean historical financial statements. Yes, you use the past historical information to create your future projections. Does that help?
.It was worth watching, but i have one query, what kind of returing it was, was it sales return or something else as you have added into revenue
In this model, "returning" means returning customers - customers who are buying again (repeat customers). And above it you see "new" customers, so people buying the first time. Does that help?
@@eric_andrews oh great...now got it... thanks for your prompt response
Eric, thank you for the information, really informative. How would I be able to get a copy of your worksheet? I clicked in the link below and there is only the waitlist registration button. Please advise. Thank you.
You clicked the wrong link. Look a little harder 😎
Hi Eric, this video was so helpful, thank you for sharing.
I am currently dealing with a large data, i.e 20,000 bank transactions, I need to reconcile them all and I find it very difficult. Are you able to help? I am willing to pay cus I know its not easy job. Looking forward to hear from you!
Hey Asla thanks for the comment I'm glad it was helpful. What I think you need is the help of an accountant. Accountants organize historical information into reports, and finance people create future projections, so they are pretty different fields. Cheers
@@eric_andrews we appreciate you, well done job , please best site for preparing account and fixed schedule
Hi Eric, what will COGS in case of a service Industry?
COGS are direct costs of a business. It would be completely different with every business but for instance if you had a restaurant the cooks and waiters and ingredients would be COGS (and maybe some other stuff), if you had a summer camp the staff would be COGS, etc. You just have to think about what costs touch the actual product.
Hi Eric, would this tutorial be the same thing they use for spreadsheet formulae/ or First (database) ?
I was just looking at roles for financial paraplanners and that seems to appear in most job description. Thanks
Hi Masato - I'm don't perfectly understand your question, but these are generally the skills of a financial analyst (or any finance roll). Sometimes companies use database software solutions for financial planning. Some of the big ones are Adaptive Insights and Analplan, and there are more. But they aren't very hard to learn, I would just focus on the financial planning concepts!
@@eric_andrews
Thanks Eric for the advice and not a problem, I’m not too sure myself regarding ‘spreadsheet formulae/ database’ which I saw on a lot of job adverts for financial planners. And thought it was the same thing as your tutorial. Thanks though
Eric, my query is in a Excel template how do I give format colours for hardcorde data / inputs???
Hey Sai - good question. You always want to format the hardcoded data in a dark blue. This is standard across all companies, and is a best practice.
What done first budgeting or forecasting????
They are the exact same thing!!
Would this be considered a bottom up or top down forecast approach?
bottoms up 100%, top down is more like just putting 20% revenue growth in (without underlying assumptions) and using that style of logic to build everything. bottoms up is forecasting all the underlying details and then adding it all up in the model
6:35
Do you think it was better to use pivot table instead?
Hi! Yes I Imagine you could also use a pivot table but then you would need to do the extra step of creating the pivot table and then linking it into the income statement. It would work if you wanted to do it that way, but I'm not sure if it really makes it easier / faster.
@@eric_andrews If doing Pivot might as well do in Power BI for better visuals.
Can I get the template sir ? I love your videos much. Thanks
Template is right there in the description for free. Thank you.
You did not calculate NI %