Magazine article Strategic Finance

Master Budget Project: Budgeted Beginning Inventory

Magazine article Strategic Finance

Master Budget Project: Budgeted Beginning Inventory

Article excerpt

In order to calculate the Production Budget, we need the values for both Beginning and Ending Inventory each period. Last month we created a query for Ending Inventory. This month we will focus on two queries we need for the Beginning Inventory values. The first records the beginning inventory value for the first period. The second does the same for all other periods by using the ending value from the previous period.

Query for the First Period

The first query sets the first Beginning Inventory values and appends them to the Budget Table. This involves the Reporting Period and Product tables and criteria for 2012 quarter 1.

Create a query. Add the Reporting Period Table and the Product table to the query layout, but don't link the tables. Include fields for Reporting Year, Reporting Quarter, Product Name, Type, Beginning Inventory, and Value (see Table 1). Set the criteria for Budget Year to 2012 and Budget Quarter to 1. Calculate Value by multiplying Beginning Inventory times Selling Price. Change the query to an append query, and append the fields as shown in Table 1. Save the query as "Append First Quarter Beginning Inventory."

Table 1. First Quarter Beginning Inventory
Query Elements

Field                             Append to     Criteria

Reporting Year                  Budget Year         2012

Reporting Quarter               Budget Quarter       "1"

Product Name                    Budget Item

Type: "Beginning Inventory"     Budget Type

Beginning Inventory             Count

Value: [Beginning Inventory] *  Amount
[Selling Price]

Query for All Other Periods

The next query uses the value from the first quarter to build the other values. This query takes the ending inventory values from the previous period and makes them the beginning inventory values in the next period. It then appends them to the Budget table.

To do this, create a query and add the Budget and Product tables. Link Budget Item to Product Name: From the Budget table, click on Budget Item and drag it over to Product Name in the Product table. Now add IIF functions for Next Budget Year (to increase the year by 1 when the quarter is 4) and Next Budget Quarter (to increase the quarter by 1 or, if it's the fourth quarter, reset it to 1). Table 2 shows the IIF statements and other elements of the query. Note that BudgetType2 is "Beginning Inventory" and that the Budget Type field from the Budget table is used to select "Ending Inventory" values. This, combined with increasing the period by 1, brings the ending inventory value forward to be the beginning inventory value for the next period. Save the query as "Append Beginning Inv Budget Values for All Products."

Table 2. Beginning Inv Budget Values for All Products
Query Elements

Field                                     Append to    Criteria

Product Name                              Budget Item

Next Budget Year: IIF([Budget             Budget Year
Quarter]=4,[Budget Year]+1,[Budget
Year])

Next Budget Quarter: IIF([Budget          Budget

Quarter]=4,1,[Budget Quarter]+1)          Quarter

BudgetType2: "Beginning Inventory"        Budget Type

Budget Count: Count                       Count

Amount: [Budget Count] * [Selling Price]  Amount

Budget Type                                            "Ending
                                                       Inventory"

Update the Macro

Open the Budget Process Streamlined macro in design view. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.