Building a Basic Business Spreadsheet In Mac Numbers

hi this is gary with macmost.com today on a special live episode of macmost i'm going to be building a spreadsheet from scratch a basic business spreadsheet tracking income expenses and things like that so first i want to start by thanking my patreon supporters macmost is supported by a group of more than 750 patreon supporters you can find out more by going to macmost.com patreon there you can read more about it join us and get exclusive content and course discounts so the basic idea today is i'm going to build a spreadsheet in numbers but instead of just showing it to you like it's happening like magic and just a bunch of steps i'm going to build it live and you can kind of see the experimentation and things that go on when designing a spreadsheet from scratch so let's get to it so here i am in numbers and i'm going to choose a blank template and create from there so really starting from scratch so let's go and start here with the idea we want income and expenses let's start with expenses here so we get the default sheet 1 here with table 1. let's make this table here our expenses okay and then we need to fill out the columns here now you have a header row which is where you label things for each column and you have a header column here now usually the header column this is where you have a unique identifier for every record so for instance if you're tracking people it might be their name might be their id number social security number something like that if you were tracking things like checks for like a checkbook each check has its own unique number but we don't really have that here you're going to have expenses that don't have a unique identifier so i want to get rid of this i'm going to go to format table here and change the headers for the column here get rid of this column so i don't have any header column there because this first one here it kind of makes sense the way i always see it is date right this would be the date of the expense so this next one will maybe be what it is so you know we could have two things we could have like you know what it's from um you know so from or you know the company or you know source or something like that and you can have description like that um so you know this might be like amazon and this might be you know a printer maybe put the description later on here we can do the amount and that's pretty good for now but we're probably going to want to have categories eventually right so maybe we move these over let's do that let's do category then source then amount and then we can do description and let's get rid of the extra columns here so as a sample we might do something like let's pretend we're starting at the beginning of the year so maybe january 5th and let's have a something like a oh i don't know office expense you know which would be like you know paper or something like that so you know we could say it's from amazon we ordered uh some paper and we spent like 20 dollars and that's kind of like how each entry would look the idea here is each row is a record like a record in a database so okay uh just take a look at its comments here so you can always i'm checking the live uh chat if you have any comments or better yet questions as i go along but let's go and populate this with a bunch of extra stuff so let's say on the next day we let's see professional services is another category and let's say this is from you know the accountant and you know maybe we spent you know 75 and i'm not going to put descriptions in here but you can imagine you know maybe that was for like a beginning of the year session on you know budgeting let's do this one another office expense now notice when i start typing off its expense of course it's going to look for other things in this column that match and give me a little pull down menu so i don't have to finish typing and let's say it was amazon and maybe this was like a printer for 400 bucks or something like that you know let's just continue to add a bunch of different things here let's say we add a bunch like this get a bunch more office expenses [Music] office max you know let's get a couple of those in there these really don't matter i'm just making up some data let's say there was a travel expense and airline you know 299 and travel and hotel you know for 450 and travel you know it was a cab for you know 30 bucks or something like that so now we're off to a good start here now usually you don't have any extra rows like that so you go in here and you want to add something new you know you can go do that to meals restaurants just a generic name for restaurant right and 56 dollars and then we'll uh we'll stop there and that's a good sample to start with so yeah so now we're going to go and figure things out here in terms of like what we can do with expenses so let's go first i don't you know i don't want these to be regular numbers here so i want to select this entire column here i'm going to double if i single click it look what happens i select the entire column including the header cell if i double click it i select all the cells in there without the header and then i want to go to format cell change it to currency and you know i've got everything set up here just use the basics there so i like that a lot i could also do the same thing here double click there and then maybe change it to a date and maybe have some other format for the date so let's go and say let's do date and time and let's choose one of these it's like let's choose including the year like that okay uh that helps you know if maybe i type something in differently a little bit every every time maybe sometimes i include the ear sometimes i didn't now i just standardized everything there so we could summarize all this and we could add this up by adding a footer row here but i don't want to do that because basically i want this just to be the ledger you know i enter new things in and what i would like to do is have a sheet where i add all this up so let me let me call this sheet here expenses and it's just going to have this one long table in it and it's just going to keep growing now i want another sheet here and call it summary and i've got the default table here let's say that this default table is going to be the summary for expenses okay so we want to have category and total for it so we can do like this and what categories do we have well we have office expense let's go over here let's total office expense professional services uh let's do travel another one we have is meals good i like that um now how do we do the total well we do the total using a lookup so or i'm sorry look up ha a sum if okay so i'm going to use the equals key on my keyboard to start entering in a formula and i'm going to do sum if now let's look that up here we can search for sumif and look up the definition here let me get me out of the way there and you can see here you've got you know how to do it some if test values condition some values you can read all about those you can look at some examples here always do this until you're confident in using a formula like this so sum if and what we want to do is we want to first get the test values so i'm entering this formula in but i'm going to change to the other sheet and the test values are going to be this one right here category so we're going to test category okay and then comma and what's the condition well let's go back here it needs to equal this okay so if the category in the ledger is going to equal the category listed here in the first cell of this row then we want to add up the values we'll go back here in amount and then we'll close parentheses and that's the formula there now we can see office expense is 560. and we look here we see well we have a bunch of office expenses we got 20 bucks 440 30 70. okay it adds up now if you look at this formula look closely here we can see it's taking the from the expenses sheet the expenses table and it's taking the column for category great it's comparing that to a2 here and a has a dollar sign in front of it which means it is preserved if i click here you can see preserve column so no matter where i move this formula to it's always going to be a it's not going to change to b if i move it over which is fine i'm not actually going to be moving it over but the 2 isn't preserved the row is not preserved so that means if i copy this from row 2 and paste it into row 3 instead of a2 it will be looking at a3 okay so let's go here and copy and paste 75 well what happened here is it's looking at professional services and comparing it to the categories here there's only one professional services at 75. so now i can actually copy this i could double click and be here and paste and it pastes it in all of this i can see travel is 799 and meals is 56. the cool thing here is that as i update this this will be updated as well because the formulas here are not looking at a group of cells it's not looking at from like you know the beginning it's not looking from b uh you know b2 to b11 it's looking at all of b so if i add another one i will add it to this so office expense is 560 now what if i go when i say on february 11th another office expense and that's going to be for 20 bucks and i look at summary now and you can see it says 580 for office expense so it's automatically going to keep adding it up this summary will always be current whenever i add something to this so that's really handy i can keep going throughout the entire year and then this will be up to date obviously i'm probably going to have more than four different categories here so you know if i added a new one like for instance say on 215 i go and i add oh let's say you know let's do utilities and let's say i add you know a 90 bill for utilities that's not going to be included here but easy enough to include i just go here and i hit return create a new row add this and you can see it already picked up since this was a formula it automatically put it here when i created a new column this was a constant so it left it blank for me to fill in so eventually you know i'll get to maybe 10 categories and that'll be it i'll probably never add another category again but at the beginning when you're starting off you may add a few categories as you you know flesh out this table here so cool all right so let's go and uh do the same thing but add a new one for income so income spell it right there we go and that income sheet will have an income table and we'll do the same thing here we're not really going to have a column here for this so we don't have a unique identifier for each row so i'll get rid of this and i'll do the standard thing like date this will be like source this will be amount this could be notes or something like that and oh we're going to need category here let me go here and insert add a column before so maybe on like 131 you know it was a client and you know client a or whatever and the amount was you know somebody paid 375 for something maybe a another client b uh played 200 for something uh you know maybe a freelance photographer it may be a graphic artist something like that so uh you're doing this and then maybe like on two eight uh you had an income from you know ads so maybe you have a blog and the blog has ads and then on two eight you got uh paid for the advertising on that um maybe from google and maybe that was like 200 bucks too that kind of thing and then we could do the same kind of thing here in summary so let's i can actually duplicate this table i'm going to do option and then click and drag the circle here and let me change this to income and we had uh clients did i call it client or client's client okay so let me call it clients that makes more sense as a category name i just want to change these to update it and then we had ads and then these were blank or we should make those blank now the formula here has got to change instead of expenses this now has to point to the income category there it's still going to go a 2 but the amounts are going to come from here so now you can see it's 575. let me copy and paste it there so 575 and 200 which would fit here so now as i add something new like 2 9 clients and client a is back for another 100 bucks and then i go to summary and i could see that's increased so i've got my expenses and my income here let's move summary to the first thing expense as an income really handy just to be able to do this let's go and save this now i'll just save it uh business and now i can keep adding new things to here 38 some more ad money came in from google to 10. now i can see that now i may want to have a total here as well so let me go and add a new row here i just did that just dragging this down i can drag this down but i'm going to go and set a footer row here so that's a footer row so it's different than the rest i can call a total and i can calculate that two ways right one way i could do is i could do a sum of some of this column here and will give me a sum of that i could also have said let's do sum and go to expenses and let's say some of this that would help if i had something that wasn't categorized a matter of fact i could even do two totals here if i want to check everything like i could do another footer here and do total one total two maybe there's like official accounting things for these but like this one could be the sum of all the expenses column d there and this could be the sum of all of these categories now they should be the same right well what if i had another expense and this one i didn't put a category for it's 50 bucks um then i can look here and i can see they're off by some because something didn't get added up into a category or maybe this is like something like let's do oh i don't know rent and you know i get a thousand dollars for rent and i look here in summary now it's off by a thousand bucks there is no rent subcategory here uh let's deal with that like because it would be really easy to add something here as a category and then forget to add it in the summary right um let's say let's say let's have another here so what i could do here is i could add another footer row what should i do this is a footer row yeah let's do this as a footer row here and call this other now how do i get other here this is tricky because what i need is a formula that is going to go and add up the amounts if the category is not listed here that's a little tough to do but it can be done it's tricky though here's what i would like to do i'm going to add another column here and i am going to with this column try to figure out if this category is actually listed here so let's go and do that is category listed so we'll do equals and say let's look up this comma and then in the table for here and close parenthesis and it's going to return what it finds let's go and over here i want to copy this and paste it oop did i do this right let's see here oh no i don't want to paste that uh actually i don't want to do that at all let's get rid of that let's go back see this is doing it live right this is like real life when i'm working on stuff like this i make mistakes like this and i go back and i say no what i want to do is i want to do it over here so let me let me copy that here i'm going to go to expenses and i want to add in a column here yeah that's what i want is category listed i'm going to do equals and then look up this in the summary list here and yeah oops i clicked a bunch of times let's just have it there bam okay so now we do it is category listed and if i copy and paste then it's not going to give me what i want here why is it not going to give me summary expenses is b2 look it up in there let's go and take a look at lookup because it gave me professional services here which isn't what i want so let's go to cell oh sorry in here and then yeah let's do lookup um it returns the value in the cell with the same relativision and second collection so let's take a look at why this one here it's looking at b14 and summary expenses a oh summary expenses a and it's returning its professional services i think it's coming up with the closest one there so let's try something a little different because this one should come up with nothing so look up ah so i'll let me show you what i'm seeing here in the definition of lookup we've got the result values i want that expenses a and look search for search where result values search for b14 which is rent look for it in there give the result values of that that's not what we want we want it to give an error let's try match i think matches what we want search for search ware so let's do that match search for this search where and here and the result values matching method be fine value there gives us an error so that's what we got here we have this match b14 to the summary expenses and find the value and it's giving us an error but if i copy and paste it throughout here you can see it gives me numbers so what we want here is we want to wrap this in is error and this will give us a true there's an error there i'll paste it here so all the ones that are false are matched all the ones that are true are not matched so if we go back here and we want it to have this other thing here we could say sum if and the sum if thing is test values so we'll test the categories against oh no i'm sorry won't test the categories we'll test the is category listed and we will see if it is true meaning there's an error and if it's true then we will grab the amount there now we can see other is a thousand why because there's only one here that has something that isn't listed and that's rent and it's a thousand so now we've got others always taken care of now the goal would be to have other always be zero so i'll add another row here call this rent and you can see rent now is categorized it now doesn't include other because this is false we can get rid of this column not get rid of it we don't get rid of it we want to hide it because we don't need to see it now it's there doing its calculation cool now let's talk about some other stuff but first i want to take a look at the live chat let's see if you send someone a number's document like billable hours summary will it open if they're in windows no because they don't have numbers there is no numbers app for windows so if you want to send them a document like this or anything you want to export to and then ideally you're asking you know document like billable hours summary or something like an invoice you want to send them a pdf you don't want to send a document they can edit right so you want to basically do the equivalent digital equivalent to printing it out and sending it to them but you know pdf is the digital equipment to let to that if you want to give them a spreadsheet like this like you send this to your accountant to be able to deal with taxes and they don't have you know their own windows send them an excel document file export to excel it exports it as excel won't look as pretty but all the numbers will be there yeah so joseph has a thing about the having the first column here i talked about it not being a header cell because there's no unique identifier you can make a unique identifier like he suggests here you can just call transaction number or something assign it your own number it's your own thing so one two three four five whatever you want and then you can kind of identify it you know if you wanted to say like you were you send it to your accountant and your accountant is looking at it you say take a look at entry 57 right okay that's useful but the other useful thing is if you number them you have kind of this entered in value right so 57 maybe february 15th and 58 maybe february uh first because you got the bills on different days um and uh but you may also want to have that original order that you entered things in as sortable so entering numbers in as the first column is is an idea that could work for a lot of people let's go and i'm going to look at a couple things having to do with filtering here let's say that you want to filter things a bit you can do some cool filtering things so let's say you just want to filter this by category so you know you've done this for an entire year and you really want to filter things by category you can go and go to organize filter here and add a filter or just click here and then you could filter do a quick filter um to show only things like let's just show show me the travel stuff boom it's done okay and then you can go here and you can do quick filter and do another thing or you can remove the filter really easily here under organize filter you can turn it off and then turn it on again or you can just get rid of a little trashcan button you can also do smart filters so these are called the categories organize categories and you could add a category for any of the columns here so let's add one for category i believe you could also do it here add category right there boom so now it puts everything together all the office expenses are together professional services together travel together really cool and then you can see summaries of the amounts all you need to do to do that is you've got this special row here that doesn't have a number see row one is the header row row two is the date and everything there you know for the next item those all the all the numbered rows after one are actually like items but this blank this row that does not have a number is the header for each category and you could click here in like a cell like this like in this header row for the category it doesn't matter which one you choose i'm just going to use the first one and under amount i can click here and i can have these special things like for instance give me the subtotal and look at that it gives me the subtotal for office expenses right here and it does it for every single category so there's a subtotal for travel for instance and you could have chosen other things like average minimum maximum all of that so that's another handy way to do that and you can close these up and open them so you can almost kind of get your summary here but you know this way so that's really handy and it's easy to turn these off and go back to this view and turn them on again so kind of handy to have there so how would i create a pull down for the category column entries i don't like to do that here's why because say i go to enter a new one if i start typing like t for travel you can see how it appears or o for office expense um and you know it's so easy to do that that you know i'd rather hit o and then choose from that list then you know click on something like that but if i wanted to i could so how would i do it well i would change let's go and get rid of that i'm just undoing back to that so i want to change this to a pop-up menu so i can select all of these and let's say this is a mature spreadsheet i've been using this for several months it's all built up i have all my categories in here so i double click here to select all of these i go to format cell and i change to pop-up menu now it recognizes that i have certain entries in here and actually puts them in it pre-populates them really cool so i'm done i could hit the plus button and add another one one that didn't exist here if i wanted to or i could just say you know fine just you know go with that i could also say start with blank like there and now it doesn't seem like anything's changed except each one now is a pop-up menu starting with none now if i go to a new entry here say 217 i go here you know i can click here and choose one of these items or i could type something new so let's say payroll let's see i think i have to oh yeah hit delete key right and it will like allow me then to type payroll now the problem with payroll is that you know this one here no longer is a pull down menu and this one doesn't include payroll none of them do easy to change that i just select these all again here and i go data format pop-up menu again and it's going to now include payroll so just by reapplying it like that i've included new ones that i've added here hey 50 well fifty dollars for payroll that's a shame uh how about uh how about five thousand dollars for payroll there we go and now i look at summary and oh five thousand dollars is another because this isn't a category here let's go and add payroll and now it's a category another is again zero okay so let's go and do something else like what if i wanted to use this past the end of the year okay this is all for 2020 and what if i want to use it for 2021 or you know 2019 or whatever you don't have to you could just go and basically duplicate this entire document make a copy of this document you call this business 2020 you duplicate the file you call business 2021 you go into it you select all this stuff and you know you you clear it all out so you know you just go in and just delete it and start over again for 2021. you could do that and that's fine because you know it's only so many years and doing that once a year is not a big deal and then you have all these different documents for different years and it's nice and even and you know it works well i think you could also just have this as a running table what the advantage would be is if say you wanted to see how much money have you spent at a particular place or on a particular service or something like that not just for this year but for all the years we've been in business well you have to go back and calculate that with each spreadsheet same thing for income right i mean if you wanted to see how much money client a had spent over five years you would have to go and calculate it for every year so what if we did this for all years that would be a problem because you want to summarize things for 2020 it's the end of 2020 you want to do your taxes but now you have all this stuff the summary here is like your summary for all the different years we can fix that we can make this something that actually does it by year so let's start off here with a new table and i'm going to have it be a simple table and i'm going to make a cell single cell and i'm going to call this year and let's put 2020 in here all right so this will be at the top here and our summary spreadsheet here is the year and we've got expenses and income okay so let's go into expenses here now we want to filter out everything that's not 2020. let's go in here and let's put a couple entries for 2021 and you know i'll just do oh it's a pop-up menu now office expense and you know hundred dollars let's do another one 2021. let's do travel oh i gotta select from here and let's say this is 500 okay so we have some stuff in 2021. now what we would like is for this not to show up in the summary it is showing up in the summary so how do we how do we get rid of that well we have to compare 2020 to something else we don't have something that shows us the year we need that so let's go and add a new column we'll call it the year we don't have to type in this individually we can use a formula i'm going to do equals and then year and then grab the year from that date so you can see it grabs the year copy it paste it in there you see 2020 all the way through 2021 oh i didn't double click there i should have now you can see okay we can do year like that okay so now we have 20 20 20 21 now when we look at the summary here we could say look we not only want it to match the category we want it to match the year as well so let's go back into here into this formula here it's sumif and we're going to change that to some ifs let's delete that do some ifs with an s which we can do multiple checks but sumifs works differently let's look at the info for that we can see sumifs takes the sum values and then test values condition test values condition test values condition as far as you want to go so let's start off with the sum values here so i'll do the values are going to be from amount great now we want to do test values so we'll test the category there and the condition is this item here now that gives us exactly what we had before just with some ifs instead of some if now let's do another condition and this one we'll look at the year and compare that not to something in this table but to this right here and we're going to click on it and preserve row preserve column it's always going to look at that exact cell no matter where we copy and paste this so two conditions here it's got to match the category here and it's got to match the year here let's copy and paste it in now when we do that we should only have the things that match 2020 because 2020 is here if we change this to 2021 bam we could see now office expense 100 travel 500 which is all that's in 2021. now suddenly we have something we can basically make this happen for any year that's at the top there so we can start this in 2020 and we can go for years and years and years having all of our data nicely packed away in here allowing us to do calculations things like how much money we spent from a source you know if we were to go and do the same thing for income here let's do it let's go and add a column before make it year for this we'll paste it in here we'll call it year let's add for good measure 1 3 20 21 and then we'll do like ads from google 400 there let's go into summary here let's change this as well to sumifs and let's grab the amounts i never set these to dollars and i never set the date things here but you get the idea let's compare the uh let's add up the amounts and then say if the category is this and let's also check the year against this preserving row and column and then here we go and we'll copy and we'll paste that here and we can see if we change this to 2021 now we've got that 400 in advertising that's in 2021. so now everything works for the year we could even go further than this let's go and say we don't want to see everything here we want to filter so let's create another filter here let's do like this add a column after and we'll say is year and we'll just compare i'll just do equals here oh it thinks it's a pop-up menu there okay equals this is equal to we can go to summary here let's set that to preserve reserve column like that and we can see true i'll paste that through here it's true for everything but it's false for 2021. so what we could do here is apply tab filter filter and add a filter that is year as text is true now we don't see all any of the 2021 things but if i change this to 2021 we get our new expenses our new income for 2021 we go to expenses and we only see 2021. nice if we ever want to turn that off and see everything here we can just go to filter and turn that filter off and then we have a complete list that we can work with we could also get rid of this we don't need to see the year here let's go and hide hide this column and let's hide is the year let's do the same thing for income here we could do say add column after and say is year and equals you know if this is equal to what's in the summary cell here preserve row observe column bam paste it throughout false false false true ext on that last one let's set a filter up for is year text is true and now we only see 20 21 in income we only see 20 200 expenses because we have 20 21 x for a year 2020 there we go now we see 20 20 there 2020 here and now we can keep everything in one document document we could back up obsessively because it's really important and we can use it to calculate all sorts of things as our business grows and changes throughout the years let me look at the chat here what's your view on the plus and minus with numbers versus google i don't know what you mean by that so you might want to clarify that um yeah i had issues different people importing yeah well you have to i mean you can't uh correct you know if somebody's not gonna input things correctly uh you know the question was you know what if somebody categorizes things wrong you just gotta go and maybe review the stuff i guess if you export this to xlsx format does it mess up let's try it i'll show you what happens so saving i haven't done anything special here all those functions some if some ifs they match they all are in excel of course so if i go and export to excel and i could choose one excel worksheets one per sheet one per table include summary i don't think i need the summary here there's some advanced options okay let's just go and do that and i will go to the desktop export it out now i have excel but it's i don't have my account set up a filter table cells were exported hidden okay so let's see what we get some of the filtering might give us issues so let's see if it will open up in excel yeah yeah i've got that on my regular account crypto read only mode that's what i want okay so here we go so now we've got summary expenses so there's some expenses there you can see this all works out summary income uh that works out uh there's so summer year that yeah that's just 2020. expenses there yeah income every table is a separate sheet on uh in excel it doesn't have the cool tables functionality you know there's the income so i've got that so yeah so you know the years here these stuff this stuff aren't hidden but you know that's fine um i guess if you really wanted somebody to you know give this to an accountant so they can work on it this is all fine they don't care about this stuff right of course it's beautiful if you want to export it as pdf for somebody else to review but you know this stuff will be there it won't be messed up it'll just have to fit within excel's paradigm of how it displays things somebody asked what screen recorder software i'm using i'm using obs to stream out obs studio so look that up and you can you know set that up with youtube to stream live oh so the oh i see so uh bryant was asking the numbers versus google the pluses and minuses not the plus key and the minus key i mean google's great because anybody could use it you know it's like you just log on and it's a web-based thing um and it's a really cool thing and they use it a lot and whether it's got to be cross-platform stuff i love the numbers ability to have these different tables like this and design beautiful looking uh you know sheets here you know i could go and i'll create wouldn't be just awesome here i can do this i could select let's say these items here let's see i can do this and correct a chart and put it here and look at that how easy that was and the cool thing is is i can include it with uh just in here you know like right there and i could alter that and say hey 66 on um payroll yeah okay uh so you know there's lots of things you can do you can design beautiful things and make beautiful printouts i mean look at when you create a new document look at some of these things like this personal budget or simple budget spreadsheets here you know uh there's some beautiful home improvement you know look at that you know creating beautiful stuff like that more human readable and usable and things so i like that but google spreadsheet's very useful for things yeah use what tools you like to use cool well i think uh i think this is a good uh start here to a business expense spreadsheet um i think you know you could do with a little extra refinement let's go and hide these hide these columns here let's go and set the format for these dates like that let's set the format for the amounts to be currency i like using thousand separator when we get there i don't think i did over here a thousand separator there you know let's color things in make it look beautiful and all of that you know maybe you need some extra columns for some stuff for more information about each thing uh you know that kind of deal but in general you've got some really useful a useful start to a spreadsheet here i'll include this document here right now just you know save it and i'm going to zip it up and include it with the post at macmost.com and you can continue to view this you know the results of this of course it'll be available on youtube and at macmost.com and uh ask me questions there in the comments um and uh if there's any leftover in the live chat just ask me in the comments there as well so that's that about wraps it up thanks for watching you

Channel: macmostvideo

Views: 73,167

Keywords:

Id: 4FW9qXWVbPc

Length: 47min 25sec (2845 seconds)

Published: Fri Oct 30 2020