Accounting case

Excel

ACCT 331
Case 2H

Cash 47,700$
Accounts Receivable 462,000
DM Inventory 33,42

5

FG Inventory 170,020
Land 8,000
Buildings and Equipment 337,000
Accumulated Depreciation (29,200)
Total Assets 1,028,945$

Accounts Payable 150,000$
Capital Stock 17,500
Retained Earnings 861,445
Total Liabilities and
Stockholders Equity 1,028,945$

You have gathered the following information about expected activity in 2022:

1. The marketing department projects the following sales levels for each product in the third quarter of 2022:
Outdoor Chairs Office Chairs Kitchen Chairs

July 2022 720 860 600
August 2022 840 760 520
September 2022 1,060 940 700

The Outdoor Chairs are expected to sell for $220 each, the Office Chairs are expected to sell for $530 each, and the Kitchen Chairs are expected
to sell for $710 each.

2. For each product, Shonji plans to have ending finished goods inventory (in units) for each month that equals 20% of the next month’s
sales (in units). Finished Goods inventory on 6/30/2022 consists of 88 Outdoor Chairs at a cost of $160 per unit, 92 Office Chairs at a cost of
$505 per unit, and 184 Kitchen Chairs at a cost of $595 per unit. Shonji uses FIFO to account for its finished goods inventory.

3. Shonji uses plastic, leather, and wood to make its chairs. Each Outdoor Chair requires 25 pounds of plastic. Each Office Chair requires
15 pounds of plastic and 21 square feet of leather. Each Kitchen Chair requires 9 square feet of leather and 42 board feet of wood. The company
expects to pay $2.25 per pound of plastic, $10.95 per square foot of leather, and $8.00 per board foot of wood.

4. Direct Materials (DM) inventory on 6/30/2022 consists of 1,960 pounds of plastic at a cost of $1.75 per pound, 1,380 square feet of leather at a
cost of $15.00 per square foot, and 1,430 board feet of wood at a cost of $6.50 per board foot. The target ending DM inventory (in units) for each material
is budgeted to be 10% of the amount of material required to be used in production that month (e.g., the total pounds of plastic required to be used in
production in a given month x 10% = the target ending inventory for plastic in that month). Shonji uses FIFO to account for its DM inventory.

5. Each Outdoor Chair requires 3 hours of direct labor, each Office Chair requires 5 hours of direct labor, and each Kitchen Chair requires 5.5 hours
of direct labor. The direct labor rate is expected to be $13 per hour.

6. Shonji uses activity-based costing, and has four cost pools for its indirect manufacturing costs. The cost pool data is as follows:
Cost Pool Cost Driver

Setup Setup hours 455.00$ per setup-hour
Machining Machine hours 12.00$ per machine hour
Assembly Direct labor hours 7.00$ per direct labor hour
Packaging Number of chairs 5.00$ per chair

All of the products are made in batches of 40 units. Each batch of Outdoor Chairs requires 1.5 setup hours. Each batch of Office Chairs requires 3 setup
hours. Each batch of Kitchen Chairs requires 2 setup hours. Each Outdoor Chair requires 4 machine hours, each Office Chair requires 6 machine hours,
and each Kitchen Chair requires 7 machine hours.

7. Variable selling and administrative costs are $7.50 per unit sold. Fixed selling and administrative costs are $16,900 per month.

TURN OVER FOR IMPORTANT INSTRUCTIONS!

Shonji Inc. produces three different types of chairs: an outdoor chair, an office chair, and a kitchen chair. The outdoor chairs are made entirely of
plastic. The office chairs have a plastic base and are covered in leather. The kitchen chairs are made out of wood with a leather seat. As the
controller, you are expected to prepare Shonji’s operating budget for July of 2022. Here is Shonji’s balance sheet as of 6/30/2022:

Rate

You are required to do the following:
Prepare a complete operating budget for Shonji Inc. for the month of July 2022. This should include all of the following schedules:
– revenues budget
– production budget (in units)
– direct materials usage budget (in units and dollars)
– direct materials purchases budget (in units and dollars)
– direct labor cost budget
– manufacturing overhead cost budget
– budget of the ending finished goods inventory unit cost
– ending inventories budget
– cost of goods sold budget
– nonmanufacturing costs budget
– budgeted income statement for the month

Excel guidelines:
– The operating budget must be prepared using Excel.
– All of your schedules should be in one Excel file; however, I expect that each schedule will be in its own worksheet.
– Each schedule should have a title that includes the company’s name, the name of the budget, and the time period it pertains to.
– I expect you to use formulas where appropriate and not to type in any calculated amounts. Only the given information should be
manually entered into Excel.
– All of the math required for the budget (e.g., addition, subtraction, multiplication and division) should be expressed using formulas
that contain cell references, not amounts or numbers you have typed into the formula bar.
– Your worksheets should be linked to each other using formulas (when appropriate).
– I expect that all of the assumptions that your budget is based on will be in one worksheet with appropriate labels. The goal is to be
able to change any of the assumptions and have the entire operating budget update automatically. I will be checking for this so
please make sure this works in your file before submitting it.

Due Date: Tuesday, April 12th
Submit: Please upload a Microsoft Excel file (see Excel guidelines above) electronically on Blackboard through

Safe Assign before class starts on the due date. The Case 2 assignment is in the Cases area on Blackboard.

Note: If you do not submit your work for the case using the required file format, you will receive a 10-point reduction
in your grade. Late assignments will be reduced by 10 points for each day they are late. Assignments more
than two days late will not be accepted. This is an individual assignment. When preparing your work, please
pay attention to the university’s policy on plagiarism, which is also referenced in the course syllabus.

Grading criteria Points
earned

Points
possible

Is each part of the operating budget listed below:
(1) included?,
(2) constructed according to the given Excel guidelines?,
(3) put together correctly?, and
(4) showing the right result using the given data?

Revenues budget 7
Production budget (in units) 7
DM usage budget (in units and dollars) 7
DM purchases budget (in units and dollars) 7
DL cost budget 7
MOH cost budget 7
Budget of the ending FG inventory unit product cost 7
Ending inventories budget 7
COGS budget 7
Nonmanufacturing costs budget (for the month) 7
Budgeted income statement 7

Are all the assumptions the operating budget is based on
entered into one separate worksheet with clear labels?

5

If any/all of the assumptions are changed, does the operating
budget successfully generate new results?

9

Overall presentation of the operating budget  please ensure
your file is organized, all data is clearly labeled, and all numbers
are properly formatted

9

Less: any deductions
Total Case 2 grade: 100