JOB COSTING EXCEL PROJECT

Custom Fabric Ventures is a small company that produces fabric-based wardrobe accessories (such as, handbags, scarves, and headbands) and home accessories (such as placemats, pillows, and window treatments). The company keeps a limited number of popular items in stock, but primarily produces custom orders. Customers are able to choose from a wide selection of styles, sizes, and fabrics for each type of product. Most of the companys customers are small boutiques, home dcor shops, and home-decorators who serve clientele with discerning tastes. As a result, customers expect high-quality products.

The sales staff of Custom Fabric Ventures has just received the following order:

See Excel file: Customer Order Form

Due to its popularity, the Black and White Handbag (BW14293) is normally a stock item. However, the handbag is currently out of stock. Rather than producing only enough units to fill Katies order, management would like to produce a job (Job 114) that is large enough to fill Katies order and replenish stock. The fabric used for this job can be stacked four layers deep for cutting, while still maintaining the quality and precision of the cut. Therefore, the company is able to realize some economies of scale by producing the handbags in multiples of 4. Management would like to produce enough units in this job to have a minimum of 30 and a maximum of 35 handbags left in stock after Katies order has been filled.

Your primary task is to determine the cost of producing Job 114 so that you can calculate the gross profit on the sale to Katies Boutique. As part of this process, you will be required to schedule production, purchase any raw materials that arent currently in stock, requisition materials for the job, and complete the job cost record. You will also be asked to consider relevant management and accounting issues and record the transactions related to this job in the companys accounting records.

The following step-by-step requirements will guide you through the entire job costing process. The documents you will encounter in this case are normally completed electronically rather than manually. In addition, these documents often interface with each other electronically in order to transfer data in real-time, eliminate tedious calculations, and prevent errors. However, the underlying process is the same regardless of whether the company uses electronic or manual documents.

Req. 1: Before the year began, Custom Fabric Ventures estimated the following costs for the year:

See Excel file: Estimated Annual Costs

First, fill in the missing costs. Then, calculate the companys predetermined manufacturing overhead rate, assuming the company uses direct labor hours as its allocation base:

Req. 2: To realize maximum efficiency and meet managements desired level of stock, how many Black & White handbags should be produced in Job 114?___________

Req. 3: The production engineers have given you the materials requirements for each Black & White handbag. Assume that no efficiencies in materials can be achieved by producing more than one unit per batch. Complete the Bill of Materials for Job #114 found below. Be sure to include number of units being produced in the Job as well as the total quantity needed of each direct material.

See Excel file: Bill of Materials

Req. 4: The production engineers have also given you the production labor requirements for each individual bag produced, assuming one bag is produced at a time:

Cutting time: 30 minutes per bag;

Sewing time: 70 minutes per bag;

Ironing time: 8 minutes per bag

Since each bag must be sewn and ironed individually, no sewing and ironing labor efficiencies can be achieved by producing larger batch sizes. Recall, however, that fabric can be stacked for cutting purposes, such that the material for four bags can be cut in the same amount of time it would take to cut one individual bag. Compute the estimated labor time requirement for producing Job 114.

TOTAL hours required for Job 114: _________

Req. 5: Use the estimated labor time requirement to schedule production of Job 114, including the start and end dates. Assume the plant will already be operating at full capacity every day while completing Jobs 111, 112, and 113. Also assume that only 4 direct laborers with the needed skill set will be available for this job. These hourly employees are only allowed to work a maximum of 8 hours per day so that the company can avoid paying overtime premiums.

See Excel file: Production Schedule

Req.6: In addition to the direct materials shown on the Bill of Materials, what indirect materials might be used in the factory while Job 114 is being produced? List at least 2 items

Req. 7: What types of production equipment will be needed for producing Job #114? List at least 5 items.

Req. 8: Consult the Raw Materials records to determine the types and quantities of materials you currently have in stock. Determine which types of materials you need to purchase for Job 114 and how much of each youll need.

See Excel file: Raw Materials Records

List the materials that need to be purchased, along with the quantity of each needed:

Req. 9: Complete the purchase orders (supplier, date needed, item number, quantity, cost per yard, total cost, total for the whole order) assuming you are only buying what you need for this job, and no other jobs. Your purchasing agent will need to check with your authorized suppliers (JoAnne Fabrics and Hancack Fabrics) to determine the current sales price of the materials. No shipping is charged on purchases orders over $50. Note: Both suppliers only allow you to order in increments of 5 yards at time, so you may have to buy a little more than what you need. This is not a problem for your company, as it can always use large pieces of leftover material for other jobs. Custom Fabric Ventures also has a standing contract with Mattel to produce clothing for the Barbie and Ken line of dolls. Thus, small fabric remnants can always be used for the toy clothing line.