Please read the instructions and complete the project as required, USING EXCEL. Everything needed should be included.

INSTRUCTIONS:

Req. 16: Obtain the Labor Time Records for the week to determine how much direct labor cost to assign to Job 114. Record the direct labor used by the job by recording the labor time record numbers, hours worked, and total DL cost on the job cost record. See Excel file: Labor Time Records See Excel file: Job Cost Record Req. 17: Calculate the amount of manufacturing overhead to allocate to Job 114. Show your calculations on the job cost record. See Excel file: Job Cost Record Req. 18: Complete the job cost record. Determine the total job cost as well as the cost of each handbag in the job. See Excel file: Job Cost Record Req. 19: The handbags have been moved out of the factory and into the finished goods warehouse to await shipment. Before the handbags are completed, the job cost records served as a basis for valuing which inventory account on the balance sheet? . After the handbags are completed, the job cost records serve as a basis for valuing which inventory account? Req. 20: The handbags ordered by Katie's Boutique are shipped out on 10/14. Update the job cost record. What is the new balance in Finished Goods Inventory for this item? See Excel file: Job Cost Record Req. 21: Calculate the gross profit on the sale to Katie's Boutique:A1 T X V fx Custom Fabric Ventures A B C D E F G H Custom Fabric Ventures W N H Customer Order Form Customer: Katie's Boutique Order Date: 10/2/2012 5 Order Due Date: 10/15/2012 6 Description: Model Number: Quantity Unit Sales Price Total 7 Black & White Handbag BW14923 7 $75 $525 8 10 RUBRIC Customer Order Form Estimated Annual Costs Bill of Materials Production Schedule Raw Ma ReadyA B C Type of Cost Estimated total cost for the year 2 Selling and Administrative salaries and expenses S 210,000 3 Direct Labor (Estimated 40,000 DL hours @ average wage rate of $12 per hour) A Indirect plant labor 40,000 5 Plant utilities 50,000 6 Purchases of direct materials 1,400,000 7 Lease and property taxes on the plant S 120,000 8 Marketing expenses 30,000 Depreciation on production equipment (equipment originally costing $700,000 three year ago is being depreciated over a 5 year useful life using straight-line depreciation. No salvage value is 9 expected.) 10 Indirect materials to be used in the plant 20,000 11 12 RUBRIC Customer Order Form Estimated Annual Costs Bill of MaterialsReq. 10: Assume shipments of all supplies arrive on 10/9. Your receiving personnel have checked the receipts against the Purchase Order and Invoice, and have noted no discrepancies in the ordered quantities or prices. Since the company keeps perpetual inventory records, update the Raw Materials Records for the materials received and the new balance in stock. Update both the physical quantities and costs. The company uses FIFO as its inventory flow assumption. If needed, keep track of separate inventory price layers. See Excel file: Raw Materials Records Req. 11: Assume for a moment that the Raw Materials Records given to you represent a complete list of the raw materials carried by the company. If you were to look at the Balance Sheet at the close of business on Oct. 9, what balance would be shown for Raw Materials Inventory? (Show the total balance and how you arrived at it.) $. Req. 12: The scheduled start date for the job has arrived. In order to begin production the materials for the job are needed in the plant. Production would like to requisition off needed materials at one time. Complete the Materials Requisition (fill in the date, Job #, and quantity needed) so that the inventory storeroom personnel know the quantities and types of material to pick. See Excel file: Materials Requisition Req. 13: The storeroom personnel correctly pick the direct materials and send them to production. Complete the cost information on the Materials Requisition above, assuming the company uses FIFO as its inventory flow assumption. Req. 14: Update the Raw materials records for the materials that were picked In Req. 13. If you were to look at the Balance Sheet at the end of the day, what balance would be shown for Raw Materials Inventory? Calculate the total balance and show how you arrived at it. $ Req. 15: Start a Job Cost Record for Job 114. Record the direct materials used by the job by recording the materials requisition number and the total cost of materials requisitioned. See Excel file: Job Cost RecordReq. 22: Assume for a moment that Katie's Boutique has found an overseas manufacturer that would charge $45 per unit for the same handbag. Should Custom Fabric Ventures match the price of this competitor? Briefly explain your answer. Req. 23: Would the cost of Job 114, as reported on the job cost record, have been different had the company used LIFO or Weighted Average, rather than FIFO, as its inventory flow assumption? If so, would it have been higher or lower than currently reported? Explain. Req. 24: Would the cost of Job 114, as reported on the job cost record, have been different had the company used "direct labor cost" as the MOH allocation base, rather than "direct labor hours"?_ If so, by how much would the total job cost have differed? Req. 25: Would the cost of Job 114, as reported on the job cost record, have been different had the company's production equipment been 6 years old, rather than 3 years old? If so, by how much would the total job cost have differed? Req. 26: The budgeted data for the year did not include employment benefits, such as paid time off, company-paid health insurance, or company-sponsored retirement plans. From an accounting standpoint, how should these costs be treated if they relate to selling and administrative personnel? How should they be treated if they relate to plant personnel? _Through what two mechanisms might these costs be assigned to jobs if they relate to employees working in the plant? 1. 2 Req. 27: As a manager, you always need to be thinking about how to decrease costs without sacrificing the quality of your products and services. Generate three to five specific Ideas this company could possibly implement for decreasing costs on similar jobs in the future. Note: General answers such as "spend less on labor" or "be more efficient" will not count. You must generate specific, realistic and actionable recommendations managers could take to possibly decrease production costs related to similar jobs in the future.A1 X V fx Raw Materials Record A B C D E F G H J K Raw Materials Record IN Item No. BWFF Description: Black and White Floral Fabric Received Used Balance Date Units Cost Total Requisition Units Cost Total Units Cost Total (yards] Number (yards) (yards] 1-Oct $6.00 $ 36.00 10 12 13 14 Item No. BWCheck 15 16 Date Total 18 1-Oct $160.00 19 3-Oct $64.00 20 22 23 24 25 26 27 28 Raw Materials Record 29 Item No. BFringe Description: Black Fringe Trim 30 Received Used Balance 31 Date Inits Cost Total Requisition Units Cost Total Unit Cost Total 32 [yards] Number (yards] (yards 33 1-Oct 3 $3.00 $9.00 34 2-Oct 60 $3.00 $180.00 63 $3.00 $189.00 35 36 37 38 39 40 Raw Materials Record 42 Item No. BStrap Description: Black Shoulder Strapping Received Used Balance Date Inits Cost Total Requisition Unit Cost Total Unit Cost Total [yards] Number (yards) yards 46 1-Oct $0.00 47 48 49 51 52 53 Raw Materials Record 4 Item No. PICan Description: Plastic Canvas Sheets 55 Received Used Balance 56 Date Units Cost Total Requisition Units Cost Total Units Cost Total 57 [sheets) Number (sheets] (sheets) 58 1-Oct 25 $0.90 $22.50 59 60 Raw Materials Record Item No, Into Description: Interfacing- leveld Received Used Balance Date Units Cost Total Requisition Units Cost Total Units Cost Total [yards] Number (yards] yards 70 1-Oct 50 $4.00 $200.00 71 4-Oct #6785 48 $4.00 $192.00 $4.00 $8.00 72 73 74 75Req. 28: Record the following journal entries associated with this case: 1. Record the purchases of raw materials. 2. Record the use of direct materials on Job 114. 3. Record the use of direct labor on Job 114. 4. Record the allocation of manufacturing overhead to Job 114. 5. Record the completion of Job 114 and its movement out of the factory. 6. Record the sale of 7 units from Job 114 to Katie's Boutique. Use a perpetual system. 7. Record indirect labor which totaled $3,500 for the month. 8. Record the use of indirect materials which totaled $1,500 for the month. 9. Assume the total amount of MOH incurred during the month was $25,450 and the total amount allocated to jobs during the month was $28,934. Was manufacturing overhead over or under allocated? Record the journal entry to close the manufacturing overhead (MOH) account assuming the company closes MOH to Cost of Goods Sold (CGS).A1 X V fx JoAnne Fabrics- Commercial Division A B C JoAnne Fabrics- Commercial Division 2 Part Number Name Price per yard 3 GrFlor Green Floral $6.50 4 FF620 Funky Floral $3.25 5 J293 Jacobean $12.00 6 DB524 Denim Block $8.50 7 RD590 Razzle Dazzle $3.50 8 Atone Aqua Tone $2.75 9 BWCheck Black & White Check $7.00 10 GrSwirl Green Swirel $9.50 11 PICan Plastic Canvas $1.00 12 Int4 Interfacing- level 4 $4.00 13 Int 5 Interfacing -level 5 $4.50 14 RRHPink Rik Rak- Hot Pink $2.75 15 BBead Black Beads $5.75 16 BFringe Black Fringe $3.00 17 OGStrap Olive Green Strapping $2.50 18 BStrap Black Shoulder Strapping $2.00 19 20 21 Hancack Fabrics- Commercial Division 22 Part Number Name Price per yard 23 CPlaid Country Plaid $7.50 24 BWCheck Black & White Check $9.00 25 FF620 Funky Floral $3.50 DB524 Denim Block $8.75 27 BWFF Black & White Floral $6.00 28 RD590 Razzle Dazzle $4.25 29 Atone Aqua Tone $3.25 30 Contsph Contemporary Spheres $4.75 31 W5269 Wavy Stripe $5.50 32 PlCan Plastic Canvas $1.00 33 Int4 Interfacing-level 4 $5.00 34 Int 5 Interfacing -level 5 $4.75 35 RRHPink Rik Rak- Hot Pink $3.00 36 BBead Black Beads $6.00 37 STB Scalloped Trim $5.50 38 OGStrap Olive Green Strapping $2.75 39 40Req. 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 you'll 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. See Excel file: Suppliers See Excel file: Purchase Order #1 See Excel file: Purchase Order #2 Briefly explain why you chose to purchase certain materials from each supplier:A1 X V fx Purchase Order A B C D E F G H Purchase Order WN H Supplier: Date ordered: 10/3 Date needed by: Item Number Quantity Cost per yard Total Cost LD 90 10 TOTAL 11 12 13 Estimated Annual Costs Bill of Materials Production Schedule Raw Materials Records Suppliers Purchase Order- #1Req. 2: To realize maximum efficiency and meet management's 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 ItemsA B C D E G Production Schedule For the month of October 3 Job Model Number Stock or Customer Quantity Scheduled Scheduled A Start Date End date 5 111 Headbands - HB246 Perreth 200 10/1 10/3 112 14 inch pillows- P14 Slothower 30 10/4 10/5 7 113 Draperies-GB159 Stock 80 10/6 10/9 8 114 Black & White BW14923 Katie's & Stock 9 10 11 12 13 14 15 16 RUBRIC Customer Order Form Estimated Annual Costs Bill of MaterialsB C D E G H Labor Time Record Employee: Tyler David Week: 10/8-10/15 w Hourly wage rate: $ 10.00 4 Record #: 324 Date Job number Start Time End Time Hours Cos 8-Oct 113 3:00 4:00 -OC $80 113 12:0 5:00 10-Oct $50 114 8:00 4:00 1-Oct $80 114 8:00 4:00 12-Oct $80 115 9:001 1:00 10 $40 11 12 13 Labor Time Record 14 Employee: Eli Kunde Week: 10/8-10/15 15 Hourly wage rate: $ 15.00 Record #: 325 16 Date Job numbe Start Time End Time Hours Cost 17 8-Oct 112 8:00 11:00 18 9-Oct $45 113 10:00 2:00 19 $60 10-Oct 114 8:00 4:00 20 11-Oct $120 114 8:00 21 2:00 $90 12-Oct 115 9:00 4:00 22 $105 23 24 25 Labor Time Record 26 Employee: Stephanie Berg 27 Hourly wage rate: Week: 10/8-10/15 $ 12.00 28 Date Record #: 326 Job number Start Time End Time Hours 29 Cost 8-Oct 112 8:00 2:00 30 9-Oct $72 113 12:0 5:00 31 10-Oct $60 114 3:00 1:00 32 11-Oc $60 114 3:00 1:00 33 12-Oct $96 115/ 9:00 1:00 34 $48 35 36 37 Labor Time Record 38 Employee: Pam Braun Week: 10/8-10/15 39 Hourly wage rate: $ 18.00 40 Date Record #: 327 Job number Start Time End Time Hours 41 Cost 8-Oct 112 3:00 11:00 42 9-Oct 113] 9:00 3:00 10-Oct $108 114 8:00 4:00 11-Oct $144 114 3:00 12:00 45 12-Oct 115 $72 9:00 46 1:00 $72 Purchase Order- #1 Purchase Order- #2 Materials Requisition Job Cost Record Labor Time RecordsA B C D E F G H Materials Requisition W N H Number: #6786 Date: Job: Part number Description Quantity Needed Unit Cost Total Cost 6 BWFF Black and White Floral Fabric BWCheck Black and White Checkered Fabric 9 BFringe Black Fringe Trim 10 BStrap Black Shoulder Strapping 11 PICan Plastic Canvas 12 Int4 Interfacing: Level 4 rating 13 Total 14 15 16 17 Raw Materials Records Suppliers Purchase Order- #1 Purchase Order- #2 Materials RequisitionA B C D E F BILL OF MATERIALS JOB 114 W N Model: BW14923 A Number of units: Part Number Description Quantity needed per unit Total Quantity 5 Needed 6 BWFF Black and White Floral Fabric 0.50 yards 7 BWCheck Black and White Checkered Fabric 0.75 yards BFringe Black Fringe Trim 1.00 yards 9 BStrap Black shoulder strapping 1.75 yards 10 PICan Plastic Canvas 0.25 sheets 11 Int4 Interfacing: Level 4 sturdiness rating 0.50 yards 12 13 14 RUBRIC Customer Order Form Estimated Annual Costs Bill of Materials ProA1 * X V fx Purchase Order B C D E F G H J K L M Purchase Order INH Supplier: Date ordered: 10/3 Date needed by: Item Number Quantity Cost per yard Total Cost 10 TOTAL $ 11 12 13 Estimated Annual Costs Bill of Materials Production Schedule Raw Materials Records Suppliers Purchase Order- #1 Purchase Order- #2Custom 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 company's customers are small boutiques, home decor 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 Katie's order, management would like to produce a job (Job 114) that is large enough to fill Katie's 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 offer Katie's 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 Katie's Boutique. As part of this process, you will be required to schedule production, purchase any raw materials that aren't 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 company's 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 company's predetermined manufacturing overhead rate, assuming the company uses direct labor hours as its allocation base:B E G A C D F Job Cost Record W NH Job Number: Customer or Stock: Job description: Date Started: Date Completed: Quantity: Manufacturing Cost Information: Cost Summary Direct Materials (list Materials Requisition # and cost) S 9 10 11 12 Direct Labor (list labor time record #, hours, and cost) S 13 14 15 16 17 18 Manufacturing Overhead (show calculation of amount allocated) 19 20 21 22 Total Job Cost 23 Number of units 24 Cost per Unit 25 26 Shipping Information: 27 Date Quantity Units remaining Cost Balance in 28 shipped Inventory 29 30 31 Suppliers Purchase Order- #1 Purchase Order- #2 Materials Requisition Job Cost Record