ACCOUNTING-The BookCases Company manufactures hand-crafted natural wood book cases

QUESTION

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 forBookCases.

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,752feet 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.

Please email me if you have
any questions.

ANSWER:

REQUEST HELP FROM A TUTOR