Create Seamless Cash Receipt Template Excel for Teams

Cash receipt template excel for teams

in this video we're going to put together a cash receipt schedule for the months from march through august and this schedule is important because it will eventually fold into our cash budget schedule so we're just trying to figure out how much money we're actually taking in from our sales now the problem is when you offer credit to your customers that means you're giving them the option to pay later so when you make a sale you can't just assume that you're going to receive the cash in the same month so that's why we're doing this let's read through this problem it says simpson glove company has made the following sales projections for the next six months all sales are credit sales so all of them are on credit so i have them from march to august and the first line on my sales receipt template is sales so let's go ahead and put them in so march is 41 000 april is 50. may is 32 000. june is 47 000. july is 58 000 and august is 62 000. so even though i'm just looking from the period march to august i will need to know what the sales are in january and february because we'll end up collecting some of the money from those sales in the months that i'm looking at so in sales in january and february were 41 000 so i'll put that in and 39 000 respectively so now what i'm trying to do is figure out when i'm actually going to collect the cash now in real life usually you'll have the historical data to help you make that decision or you have knowledgeable people that are in the company that monitor that and would be able to tell you but in our case they're just going to arbitrarily tell us uh percentage so it says experience has shown that of total sales receipts 10 are uncollectible so since i'm just looking at the cash i can't even put 10 percent down here so that's just bad debt but then it says 40 percent are collected in the month of sale so i'm just going to just help me with this i'm just going to put 40 right beside january and then 30 are collected in the following month so i'm going to put that down and then 20 are collected two months after the sale so i'm going to put that down now it's hard for me first to identify the pattern that's why i color-coded everything but once you have the pattern down then it's easy to make these calculations so let's use january as an example so i made a sale in january for 41 000 and i was able to collect 40 percent in the same month then in february i was able to collect 30 percent of that and then in march i was able to collect the remainder 20 of that and then ten percent i think is uncollectible forty one thousand times twenty percent there we go so that's why i highlighted those all in yellow because this is the sale and this is the cash that i received so let's go and do the same thing for february so i collect 40 percent in february i collect 30 percent the following month in march and then i collect 20 percent two months after so in april march 40 of that sale in the current month 30 next month and 20 percent two months and i'm afterwards to keep going april 40 in the same month 30 percent the following month 20 percent two months afterwards may i'm going to do 32 000 times 40 because i collect that in the current month i'm gonna take 32 000 times by 30 percent because i collect that in the following month and then i take 32 000 and multiply that by 20 and that's collected two months afterwards with june i'm going to take 47 thousand that's the sale forty percent is collected in the current month thirty percent of june sales is collected in the following month and then twenty percent of june sales is collected two months from june same thing with july 58 000 in the current month that's 40 30 in the following month and because i don't have september or i'm not looking at september i don't put it on my schedule but if i was putting a balance sheet together then anything that i haven't collected from july would go on to my balance sheet as accounts receivable and let's do the same thing for august 62 000 times 40 percent i collect in august and then the remainder of august sales when you collect in september and october will be accounts receivable so now all i need to do is add up all my total cash receipts so i only want to add up the cash so it's the cash from the current sales plus the prior month sales and the two month sales so it's just these three added together and i'm not even going to bother totaling january or february because those are incomplete because i don't know what the sales were prior to that so an easy way in excel is if i want the formula to be the same instead of having to type it in all the time you can just drag it so just drag this across and then it'll subtotal everything for us so then these amounts here is what we'll end up putting on our cash budget when we do that next