Purchasing and inventory reports and graphs all packed into a single screen application it's going to be incredible i've got a lot to show you if you like this type of training if you like those invoicing and purchasing and dashboards and i'm giving you a little bit of a taste of this in this video but if you want to go full on out then perhaps our mentorship is the right program for you inside that mentorship program i'm gonna be creating a full featured invoicing complete with.
Inventory stock management accounting cash flow dashboard reporting full purchasing sharing in sync and a ton of other features in a full accounting application i'm going to teach you everything from concept to cash while i teach you how to build your own applications as we move within the define phase the design and then we go into the development and finally deployment i'm going to teach you every aspect if you want to learn more about that go to dot myexcel my excel mentor dot com.
All the details are there that's a great program i've got hundreds of students in that program that are learning how to create their own financial independence through excel application development all right let's get started because i've got so much to cover in these particular videos i bring them to you each and every tuesday there's a free download below check the description below this application is absolutely free either through your email or facebook down below full download you got that free absolutely free make sure you do watch.
This video also not just download the application use it because there are tricks and tips and techniques inside these videos and i teach you how you can make your own applications every single tuesday i bring you these if you have not yet subscribed please do so here by clicking on the subscription below and clicking the notification icon bell that'll ensure that you get these training each and every day let's get started i've got so much to show you on this basically what i want to do is create a single sheet menu with basically four sections income and expenses invoicing and sales inventory and.
Purchasing and reports and graphs so i've done just a little bit of the basic work just to help us move us along this training could be well over an hour so i want to minimize it as quickly as possible with me you're going to get full courses each and every week i deliver value and i'm so happy to do that each and every week i've got a list of invoices basically once invoices get completed there's going to be a list of invoices here we're going to do some filtering when it comes to reports so we'll go over that i've got some lists here these are just basically account types like customer sales.
Income and expense accounts we're going to need that when we enter transactions categories that you might find helpful for particular income and expenses a list of vendors when we make purchases we're going to need to know who to purchase them from so we've got a list of vendors here i've got invoice item these are the specific items that are created once an invoice gets created so for example these are on invoice number one these are on invoice number two number three four and five and so on so.
Each invoice number has a specific unique id and then there's items attached to that purchase is the same thing nearly the same as of course invoices where this should say p o number po because it's a purchase right p number a vendor a date and a total we have a purchase list we also have purchase items right this is the purchase number these are the items that are purchased on individual purchase orders purchase order number one number two and number three so those are the individual purposes.
So we've got a list there once the purchase order gets created they get put on this list we've got a list of customers nothing interesting on this just a simple list of customers we're just going to use a customer name i've got a list of inventory items basically item name description the purchase quantity you know when you normally purchase make a purchase we want to know what that default quantity is we have the cost of it know what we're going to sell these items for so that's important we also need transactions a list of transactions when we enter a specific expense or a specific income.
Income we need a list of those transactions so they're going to be stored here inside our transactions that's pretty much it that's all we have the database everything we're going to be creating all the work that we're going to be doing is based on this sheet here so that means we're going to have a four different types of categories of course income and expense so let's get started on designing them what i'm going to do is i'm just going to give it the basic header and we're going to go all the way down probably to let's just say bf and i'm just going to give it a little bit of a formatting here.
Basically a fade we'll go into fill effects and then i'll just give it this darker we're going to this will be our theme this is the turquoise here so we're going to go from darker we'll keep this as the theme let me go above here so you can see it and then i want the line below basically all the same let's go ahead and click the last one here the line below will fade into the main color so again formatting the cells on right click then we're going to fill the fill effects and then we're going to fade it down we're going to use the same middle color then we're going to go back.
In to our what is going to be our background color this light blue here turquoise here
Okay everything else will just go down to a large row here everything else is going to contain that main background color other than the first two columns okay so then let's go ahead and just assign it that color into the home we can give it that color right here no no effect so that's going to be our main background color for our application that's good enough there so now as you just saw this is basically shape this is what our shape is going to be.I want to create a menu based on this shape this is just something that i found on a powerpoint presentation it's pretty basic simple it's just a symbol that we're going to be using but i like it because it's got four different parts and i can assign macros to those four different parts so i'm going to zoom out a little bit and then i'm going to drag this up to maybe a central part here i've got some columns preset that is going to help us move a little bit quickly so that's the idea so the idea is that i want to click basically this would be perhaps income and expenses this would be invoicing and sales.
Reports and graphs and then of course inventory i'm in here so let's give this a title we're going to call this capitalized mini erp application and hopefully i don't misspell things but i probably will so if something gets misspelled let there be any rp applications okay so that's basically what i want to do and then what i want to do is i want to each one that i click here it's going to go to a different section so i'm going gonna have one section here one section here one section that's gonna go on to four different sections so it's gonna hide basically this this is gonna hide it's only gonna show one.
Single section at a single time so let's say i'm gonna put uh over on e let's go ahead and go over here the next thing i want to do is i want to add an income and expenses so i've already done some formatting just to help us move things along income and expenses these videos are long enough as they are so i try to do things to make them a little bit quicker but i'll still allow you to show you the entire process so we're going to put that called that income and expenses and then the next section i want to have let's call that is going to be invoicing and sale.
So we'll put that name in invoicing and sales and then also i want probably purchase orders i'm going to put purchase orders probably here i want to put purchase purchasing we can just call purchasing or purchase orders either one is good purchase orders because it's really an order that we're purchasing and then we're making a purchase and then the last one i also want a reports and graphs section okay so we're going to put that's going to be a little bit larger section and basically it's going to allow us to create almost unlimited or many many reports and graphs on the.
Single and i'm going to create three for you right there but you can create as many as you want reports and graphs okay so now we have these three sections i'll check the spelling on those reports and graphs so this is going to be the section only one section will appear at a single time will hide the other columns reports and graphs and purchase order invoicing and sales and income expenses perfect that's what i want let's bring in some additional icons that i've saved that's going to help us move things along and make it look like a little bit more professional so i'm.
Going to bring in all these icons these are something that i've saved and then i'll reset the sizes because they're going to be too big so let's zoom out a little bit see where they are here they've put them here we don't need them all the way over there we don't need them that big so we're going to drag them over here to the main section here and then i'm just going to resize them to something standard which is fine there and then i'll zoom in and then we'll resize them accordingly that way we can work with them okay so this is going to be our logo here so let's bring that over here and.
Then make that a little bit bigger so we can see it i'm going to give it a different color color a little bit more similar to what our application which is here i'm going to do the same thing with this just something a little bit fun this one looks good okay so then the color is consistent mini erp application okay so now what do i have i've got uh let's see i've got a check mark this is going to help us enter transaction i've got here this is going to be for invoicing so i'd like to put that about right here i want uh this one's going to be our home.
Button this is going to help us return i'm just going to bring that over here we're going to use that for every single one and then what i want i've got a let's take a look at this this i'm going to use for transactions so i'm going to bring that down here and this here so then what i want is i got to report some graphs let's use that right about here then i've got another one we're going to use this right here that's going to be for inventory and purchasing so i've got my icons here and that's just what i want i'll line those up a little bit better we can align them up horizontally here and here just to make.
Sure they're even and then we'll just make sure there's some white consistency on them then i'm going to do the same thing let's undo that then i'm going to do the same
Thing here lining them up vertically here and then i'm going to do the same thing here vertically here so everything kind of gets lined up and has some consistency there this one i'd like to use right in the middle here so i'm going to bring this out and i want to actually it's a bit too big so i'd like to make it look a little bit professional in the center there i want to center that but i don't want that to i don't those edges are not good so i'm going to move this to the back here.So we're going to go into the format here and then send backward and then send to the back that's what i want to get that nice look here that looks good okay so now i want some text on here we're going to use these i'm going to bring these over because we're going to use these a little bit later on now i want some text i want to know what we're doing so i'm going to insert a text box here shapes and we're going to insert a text box here and this text box let's just call this income i'm going to capitalize it income and expenses.
That's going to be our first one and then but i want to format that i don't want it to have that so what we're going to do is we're going to remove this shape fill so no fill on the shape we are going to say no outline we don't want any outline on the shape and i want this let's go ahead and put it to 14 on the text and i'm going to white and make it bold that's going to look good so i'm going to bring that right here something like that that should be pretty good there all right basically what i'm going to do is i'm going to duplicate that three more times and i'm going to drag it over here and create titles for each one of them and then i'm going to assign macros to.
Them eventually so this one's going to be inventory and this one's going to be reports and dashboards so let's bring this one over so this one i'm going to be calling invoicing and sales so let's change that invoicing and sales okay good that's just what i want there and then we'll bring it down a little bit here okay i like that that looks pretty good and then the next up what i want is let's bring that what we'll do is we'll enter that in hit enter and bring that down to the next line so it lines up.
Then also going to have let's call this purchasing inventory and purchasing because we're focused on the inventory inventory and purchasing okay good and the last one we're going to have reports and graphs so it's going to give us a name then we're going to be able to assign macros to them and how this goes so we'll create those reports and and graphs okay good let's just take a quick look at that line everything up it looks pretty good i'm going to hold down the control and then down the shift actually and.
Then line those up doing the same thing here just line them up make sure everything gets lined up and looks properly looks very proper okay so now that we've got that all lined up we've got our our application i'm going to move this down drop it down a little bit perfect all right so we're good to go on our home menu this is exactly what i want it to look like and the only thing i might want to do is group them and then of course add a shadow let's go ahead and add a shadow and group them so i'm going to hold the control down and select every single item another way to do that is use the selection option here and just go ahead and selection like.
This okay but what i want to do is i want to put a shadow on this larger shape here first so i'm going to group that shape there and then i want to put a shadow on it so i'm going to go format and the shape effects and just a little bit of a shadow on that to give it a nice look there we go it's just a very slight shadow that kind of brings it out a little bit now what i'm going to do is take off now what i'm going to do is i'm going to use the selection change again i'm going to select everything and then i want to group everything and then that's good and then i want to group.
Hold down the control and group that so basically i'm grouping every shape inside that and i'm going to give it a name we're going to call this home group home and then group okay because i want to group on that okay and every time you group thing you need to make sure that of course it the size and properties are move but don't size we want to make sure this doesn't get stretched any which way vertically or horizontally so let's just move this over here move but don't size that's important okay great so now we have our settings now now we can start to build out.
The rest of the application so we've got our nice little home menu here to bring that up a little bit here and that's a little good once i bring it up it's going to be right in the center that's exactly the way i want it okay moving down let's say we've got now income and expenses so let's put some fields on for the income and expenses i want a date field so that's first let's put in the date i gotta have an invoice date and i also want to have an amount so we're gonna put an amount and then we'll skip a line and then just go to account we need to know what a count is there that's important and a category all right so we can.
Decide to use colons or not let's not use colon so i'm going to remove them so sometimes just be consistent and i also have some formatted automated formats which is going to help us and i'll show you what that is in just a moment okay so we have those three fields and i want memo too so we can assign a memo to a specific income and expense memo and that's going to be merged in the center it's already merchant header just because i pre-filled it okay so when you go into the home we have some formats here so we have some cell styles that i've created i've created two custom cell styles if you want to create a cell style all.
You need to do is just click any cell that you like and just go into cell styles and click new cell style that'll automatically create a new cell style i've done that just to help us so when i create the cell style as you can see i've created this for the field and this for the labels it's going to make it a lot quicker so all i need to do is hold down the control on the labels and then just click cell style and click label and i'm going to do the same thing for the field holding down the control here i've already merged the center just to make things a little and then click field so that's going to quickly be able to create our field so.
Now we've got our income access all we need to do is add some buttons here so that's going to help us out and then we're kind of done with that so let's go ahead and of course i have to add some data validation here under the account and the category so that's going to be let's go ahead and put in those buttons here so i'm going to insert a shape we'll just use a square shape and i'll make it consistent with the color so i've got the color and let's just go ahead and put in something like this but a little bit darker a little bit darker so i want to use a darker color that's.
Too dark how about this that's perfect okay so what i want to do now is basically add the shape to this so what are we going to call basically just going to call this add transaction so i'm going to type in add transaction because that's what i want the user to do so i will right justify that here so go into the home right justify it and put it in the middle and then i'll make it bold the font that's good i just want to leave room for an icon so we've got an icon here so let's put in this icon we're just gonna have to move it to the top so i'm gonna zoom out a.
Little bit and then what i'm gonna do is bring it up and again like i said move it to the top i've got the shortcut here that's gonna move it to the top and then size it now we're going to use the same for the each one of them so it's going to be much easier when we add additional buttons so that's going to add the transaction nice and big just the way i like it going to group that okay and then what we want to do is we have i want to put some icons here too so for the income expense i want to put some icons for each one of those so let's do that now i'm going to copy this.
Icon and just put it over here and make sure when you copy it here it is and then i want to do the same thing for invoice so i'm going to select on the group i'm going to select inside the group not ctrl d which will keep it inside the group but i'm going to use ctrl c and then ctrl v and that's going to put it outside the group i'm going to do the same thing for this one here for inventory i'm going to bring it over here ctrl c and then just like outside the group ctrl v bring it over here and then same thing for reports and graph i'm going to use these icons for the individual sections too so now.
We've got these so let's zoom out here so we can see this is going to be for reports and graphs we'll move that over here and then this is going to be for purchasing we'll bring that over here invoice transactions income and expenses here and invoicing here so now when we zoom in we can use this nice we've got this nice icon we can add some shadows onto that to make it look a little bit better all the way we have here is going to be reports and graphs so i'll move that a little bit too far over bring it over here okay so perfect.
So these are going to be part of the groups for each one and then what we'll do is we can just hold down the control for each one of those and i'll just put a small shadow behind it so they stand out so we've got each one of them now and then we'll do format and then picture effects and then just a shadow on that a little bit should i that way they stand out okay so now we've got invoicing and sales we can line them up too when we zoom out we can line them up accordingly horizontally like this and then just go ahead and we've got one more here invoicing and sales but i think we.
Didn't put a shadow on that so let's go ahead and put the shadow on that one and then line them up perfect okay so now they're all lined up perfectly now we can zoom on in that's just an easy way for us to work through these and all right good so that looks good purchase orders here and we're ready to write some macros in just a moment but i'd like to fill in the cells before we do that so we can see what we're doing so we've got add transaction that's good it's we can we can give it a name what i'm going to do is i'm going to group it with this shape but let's duplicate this button first because i.
Need to use to add invoices and add purchases so i'm going to use control d and then what we're going to do is we're going to move it over to invoicing and sales because invoicing and sales we are also going to have an additional button so i'm going to call that save invoice so just going to change the text here call that save invoice because we're going to need to save that and also want to save the purchase order so save save invoice okay and we can make it a little bit smaller we don't need it that big and then i'm going to duplicate that.
Here and we're going to use that for purchasing so i'm going to bring this over here bring this over here somewhere over here once i create the fields i'll better update it and then purchasing and then we have purchase orders here and then reports and graphs and the reports and graphs will probably use something like a refresh button so i'm going to duplicate it once more and then bring this for this and then i'm going to save this let's call this save purchase okay and we can expand that just a bit more okay all right so then one more time we're.
Going to actually we can undo that let's go ahead and ungroup that i should say because i'm going to use a different icon i'm going to delete that icon we're just going to call this refresh report all right and then i've got a refresh icon if you remember in column a right here so we can bring that over let's zoom out and then just slide that over and bring that all the way over here to our refresh bring it up right here so once we we do that and we can also bring it to the top here and i just brought it ahead in front of the button and we can size it.
Accordingly something like 0.3 should be fine and bring it down okay so now we can see everything it looks nice we're going to zoom in a little bit check the sizing on that i'll make it a little bit bigger here now we've got everything properly justified here we'll center that group it we don't need need to name these buttons but we do need to group it with their icons and then what we're going to do is group them together all right very good but before we do group we need to once the user gets.
Is viewing this section we need them a way to return back to this home screen here so we're going to create this home button here but let's create that in the button the first thing i want to do is this if you look at this it's kind of facing the arrow is facing the wrong way i'd like the arrow to face to the left because we're actually going back so what we can do is we can just rotate that and we can flip it horizontally that's really what i want and i want to put that in a button something similar to what we have so let's insert but i want to do a round shape so i'm going to insert a round.
Shape something like some big button like this i'll make sure it's perfectly round so we'll change that to 0.6 and then now it's perfectly rounded we'll give it the same fill and then the same look the same effect i think we had basically this preset here something like that is fine okay so we'll bring that back on top we want the home button on top make that a little bit smaller so .35 should be consistent and that's going to give us a nice home button we're going to use that button for each group to have them return so i'm going.
To select on both of those or use my selection tool to group them align them both vertically and generally and then group them we don't necessarily need to give it a name because it's going to be part of a larger group that group will have a name so undo our selection and basically what i want to do is have this as a group and group these three items so i'm going to duplicate this home button i'm going to use that same button for each section one for the invoice here one for the purchase order let's take a look copy it and one for the purchase order here.
And one for the reports and graphs here okay so we've got that home button i'll worry about the placement a little bit later of that button but it's going to be something like this let's go ahead and put out some fields before we group everything so that we can see it properly we've got income expense that's done let's focus on the invoicing and sales and zoom in and see what fields we want it's going to be relatively limited just going to keep it basic for now so we can get everything done because we've got a lot to cover in this so the first thing what i want to do is i want to put in a customer name here so let's do that all right so.
Let's go ahead and bring this over here and then put in i'll put in the customer name so we want is customer name here and then i also want the date so i want to put the invoice that would be the invoice date so we've got some fields here again holding down the control button what we're going to do is i'm just going to format it again using our cell styles our fields and then doing the same thing here holding down the control button cell styles and then label okay now we've got that now let's i also want an invoice total here so somewhere around here i want the invoice.
Total put that invoice total here and then i want to have the invoice total here and then basically what i want to have is our invoice fields let's say right here so i'm going to put in the item i want the item here the description and the quantity and then i'm going to have the amount and then the total okay so those are the those are the information i want here so let's center those if they're not already and then i'm going to create a little bit of a just a different color background something similar to.
Our theme so we're going to go into the fill effects and then just create a reverse field here so that they can see a clearly a difference between those and then click ok and then what i'm going to do is i want to put a border around it consistent with our theme using this color here i'm going to go border all the way around like this that'll be good okay so now we've got our invoice told i'll do the same thing here format those cells and then i'll put a border around here and then i'll put the dotted line in the middle so that it designates the border okay so we've got that cover we've got an invoice total this is going to be total.