Content area
Full Text
This month we will begin the process of creating the Direct Materials Budget table. The report shows the Required Production totals for each budget item as well as the Direct Materials Needed broken down by Basic Bike, Deluxe Bike, and Totals. It also details Desired Ending Inventory by part and Total Needs for production. The report then subtracts Beginning Inventory of parts in order to calculate Direct Materials to be purchased, cost per unit, and cost of purchases. This leads to a Schedule of Cash Payments for accounts payable. (For the original Excel version of the Direct Materials Budget, see Teresa Stephenson and Jason Porter's "Excel-Based Budgeting for Production" in the March 2010 Strategic Finance.)
In order to create this report in Access, we'll build a table for the data one section at a time, involving numerous queries. This month we'll build the data for the Required Production and Direct Materials Needed (up through row 36 of the spreadsheet) using four queries.
First, create a query that uses the previously created Total Production Units query as a data source. Save this query as "Production Units for Direct Materials Report." Change the query to a crosstab query. Include the Budget Item field as a Row Heading, Production Units as a Row Heading, and...