Printer Friendly

Master budget project: MOB report.

Last month we created queries to gather data for the Manufacturing Overhead Budget (MOB) report and laid out a plan for creating the report and subreports needed. This month we will create the report. It includes a main report, an embedded subreport, and the Predetermined Overhead (OH) Rate calculation. Figure 1 shows the design for the completed report.

Main Report

First, create a query named "MOB DLR." Using the Manufacturing Overhead Budget table as the source, this query will pull the data for Budgeted Direct Labor Hours. The first field should be MOBCategory, and its Criteria line is "Budgeted Direct Labor." The rest of the fields are the first four quarters (see Figure 2). Run the query. Be sure to test, save, and close the query.

Next, use the MOB DLR query as the data source for the main report. With the query selected in the Navigation Pane, go to the Create tab and click the Report icon. This creates a quick report design. Delete everything from the headers and footers. Use Figure 1 as a guide for layout. After the four quarters for 2012, add an additional text box labeled "Year." The calculation for the Year text box is =[2012-1]+[2012-2]+[2012-3]+[2012-4].

In the Detail section, add another row to show the Variable Overhead Rate. Enter ="$1.75" into a text box for each quarter and Year.


To get the Overhead dollar amounts on the report, we need to create a subreport. Open the MOB DLR query and save it as "Variable and Fixed Overhead Data." In the MOBCategory column, change the Criteria line to "Total Variable Overhead" Or "Fixed Overhead" and then run the query. Save and close the query.

Now create a report using the new query as its source. The Detail section will include the same MOBCategory and columns as the Detail section in the main report. As in the main report, add the column and calculation for Year. In the Report Footer for the subreport, use text boxes to calculate the Total Overhead. Each column will calculate the total for the quarter or year, such as =Sum([2012-1]). This report will have just a detail line and a report total line, nothing else.

Save and close the subreport. Open the main report (Manufacturing Overhead Budget) in Design view. Find the subreport in the Navigation Pane. Select it, and then drag it into the Report Footer of the main report. Delete the label above it and place it as shown in Figure 1.

Predetermined OH Rate

I initially planned to create a separate subreport for the Predetermined OH Rate calculation section of the report. As I was working on the main report, however, I realized that I could perform the calculations in text boxes using data from the Reports Menu form. Because that was an easier solution, I went with it even though it was a change in plan.

Under the subreport in the Report Footer of the main report, add text boxes for the three predetermined overhead rate calculations. See Table 1 for the calculations.

Table 1. Overhead Rate Calculations

Control Label                   Calculation

Total             =([2012-1]+[2012-2]+[2012-3]+[2012-4]) *
Manufacturing             [Forms]![Reports Menu]![Variable
Overhead             OverheadRate]+[Forms]![Reports Menu]!

Budgeted DL           =[2012-1]+[2012-2]+[2012-3]+[2012-4]

Predetermined OH                        =[Text29]/[Text30]
Rate *

* Use the names of the Total Manufacturing Overhead text box
and the Budgeted DL Hours text box in the calculation. Your
text boxes may be named differently.

Update Reports Menu Form

To generate the report automatically, create a Delete query to empty the Manufacturing Overhead Budget table. Then create a macro named "Manufacturing Overhead Budget Report Process." This will be used to string the action steps together to build the table and open the Manufacturing Overhead Budget report. Table 2 shows the order of the actions in the macro. Remember to add the SetWarnings action at the beginning and end of the macro to turn off the warnings and then turn them back on.

Table 2. Macro Actions

Action                     Query or Report

Open Query   Empty Manufacturing Overhead Budget
Open Query   Append Direct Labor Hours to MOB Table
Open Query   Append Variable Overhead Rate to MOB Table
Open Query   Append Total Variable Overhead to MOB Table
Open Query   Append Fixed Overhead to MOB Table
Open Report  Manufacturing Overhead Budget Report

Finally, add a button on the Reports Menu to run the macro and open the report.

Best Practice

Start with a plan, but be flexible if you discover an easier or more direct way to accomplish your goal as you work. Also remember to save and close queries before moving along to the next step.

Next month we will create the Schedule of Cash Disbursements.

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 also is a member of IMA's Madison Chapter. To send Patricia a question to address in the Access column, e-mail her at
COPYRIGHT 2013 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2013 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ACCESS; Manufacturing Overhead Budget
Author:Cox, Patricia
Publication:Strategic Finance
Article Type:Report
Geographic Code:1USA
Date:Oct 1, 2013
Previous Article:Counting workdays in nonstandard workweeks.
Next Article:Apple iPhones.

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