Printer Friendly

Access budget project: production budget.

Now that we have completed the sales budget projections, we can start working on the other budgets. We will follow the same order as the worksheets in Jason Porter and Teresa Stephenson's workbook for Bob's Bicycles. The Production Budget sheet (see "Excel-based Budgeting for Production," March 2010, p. 36) shows that Production is equal to Budgeted Sales plus Desired (or Budgeted) Ending Inventory minus Current Inventory. In order to calculate Production Budget, we will need to store the fields that we use to calculate it: Ending Inventory and Beginning Inventory.

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.


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.


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

Product Name      Product  Ascending  Budget

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

BudgetType: "                         Budget
Ending                                Type

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

Amount: [Budget                       Amount
Count] *

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
COPYRIGHT 2012 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2012 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ACCESS
Author:Cox, Patricia
Publication:Strategic Finance
Geographic Code:1USA
Date:May 1, 2012
Previous Article:VLOOKUP variations.
Next Article:Global XBRL reporting update.

Terms of use | Privacy policy | Copyright © 2019 Farlex, Inc. | Feedback | For webmasters