|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Profit & COGS By Richard Rost 
5 years ago
Net Profit and Cost of Goods Sold for Orders In this video, I'll show you how to take my free Invoicing Template and add a unit cost to each line item. This will allow you to calculate your net profit for each order and, in turn, your Cost of Goods Sold (COGS). Emerson from Chesterfield, UK (a Platinum Member) asks: I'm using your Invoicing Template. How can I add the cost of each item sold so that I can calculate my net profit for each order, and my Cost of Goods Sold for the quarter? MembersMembers will add Unit Cost to the Product Table in the Member version of the Invoicing Template. We'll add it to the product combo box on the order form so it automatically populates into the order detail form. Then we'll create am aggregate query showing COGS for each quarter.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, calculate profit, gross profit margin, cost of goods sold, cogs, profit for each month, profit for quarter
IntroIn this video, I will show you how to track profit and cost of goods sold (COGS) in your Microsoft Access order entry invoicing database. We will add a unit cost field to your order detail table, update your queries and forms to calculate the extended cost for each item, and display net profit for each order. I'll also show you how to easily calculate your cost of goods sold so you can keep an eye on your business's performance.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to calculate profit and cost of goods sold in your Microsoft Access order entry invoicing database.Today's question comes from Emerson in Chesterfield, UK, one of my platinum members. Emerson says, I'm using your invoicing template. How can I add the cost of each item sold so that I can calculate my net profit for each order and my cost of goods sold for the quarter? Well Emerson, this is just a matter of adding the unit cost to your order detail table and adjusting your queries and forms accordingly. Let me show you how. Now before we continue, I'm assuming that you've watched the invoicing template video. If not, pause this and go watch it right now and then come on back. Here I am inside of my TechHelp free template. You can download a free copy of this from my website. I'll put a link down below. Now Emerson is a platinum member and the members version has a few more bells and whistles in it, but I'm going to show you the basics in the free version first. The first thing we have to do is edit our order detail table. We have a unit price. Now we just have to add a unit cost, and that's a currency value. That's what we pay for the item that we're selling. So if you buy, let's say, hard drives at $100 and you sell them for $125, now you can track your unit cost along with each item that you sell. Save that. That's all for that table. Let's put some values in here. Here's my unit price for each item. Over here is going to be the unit cost. Here's $30 for that. Let's say we bought it for $25. We bought that for $6. We bought that for $3. We bought the phaser bank for $12.50. The photon torpedo is $3,200. Let's say $80, $40. And this test one down here, let's give this some value over here. Let's say $5 and then $3. Looks good. I like to put sample data in my tables before I do too much of the database. By the way, if you want those prices and costs saved in a product table where you can just pick them, that's covered in the extended cut for the members. One more reason to join. Let's go ahead and edit our order detail query. Right-click, design view. We've got the extended price in here. Now we need the extended cost, the cost for each line item, which is the unit cost times the quantity. The extended cost is going to be the quantity times the unit cost, which unit cost should now show up in here since we added it to our tables. You're right there. Save that. If you run it now, you'll see the extended cost for each item. You can format that as a currency if you want, but you don't really have to. We're never going to work directly with the query. Let's close that. Now let's do the same thing with the form. Where's the order detail form? Right here, design view. We're going to have to make it a little bigger now. Slide that open. I'm just going to copy and paste all of this stuff. See all that? Copy, paste. Now it slides it down below it. Slide this up over here. Slide these up over here like that. Slide this one over here. Now we'll just line this stuff up like that. Line that up like that. Actually, this has to be lined up on the right. Slide that up. Slide that up. Slide that up. Save it. Now we'll just do some editing. Let's come in here. This will now be the unit cost. This will be the extended cost. One thing that I hate is when you do that, notice how it got a little bit bigger like that. I can't stand that. Get back on that grid. Sometimes it just pops it up just a little bit. Access team, fix that. Unit price right here. Now we're going to change this to unit cost. Don't forget to change the name too. We don't want it, text16. Alex will yell at us. If you don't know who Alex is, you need to watch more of my TechHelp videos. Extended price is right here. We're going to change this to extended cost. Copy and paste that over the name. You need to line it up on the right. There you go. Now down here, this one over here is the sum of the extended price. I called it sum extended price. This one's going to be the sum of extended cost. This is a form footer total. Sum extended cost. If you don't know how to do a form footer total, go watch my video on form footer totals. I'll put a link down below in the link section. Let's close this, save it, and let's open up just the order detail form and you can make sure everything's working. I've got one unit price of seven, unit cost of six. Let's make this two real quick. Two. Look at that, 14 and 12. Perfect. Down here, add that up if you want to. Looks like it's about right. I'm not doing it. Use a little calculator app if you want to. Trust me, it's good. Now, this is all of the items. So let's go back into our order form now. Here's the order form. You can't see it. That's because the order form isn't wide enough. Now you have two options at this point. You can leave it kind of hidden like that if you don't want your customer looking over your shoulder, for example. But you have to remember to tab over there. See how you can just tab over. You can turn the horizontal scroll bar on if you want to. I covered that in the invoicing video. Or you can make your order form bigger so your employees don't forget to put the cost in. Right-click and design. Slide this subform out about yea far. Don't go too big. Sometimes I have a hard time grabbing the exact edge. Bring it right about there. Good enough. You can make this stuff bigger now if you want to. Make it look nice. Save it. Now let's go back to the order form. There you go. Now you can easily see the total price for each order, the sale price, and your total cost for each order. Now it's easy to calculate your net profit. Let's go back to the order detail subform. Design view. This is always easier to edit subforms by themselves instead of editing them inside the parent form. It's just easier this way. You might sometimes want to do this too. Let's take this guy, put it like there. Put that underneath it. Maybe un-bold that. Let's add some labels, copy, and then we'll paste it down here. What I did is I clicked on that and then I pasted it. That attaches the label to that text box. This will be the order total. We'll bold that so people know what they're paying. Copy, paste. Maybe un-bold it. We'll call this the total cost. That's your cost. Now we can easily calculate our total profit. Let's just copy this. Copy, paste. We'll put down here net profit. What's this going to be? This is going to be, we've got the sum extended price, right, the name of it up here, sum extended price, sum extended cost. So this guy is going to be the net profit, which is going to be equal to the sum extended price minus the sum extended cost. See that? You can use those little pop ups if you want to. I'm old school. I almost never use those little helper windows. I appreciate them, but I never use them. Save it. Close it. Open it back up again. There you go. There's your net profit. Make a change up top. Give me two of those. Now I made $10.50. Now you've got the figures in your table. You've got the cost and your sale price in your tables. You can calculate your cost of goods sold. That's easy to do. That's just an aggregate query. If you don't know what an aggregate query is, I've got a video on that. It's free. Go watch my aggregate query video. I'll put a link down below. Or you can sign up and become a member, and in the extended cut I'll show you how to do a complete cost of goods sold for the entire quarter, and we'll make these changes to the members version of the template so you can just pick from the product list and it'll put all the stuff up top here. That's for the members. Here's the members database. They've got a shiny new coat of paint. They've got an order list. They can pick an order. Down here, now we're going to add a mouse. Add. They have a list of products to pick from. Now it adds in the unit price and the unit cost and calculates the stuff down here automatically. We've got a cost of goods sold query that we can open up, and it'll show the quarter, total sales, cost of goods sold, and net profit for each quarter. Let's get them all together. That is covered in the extended cut for members, adding in the cost to the product combo box and the cost of goods sold quarterly query. Silver members and up get access to all of my extended cut videos. Gold members can download the templates. How do you become a member? Click the join button below the video. After you click the join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the show more link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the first step Richard recommends for calculating profit and cost of goods sold in an Access invoicing database?A. Add a unit cost field to the order detail table B. Create a new database from scratch C. Delete and reimport your tables D. Only change the product table Q2. What does the "unit cost" field represent in the order detail table? A. The price at which you sell the item B. The price your customer pays after tax C. The price you pay to purchase the item D. The shipping cost of the item Q3. To calculate extended cost for each line item, which formula should be used in the query? A. Unit Price x Quantity B. Unit Cost + Unit Price C. Quantity x Unit Cost D. Unit Price / Unit Cost Q4. Why does Richard suggest putting sample data in tables before making too many database changes? A. So you can set up user login credentials B. To help test and visually verify the database design C. To make the file size smaller D. To ensure prices remain fixed Q5. What is the purpose of a form footer total in the order detail form? A. To display the sum of prices and costs for all items listed B. To lock the form for edits C. To show only the first item details D. To change the database password Q6. What calculation gives you the net profit per order on the form? A. Unit Cost + Quantity B. Total sales + cost of goods sold C. Sum Extended Price minus Sum Extended Cost D. Extended Cost divided by Extended Price Q7. How can you display cost and profit fields to users so employees do not forget to enter required data? A. Make the form window smaller B. Hide the cost field from all users C. Expand the subform or parent form to reveal the fields D. Only display cost fields on printed invoices Q8. What is an aggregate query used for in this context? A. Grouping customer addresses together B. Calculating totals, like cost of goods sold over a period C. Sorting orders alphabetically D. Creating new database users Q9. In the members version of the template, how are unit price and unit cost added to order details? A. They must be typed manually every time B. Users pick from a product list, and prices and costs fill in automatically C. Only admin users can enter them D. They are not included in the members version Q10. What additional benefit do Gold members receive, besides access to extended cut videos? A. Free computers B. Access to a download folder with sample databases and the code vault C. Personal database consultations D. Physical textbooks by mail Answers: 1-A; 2-C; 3-C; 4-B; 5-A; 6-C; 7-C; 8-B; 9-B; 10-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to calculate profit and cost of goods sold in a Microsoft Access order entry invoicing database.Recently, I received a question from a student who wanted to know how to add the cost of each item sold, so they could calculate net profit for each order and determine cost of goods sold for the quarter. To accomplish this, you need to add a unit cost field to your order detail table, make some changes to your forms and queries, and then you will have the foundation necessary for tracking these figures. Before starting, it is important that you have some familiarity with my invoicing template video. If you have not seen that yet, I recommend watching it to understand the basics. For those using my free TechHelp template, you can always download a copy from my website. Members have access to a version with more advanced features, but the explanation here will focus on the basics that are covered in the free template. The initial step is to update your order detail table. Alongside your unit price field, create a new unit cost field as a currency type. This field will represent what you pay for each item. For example, if you purchase a hard drive at $100 and sell it for $125, the unit cost is $100 and the unit price is $125. After adding the new field, save your table. It is a good idea to input some sample data in your tables to test your changes as you build the database. For example, enter some realistic unit prices and corresponding unit costs for a few products. This makes it easier to check your work as you update queries and forms. If you would like to save product prices and costs directly in a product table, which allows you to simply pick from a list instead of entering costs manually, that is a topic covered in the extended cut for members. Next, move on to editing your order detail query. In the query, after adding the unit cost field to your table, you can add a calculated field for extended cost. This value is simply the quantity times the unit cost for each line item. You may format this as currency, but that is optional since you will not be working with the query directly. Now, update your order detail form to show the new cost information. Resize the form as needed, then copy and add new controls to display both unit cost and extended cost. Make sure the labels and control names are updated so that everything is clear and organized. You may also want to add a total for the extended cost in the form footer, similar to how the total price is displayed. If you are not familiar with creating footer totals, I have another tutorial covering that topic. When testing the updated form, enter multiple quantities and verify that the calculations update correctly. For example, increasing the quantity for a particular item should reflect the correct extended price and cost totals at the bottom of the form. Because some layouts can make it hard to see the new fields on your main order form, you have a couple of choices. You can either leave the cost column slightly hidden from view, which may be useful if you have customers nearby, or resize the order form to make the subform more visible for your employees. Adjust the form size as you see fit so your staff does not forget to enter cost data. To keep the layout user-friendly, especially in the order detail subform, you might want to reorganize and clearly label total fields for extended price, total cost, and net profit. Calculating net profit is straightforward as it is simply the sum of extended price minus the sum of extended cost. Double-check that your calculations reference these summary fields properly. Once you have this set up, each order's profit is clearly visible, with cost and sale price tracked in your tables. To analyze your cost of goods sold over time, such as for a quarter, an aggregate query will handle this. If you are unfamiliar with aggregate queries, I have a separate video explaining them, and you can find a link on my site. In the extended cut available to members, I demonstrate how to perform a full cost of goods sold calculation for an entire quarter and apply these changes to the more advanced member version of the template. This includes picking from a product list, which automatically fills in unit price and cost, as well as setting up a cost of goods sold query that reports total sales, cost of goods sold, and net profit by quarter. Silver members and higher receive access to all of my extended cut videos. Gold members can download the databases I create as sample files, along with access to my Code Vault filled with various helpful routines and functions. Platinum members get these benefits plus full access to my beginner and some expert courses, not only on Access but Excel, Word, Visual Basic, and more. For those sticking with the free content, I will continue producing these TechHelp videos as long as you find them helpful. Remember to give a thumbs up if you found this video useful, leave comments if you have questions, and consider subscribing to my channel so you get notified when new videos become available. You can also join my mailing list if you would like email updates about new videos, since YouTube notifications can be inconsistent. If you are new to Access, I highly recommend trying my free Access Level 1 course. It covers all the basics and runs over three hours. It is available on both my website and YouTube channel. Level 2 is just one dollar or free for all YouTube members. If you would like to have your question featured in a future TechHelp video, please visit my TechHelp page and submit your inquiry there. For a full video tutorial with step-by-step guidance on everything I covered in this article, visit my website at the link below. Live long and prosper, my friends. Topic ListAdding a unit cost field to the order detail tableEntering unit cost sample data for products Editing the order detail query to calculate extended cost Calculating extended cost as quantity times unit cost Updating the order detail form to display unit cost and extended cost Formatting and positioning form controls for cost fields Adding a form footer total for extended cost Adjusting the main order form to display new cost fields Calculating net profit on the order detail subform Displaying total sales, total cost, and net profit on forms Explaining how to create a cost of goods sold aggregate query |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access calculate profit, gross profit margin, cost of goods sold, cogs, profit for each month, profit for quarter PermaLink Profit & COGS in Microsoft Access |
