BookCases Budget Project
ACC 202 – Fall 2015
Due Friday, November 13
1. The BookCases Company manufactures hand-crafted natural wood book cases.
Currently the company makes only one size of book case, which is 5 feet tall and has
5 shelves. The final product consists of a routed, sanded, assembled, and stained
poplar wood book case. Direct materials include poplar shelving wood and red oak
plywood backing board. Other materials, such as wood screws, sand paper, stain,
wood glue, and packaging, are treated as indirect materials. BookCases is preparing
budgets for the fourth quarter ending December 31, 2015.
For each requirement below prepare budgets by month for October, November, and
December, and a total budget for the quarter. The previous year’s sales (2014/15) for
the corresponding period were:
October
November
December
January
February
2,000
2,800
4,000
4,400
3,600
book cases
book cases
book cases
book cases
book cases
The company expects the above volume of book case sales to increase by 15% for the
period October 2015 – February 2016. The budgeted selling price for 2015 is $280.00
per book case. The company expects 40% of its sales to be cash (COD) sales. The
remaining 60% of sales will be made on credit. Prepare a Sales Budget for
BookCases.
2. The company desires to have finished goods inventory on hand at the end of each
month equal to 15 percent of the following month’s budgeted unit sales. On
September 30, 2015, there were 345 book cases on hand. (Note, an estimate of sales
in January is required in order to complete the production budget for December).
Prepare a Production budget.
3. The book cases require two direct materials: poplar shelving wood and red oak
plywood backing board. Twenty (20) feet of 12×1 poplar shelving wood are required
for each bookcase produced. Management desires to have materials on hand at the
end of each month equal to 20 percent of the following month’s bookcase production
needs. The beginning inventory of poplar wood, October 2015, was 9,752 feet of
wood. Poplar wood is expected to cost $2.00 per foot.
Red oak plywood backing board is purchased by the sheet and 4 backing boards can
be cut from each sheet. Management desires to have plywood on hand at the end of
each month equal to 10 percent of the following month’s production needs. The
beginning inventory of plywood, October 2015, was 60 sheets. Red oak plywood is
expected to cost $24 per sheet. (Note, budgeted production in January is required in
order to complete the direct materials budget for December. Also, use the @ROUND
function to round up to the nearest whole number the number of sheets of plywood to
purchase). Prepare a Direct Materials budget. Also because two direct materials are
required for production – poplar wood and red oak plywood – you will need a separate
schedule for each direct material.
4. Each book case requires 6 hours of direct labor. BookCases uses a series of table
saws, table routers and sanders set up for specialized operations to achieve production
efficiencies. Direct labor costs the company $18 per hour. Prepare a Direct Labor
budget.
5. BookCases budgets indirect materials (e.g., wood screws, sandpaper, stain,
packaging) at $16.50 per book case. BookCases treats indirect labor and utilities as
mixed costs. The variable components are $5.40 per book case for indirect labor and
$2.20 per book case for utilities. The following fixed costs per month are budgeted
for indirect labor, $60,000, utilities, $4,000, and other, $61,800. Prepare a
Manufacturing Overhead budget.
6. Variable selling and administrative expenses are $32.50 per book case sold. Fixed
selling and administrative expenses are $100,000 per month. These costs are not
itemized, i.e., the budget has only two line items – variable operating expenses and
fixed operating expenses. Prepare an Operating Expenses budget.
7. Prepare a Budgeted Manufacturing Cost per unit budget. Refer to exhibit 9-11 for
guidance. To calculate FMOH/unit calculate total FMOH for the year and divide this
by budgeted production for the year. The total production volume for the year is
budgeted at 48,000 book cases.
8. Prepare a Budgeted Income Statement for the quarter for BookCases. Assume
interest expense of $0, and income tax expense of 35% of income before taxes.
Directions:
Refer to Chapter 9 (The Master Budget) for guidance in setting up your budgets and
schedules. Adapt your schedules for the specific details outlined in the requirements
above. Prepare your budgets using Excel. Use formulas and cell references so that any
change you make in one budget is carried through to all the budgets. There should be
no hard keyed numbers in your formulas. For example, if you change the ‘sales volume
increase’ from 15% to 12% you should see effects of that change throughout the other
budgets. Likewise, if the budgeted selling price per book case changes from $280 to $285
your spreadsheet model should be able to quickly and easily accommodate this change,
i.e., change the input cell for budgeted selling price and see the effect on income.
The spreadsheet will be graded on presentation, correctness, and quality of your
spreadsheet model (i.e., does it update correctly for changes in input variables). See the
grading rubric on Canvas. You should approach this assignment as if you are the
Management Accountant at the BookCases Company and you are going to present these
budgets in a meeting to the CEO, CFO, and other management personnel.
Some general principles to follow in constructing your excel spreadsheet model:
1. Prepare an input area in which you enter all input variables – e.g., selling price,
budgeted volume increase, feet per book case, ending inventory percentage, etc…
2. Each schedule should refer to the input area to source data (see sample
spreadsheet file). If possible, keep all constant values together in one area of the
worksheet. An important principle of good spreadsheet design is to keep just one
copy of each constant value. That is, enter a constant value in only one location in
the worksheet. Then if you use the value in another cell, use a cell reference that
refers to the constant value’s unique location.
Example (hypothetical): You enter the constant value of 6% for sales tax
in cell E5. When you write a formula in your worksheet that requires sales
tax, reference E5 in the formula instead of "hard coding" in the 6% value.
Do: =subtotal*E5
Don’t: =subtotal*6%
3. There should be no hard-keyed numbers in your formulas – e.g., the formula to
determine current period sales in units should reference last year’s sales volume
and a cell with the volume percentage increase.
4. Label and format appropriately – e.g., use $ to format dollar amounts, format cells
for decimal places, etc…
Submit your Excel spreadsheet to Canvas under Spreadsheet Assignment
Naming your file. The name of your file should follow this format:
lastname_firstname_202_spreadsheet, e.g., upton_david_202_spreadsheet. Also, include
your name in the spreadsheet.
This is to be your OWN work. Sharing files is unacceptable. You may discuss the
project with me, tutors or other students, however, the file submitted must be your
own work. If I consider files have been copied and you are submitting the work of
another student I will assign zero for the assignment. Late work will not be
accepted.
