 Zero Accounting Software 2023, Budgeted Income Statement, Export to Excel, and Modify Part Number 2. Get ready to become an Accountant Hero with Zero 2023. First a word from our sponsor. Well, actually these are just items that we picked from the YouTube Shopping Affiliate Program but that's actually good for you because these aren't things that were just given to us from some large corporation which we don't even use in exchange for us selling them to you. These are things that we actually researched, purchased, and used ourselves. Ugg Slippers. I usually walk around my home in just my socks but I wanted a high quality pair of slippers that didn't have a heel on them so I can slip them on easily, give me a little bit more warmth than just my socks provide and which has a sole on them so I can deal with messes in the home such as spilled liquid or broken glass without getting my socks wet or my feet cut up and the Ugg Slippers do a great job with that. I like the quality of the slippers. They feel like they're going to last a long time. They will probably outlast me so I recommend the Ugg Slippers. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com where we have many different courses. You can purchase one at a time or have a subscription model giving you access to all the courses. Courses which are well organized have other resources like Excel files and PDF files to download and no commercials. We are in our custom zero home page going into the company file we set up in a prior presentation that being get great guitars. We're going to duplicate some tabs and put our reports in them right click in the tab up top so we can duplicate it right click in the tab up top again so we can duplicate it once again back to the tab to the middle accounting drop down opening up our balance sheet report this being a comparative report if you don't have it you can open the standard balance sheet tab and to the right accounting drop down we want the income statement report this being a comparative income statement report if you don't have it you can open the standard income statement in a prior presentation we took the information from a trial balance exported it to excel and then trimmed it down to the information that is basically the income statement information so that we can create a budget from it we double check that by looking at our total income statement here the net income time out to the one three two four which matches what we have thus far on our income statement we took the pennies off one three two four so now what we're going to do is take this information to then a budget out into the future now note that we are we entered two months of data input into the system January and February we're going to imagine that that was like November and December of the prior year so that we can take that data and then project out for 12 months and that also will allow us then to take the information and put it into zero and what run reports for at least two months of which we will already have data actual data that we can compare to the budgeted data that will put into the system okay that said let's go back on over to the budget here and let's continue adding on to this so we basically have our income statement positive numbers for the revenue negative numbers for the expenses so this is the most trimmed down income statement we can have in one column which makes it easy for us to kind of project out into the future I'm going to delete this column over here we don't really need this one so I'm going to put my cursor on the C drop down right click and delete that alright so then let's I'm going to put some months over here this will be where we construct our budget from our data on the left and so I want to add a row up top which will be our header rule row so I'm going to put my cursor on row one selecting the entire row right clicking on it and inserting which puts a row above it and so then over here we'll start and Jan you worry and this is where our data is going to be so I'm going to say Jan tab Feb tab and I should be able to this system should be able to see that pattern so I'm going to select these two months put my cursor on the fill handle and drag to the right and you can see it says March and I can drag on over till we get to December December and then I'm going to center that well it's still highlighted home tab alignment center and we'll go to the font group drop down let's make it black drop down let's make it white black and white there we go and so that looks good I'm going to select all of these now and see if I can make them a little bit thinner little bit skinnier put them on a bit of a diet so we could see it all on one page here you can't fit them all on the page when they're that large let's put another let's put another column in between these two right click and insert another so we have this like kind of breaker between our source data and our budget data alright so now our starting point is going to be this is two months worth of data so our starting point will be let's just take that and divide it by two and that's what we're going to imagine will be our normal numbers all the way down well for each month because that'll be our monthly number now that's way too simplistic because we might have variance on that because we might have some months that are larger than other months we might expect our revenue to go up over time just as a matter of course because of our excellent work and people word of mouth just catching fire so that we start growing and but we'll get it but we'll start off with the baseline which will just basically be if it if it if this was 12 months worth of data we can take it and divide it by 12 now there's two methods that you can basically use by the way you could say I'm going to take my income statement I could make an income statement per month for the prior year possibly and then project that out and say I'm just going to start with an income statement that's actual numbers from the prior year in that way you might be able to capture more of the seasonal differences if you have seasonal differences but oftentimes that that's not the best thing sometimes because you might be improving towards the end of the year so the end of the year might look a lot different the beginning of the year in terms of the business environment so so it might sometimes be better to start off with taking like the whole year or whatever and then dividing it by 12 and getting at least an average of the year and then go from there another method you might say is well at the end of the year I think that is most most in alignment with what I think is going to happen going forward because we've improved towards the end of the year and I think that improvement is going to stick and go forward it's not just a seasonal thing possibly and you think that's going to go forward so it just kind of depends on the business but I'm going to start by saying let's do this we're going to take this number and divide it by two so that number divided by two gives us our monthly amount of the 29 226 now I could just copy that all the way down so I could just take this and copy it down but note that if I copy it to the right it's going to it's going to cause a problem like if I copy it to the right it's going to it's going to then pull this cell to the right because anything that comes from our from our source data we need to be careful of to see if we need to make an absolute reference from it or mixed reference now I could I could fix that by double clicking here and like making this a mixed reference so I can copy it down and across which is kind of a neat thing to know but I'm going to use a different method here instead in February I'm going to say February is going to equal the month before it because that allows me to make a change anywhere in in the following month which will carry forward going forward so therefore I'm not going to use a mixed reference or anything I'm just going to copy this one down and I don't need to and so therefore I don't need an absolute or a mixed reference or anything I'm just going to copy this down and this gives us our information on a monthly basis I'm not going to populate the net income because I would rather recalculate the net income this way so now I've recalculated the net income and so now when I go into February what I would like to do is instead of copying this to the right which I again I could format it so it still pulls the source data I like to say this should be equal to the prior period and so that way when I copy this one all the way forward every cell is equaling the prior cell and that and that way if I make a change out here in August to like 30,000 that change will copy forward by default that's why I prefer using this method so then I'm going to go back on over I'm just going to do that all the way down this equals the prior cell and then I can copy that down I could just fill handle that and copy that down and then I can take all of that and copy that to the right copy it to the right I can't see how far it should go because I'm blind up to the top so there it is I'll just delete these when it went too far you've gone too far you've gone too far this time I'm going to select those and right click you will be deleted there we go that's what you get for going too far so now this is going to be the total over here and I'm going to select this whole column over here and then home tab clipboard and paint brush it and then paint brush this one so we get the same formatting and then I'll just total this up equals the sum our most famous function the sum function very famous very popular everybody's favorite function if you don't like this the sum function it's just because you're jealous you're just a hater that's why everybody likes it it's the best it's the best function it's not if you don't think it's the best function you're wrong you're just wrong man anyways I'll copy this one over to hold on wait a second this should be the sum down here here's the sum and I'll copy this one across and so you can double check your number here that's probably the best check so if you if you followed everything that we did you should come up with this seven nine four four on the total down here alright so now let's make some alterations with this thing I'm going to save it right now so I don't lose it because this is I've got some good work happening here and then I'm going to go back up and say that we want let's start with this one I'm going to start with the rental income I'll make it I'll make it green just so we can see where we are and I'm going to say that that one will say it start we think January is going to be the same but in February it'll be one month worse of data but in February we're going to start to increase it and well there's a couple ways we can increase it we could say over time it's just going to go up I think because we're going to get more business what do I think it's going to go up by well we could make it go up by fixed amount or we can make it go up by a percentage increase that will be compounding or we might have a seasonal kind of thing where we have to basically you know do a seasonal type of thing for a few months that we think are bigger than the rest of the months right so let's say that we think it's going to go up by five percent compounded for the year so in January it was one one three zero so we're going to say this equals and let's take the prior amount and then we're going to say it increases by five percent so I'm going to say times one point oh five meaning a hundred and five percent or one being a hundred percent which would give us the one one three zero plus another five percent over the top of that a hundred and five percent so if I enter that now we have this now it populates the rest across this way because all the rest of them are equaling the prior that's good but I'm going to compound it I wanted to keep increasing going forward so for example here I want this to be equal to the prior one times one point oh five as well now if I just copy that formula across I can just put my fill handle fill handle it copy it across then it should work so so there we have our total at the seventeen nine eighty six alright so let's continue on with the next one I'm going to say let's do this one that's going to the this one let's do this one and so it's going to go up and let's say it goes up in a similar fashion but by ten percent so I'll go to February this equals the prior month times one point one zero one hundred percent plus ten percent one hundred and ten percent one point one zero enter let's copy that across so we'll pick our cursor there and it's going to compound going forward so I'll fill handle that across and there we have it the total at the six twenty four nine six six now another method of increase that you might use is to say well what if it doesn't go up by a percent but it goes up by by a fixed dollar amount for example so let's pick this one and say that we're on February and I think it's just going to increase by a thousand dollars and so I'm just going to double click on it and say it's going to go the prior cell plus one thousand now again that'll populate going across but if I think it's going to increase by a thousand dollars each month I can once again copy that across so now it's not increasing by a percent but just a thousand dollars we think it's going to go up by a thousand dollars each month and compounding across to one hundred and seventeen thousand alright so let's go back on over then and let's go let's go to the cost of good sold let's go to the cost of good sold here and this one this one should be tied to our sales because we should have a constant basically profit margin so if you deal with inventory you would think your cost of good sold would increase in relation to your sales and unless you made a change like a change to the sales price right you increase or decrease the sales price so for example if if we think like it like in January the cost of good sold is twenty two nine seven oh hold on a second twenty two nine seven seven over the sales two nine two two six and that's point seven eight six one percent so you would think in February if I increase the sales to thirty two one forty eight that this should be equal to times the three two one four eight that the cost of good sold would go up to twenty five two seven four now an easy way to do that is we could say well if sale whatever the sales went up by the percent that it went up by we should which was one point one ten percent and we compounded it we could do the same thing for cost of good sold so I could say this will be the same relationship so we're going to say this will be equal to the prior times one point one and and then and that should be the same thing right so if I take this two five two seven five divided by the three two one four eight this is this divided by this we get that point seven eight six right so I should be able to get that all the way across so if I copy this across it'll be increasing when I get to here I should have that same relationship which is this six five five five six divided by the eight three three eight four and there is the same ratio I'm pretty sure unless I forgot but I'm pretty sure it's same okay let's go down to the expense side of things the expenses most of the expenses are probably going to be more standard like like the bank the bank service charge for example it's in material or pretty small probably won't have an impact too much on our on our projections so because of the dollar amount so we'll keep that basically the same and it should be somewhat the same going across anyways and then insurance now note that when you think about your budgets you might also think about a cash budget versus an accrual budget you'll recall if when we when we put our data together we did an accrual concept to deduct the thousand dollars but if you're on a cash system and you're tracking cash closely you know you might track it you might think about the expenses that you're deducting on a cash basis let's just think about it that way just so you can see if that kind of system that might happen because you might say okay when do I pay maybe I paid my insurance twice a year for example so if I pay it like in February I might pay six thousand in February and like six thousand in September for example and so you might have some kind of a difference in your in your reporting from a month to month reporting that would look something like that right all right now let's go down to the wages now with the wages you might have a situation where it's pretty much the same until it isn't right the taxes get a little bit messy because because you could have caps and whatnot on the taxes but usually it's for it's fairly consistent unless you have hourly service and you have variance for seasonal work and whatnot but we're gonna say it's consistent but you might have this a level up kind of situation you might say I think it's gonna be consistent but I predict that I'm gonna have to I'm gonna give raises or something like in June let's say so so that so in June what we think it's gonna go up by 10% or something so if that happened you're gonna take the prior period times 1.1 in June increase in 10% 110% of the prior period bringing it up to 7682 and after that step up you're we're gonna say it's gonna lock in at that higher rate going forward right and that that might be that's like a that's why this nice formula that keeps that that is always equal to the prior sell is kind of nice for the budget now then the taxes right below it we would think in a similar way that we had with the sales and the cost of goods sold that the payroll taxes would go up in relation so I would think I would have a similar relationship between the payroll taxes social security and Medicare employer portion with the other so I could say this is will also go up by we're gonna say 10% as an estimate right so that comes up to the 535 so those increase to the total for the 88 688 and the 6175 let's actually make a minor adjustment say that that happened in July so I made it happen in June so what I'm gonna do is I'm gonna copy May over I'm gonna take May here and copy it over one time and so now and so then so that looks good and then I'm gonna make the adjustment in July which equals the prior period times 1.1 and the taxes equal the prior period times 1.1 in July so now that step up happened in July totals adjusting to 87 990 and 6126 alright so let's go through a couple more of these so we'll say payroll telephone we're gonna say it's pretty much the same going across so we'll keep that as is same with the internet supplies let's keep that the same going across depreciation could change depending on the depreciation method that you're using but if it's a straight line method for example it'd be it'd be similar we might be able to get that directly from the depreciation schedules for example if you had some other kind of but we'll keep that the same going across miscellaneous expense let's keep that now the interest interest expense up here let's make a change to this one interest might actually go down over time if you've got financing and you don't expect to have more financing on a loan for example because as you pay off the loan your interest might go down you could pull the interest directly possibly from your amortization schedule but let's just imagine that it we have the similar kind of process but we think it's gonna go down over time so let's say that we're gonna say instead of increasing it I'm gonna say the prior period times 0.95 meaning it's only gonna be 95% of the prior amount which brings the balance down and then instead of keeping that salt the same it's gonna go down I think I'm gonna say each period so we lower the interest across just so we can see that same kind of principle but this time it decrease instead of increasing all right now the other thing we had up here was this gain on the sale of investments those were like stocks and bonds we're not actually in the business of selling stocks and bonds that was like why it's in like other income so here let's imagine we're not gonna sell any more stocks and bonds I'm just gonna delete this whole thing this isn't something that's repetitive we think that was like a one-time thing so I'll remove that entirely and so now we're at 125 965 on the bottom line so I think this looks good now let's just clean this up a bit clean it up I'm saving it I'm going back here so notice that net income if I look at the bottom line here this is my net income by month so if I double click here we've got our net income summing up this is summing up across now notice that February is a bit of a and something to be skeptical or an issue for us to make sure that we have the cash flow to deal with February you'll notice that we put this 6,000 in there on like a kind of like a cashed-based system which which made us go into the negative right but then but then we're back in the positive and March so that's something we have to make sure we got the cash flow for if we're thinking of it on a cash-based system and then we come up to the 25 20 125 965 for the total on the year okay we can double check this number by the way by summing it up also this way I could sum up this way and I should be able to kind of double check that that number works just from a from a table perspective so 125 so I could sum it up vertically I can sum it up horizontally we should be good either way let's put some brackets around this whole thing because I like the brackets kind of makes things stand out a little bit more selecting home tab font group dropping it down and we want the brackets boom