Access budget project: production budget.
Edit Table, Enter Base Data
In order to facilitate the Production Budget and Direct Labor, we need to add some additional fields to the Product table: Budget Ending Inventory, Beginning Inventory, and Direct Labor Hours. We won't use the labor hours until an upcoming month, but adding the field now will save an edit to the table and form later. These fields should be added to the table with a data type of Number and field size of Long Integer. I also included a description of the fields, as shown in Figure 1, and then deleted the obsolete items from last month.
Replace the Form
We want to make sure we have an easy way to enter and edit these base values, which means adapting the Product Data Entry Form. But adding the details and removing other ones can get tedious. There's an easier approach that can also save time. Once you've edited the Product table, save your changes and close it. With the table still selected in the Objects list, go to the Create ribbon and click the Form button. This creates a new form with the Product fields in the same order as you have it in the table (see Figure 2). It also adds the sub-form that contains the Part Numbers and Units Needed. Save the form using the same name as the form you want to replace--in this case, Product Data Entry Form. By using the same name, everything else in the database that connects or refers to the Product Data Entry Form will still work.
[FIGURE 1 OMITTED]
Production Budget Queries
In order to calculate Required Production, we need to first calculate Beginning and Ending Inventories for each quarter. This is one of those cases where it's much easier to do something in Excel than in Access. We'll start by creating an append query to calculate the Desired Ending Inventory figures. The query will use the values for Sales already calculated by quarter. Then we'll append that data to the Budget table.
Go to the Create ribbon and select Query Design to open a blank query. Add the Budget and Product tables to the view. On the Design ribbon, click on the Append button. From the dropdown, choose Budget. The Append To line will now be available in the query, and you can select the field to append to by using the dropdown list that appears on that line.
[FIGURE 2 OMITTED]
The tricky part of the query is controlling which quarter the calculation is applied to. Because Ending Inventory is a percentage of next month's Sales Budget, the calculations for Budget Year, Budget Quarter, and Count are a little intricate. Table 1 shows the elements of the query. I used Immediate IF functions (IIF) to control the values and quarters. Note the criterion of > 2011 for Budget Year. This addresses when the first quarter of 2012 becomes the fourth quarter of 2011.
You may find another way to do this, but this was the easiest method I have found so far. I tried a number of different approaches until I got it to work. I'm sure there are other ways of doing this and would love to hear from you if you create one.
For this month's file on LinkUp IMA, I also created two queries for quarterly beginning inventories. Download the file to see for yourself, or try to think about how you would create them. We will go through them in detail next month. If you're working ahead on your own, you'll also want to add the queries to the macro and test the process from the beginning.
Table 1. Elements of the Append Ending Inv Budget for All Products Query Field Table Sort Append Criteria Notes To Product Name Product Ascending Budget Item Last Budget Ascending Budget > 2011 Changes the Year: Year budget year IIf([Budget to the Quarter]=1, previous [Budget one if the Year]-1,[Budget Quarter is Year]) 1 Last Budget Ascending Budget Changes the Quarter: Quarter Quarter to IIf([Budget 4 when it Quarter]=1,4, becomes 0 [Budget Quarter]-1) BudgetType: " Budget Ending Type Inventory" Budget Count: Count Calculates IIf([Product the percent Name]= " Basic of next Bike", [Count] * quarter's 0.2,[Count] * sales for 0.05) the two different bike models Amount: [Budget Amount Count] * [Selling
Patricia Cox has taught Excel and Access to management accounting students and other college majors and has consulted with local area businesses to create database reporting systems since 1998. She is also a member of IMA's Greater Milwaukee Chapter. To send Patricia a question to address in the Access column, e-mail her at firstname.lastname@example.org.
|Printer friendly Cite/link Email Feedback|
|Date:||May 1, 2012|
|Previous Article:||VLOOKUP variations.|
|Next Article:||Global XBRL reporting update.|