accounting excel
Directions CTA 4- CVP & Cost Management Systems **Before you begin, save this file to your computer named as LastName, First Name CTA4 This critical thinking analysis includes a comprehensive problem that will cover concepts you learned in chapters 16-20. There is also one problem that continues our problem with Nerdzy Computer Company that is focused on chapter 21. The comprehensive problem that covers chapters 16-20 can be found on the tab named "Comprehensive". All of the data that you will need and all of your answer input areas will be found in that one sheet. The problem that covers cost management systems and continues working with Nerdzy Computer Company can be found in the tab labelled "Req2". All of the data and answer inputs will be found in that one sheet. Special Notes: 1. Only enter answers in blue shaded cells. If you would like to do additional calculations in excel that are not part of the final answer, please add a new worksheet to the workbook. 2. If a cell does not have an answer, leave it blank. If a cell should have a zero, please enter it into the cell. 3. I tried to set the pages up so that they would print nicely on whole sheets of paper. If you would like to, you can print the file and complete it by hand. After, please transcribe your answers to this excel workbook so that it can be submitted. 4. When you are done with the assignment, upload the completed file into the associated Blackboard assignment area. Comprehensive Comprehensive Problem Chapters 16-20Req C1. Complete a production cost report for the Cutting Department and the Sewing Department. What is the cost of one basic shirt? The Tattered Threads Shirt Company makes two types of T-shirts: basic and custom. Basic shirts are plain shirts without any screen printing on them. Custom shirts are created using the basic shirts and then adding a custom screen-printing design. The company buys cloth in various colors and then makes the basic shirts in two departments, Cutting and Sewing. The company uses a process costing system (weighted-average method) to determine the production cost of the basic shirts. In the Cutting Department, direct materials (cloth) are added at the beginning of the process and conversion costs are added evenly through the process. In the Sewing Department, no direct materials are added. The only additional material, thread, is considered an indirect material because it cannot be easily traced to the finished product. Conversion costs are added evenly throughout the process in the Sewing Department. The finished basic shirts are sold to retail stores or are sent to the Custom Design Department for custom screen printing. The Custom Design Department creates custom shirts by adding screen printing to the basic shirt. The department creates a design based on the customer’s request and then prints the design using up to four colors. Because these shirts have the custom printing added, which is unique for each order, the additional cost incurred is determined using job order costing, with each custom order considered a separate job. The Tattered Threads Shirt Company Production Cost Report- Cutting Dept Month Ended March 2021 Equivalent Units UNITSWhole UnitsDirect MaterialsConversion Costs Units to account for: Beg. WIP Started in Production Total Units to Account for Units accounted for: Completed & trsf out End WIP Total units accounted for For March 2021 the Tattered Threads Shirt Company compiled the following data for the Cutting and Sewing Departments: COSTSDirect MaterialsConversion CostsTotal Costs Costs to account for: DepartmentItemAmountUnitsBeg. WIP CuttingBeginning balance- 0- 0shirtsCosts added during period Started in March- 01,411shirtsTotal costs to account for Direct materials added in March1,980÷ total EUP Conversion costs1,380Cost per EU Completed and transferred to Sewing???1,411shirts Ending balance- 0- 0shirtsCosts accounted for: SewingBeginning balance, transferred in, $1458; conversion costs, $6882,146521shirtsCompleted & Trsf out Transferred in from Cutting??????Ending WIP Conversion costs added in March1,257- 0Total costs accounted for Completed and transferred to Finished Goods???1,000shirts Ending balance, 63% complete?????? For the same time period, the Tattered Threads Shirt Company compiled the following data for the Custom Design Department:The Tattered Threads Shirt Company Production Cost Report- Sewing Dept Month Ended March JobQuantityDesign FeePrintingStatus 367400Yes3 colorsCompleteEquivalent Units 368150No2 colorsCompleteUNITSWhole UnitsTransferred inDirect MaterialsConversion Costs 369100Yes5 colorsCompleteUnits to account for: 370500Yes4 colorsCompleteBeg. WIP Transferred in The Tattered Threads Shirt Company has previously determined that creating and programming the design cost Total Units to Account for 84per design. This is a one time charge. Units accounted for: If a customer places another order with the same design, the customer is not charged a second time. Completed & trsf outn/a End WIPn/a Additionally, the cost to print is .25per color per shirt.Total units accounted forn/a RequirementsCOSTSDirect MaterialsConversion CostsTotal Costs C1. Complete a production cost report for the Cutting Department and the Sewing Department. What is the cost of one basic shirt?Costs to account for: Beg. WIPn/a Costs added during periodn/a C2. Determine the total cost and the average cost per shirt for jobs 367, 368, 369, and 370. If the company set the sales price at 225% of the total cost, determine the total sales price of each job.Total costs to account forn/a ÷ total EUPn/a Cost per EUn/a C3. In addition to the custom jobs, the Tattered Threads Shirt Company sold 1250 basic shirts. If the company set the sales price at 140% of the cost, determine the sales price per unit, total sales revenue, total cost of goods sold, and total gross profit for the basic shirts. Costs accounted for: Completed & Trsf outn/a **(assume the beginning balance in Finished Goods Inventory is sufficient to make these sales, and the unit cost of the basic shirts in Finished Goods Inventory is the same as the unit cost incurred this month)Ending WIPn/a Total costs accounted forn/a C4. Calculate the total revenue, total cost of goods sold, and total gross profit for all sales, basic and custom. C5. Assume the company sold only basic shirts (no custom designs) and incurred fixed costs of $790 per month.Cost of one basic shirt = a. Calculate the contribution margin per unit, contribution margin ratio, required sales in units to break even, and required sales in dollars to break even. b. Determine the margin of safety in units and dollars. c. Suppose the Tattered Threads Shirt Company wants to earn an operating income of $1240 per month. Compute the required sales in units and dollars to achieve this profit goal. C6. The Tattered Threads Shirt Company is considering adding a new product line, a cloth shopping bag with custom screen printing that will be sold to grocery stores. If the current market price of cloth shopping bags is $2.12 and the company desires a net profit of 62% what is the target cost? C2. Determine the total cost and the average cost per shirt for jobs 367, 368, 369, and 370. If the company set the sales price at 225% of the total cost, determine the total sales price of each job. The company estimates the full product cost of the cloth bags will be $0.70. Should the company manufacture the cloth bags? Why or why not? Job367368369370TOTALS COSTS: Student Calculations / Notes:Basic shirt Design Cost Printing Cost Total Cost Cost per shirt Sales Price per shirt Total Sales C3. In addition to the custom jobs, the Tattered Threads Shirt Company sold 1250 basic shirts. If the company set the sales price at 140% of the cost, determine the sales price per unit, total sales revenue, total cost of goods sold, and total gross profit for the basic shirts. **(assume the beginning balance in Finished Goods Inventory is sufficient to make these sales, and the unit cost of the basic shirts in Finished Goods Inventory is the same as the unit cost incurred this month) Sales price per unit Total Sales Revenue Total COGS Total GP C4. Calculate the total revenue, total cost of goods sold, and total gross profit for all sales, basic and custom. Custom ShirtsBasic ShirtsTotals Sales Revenue COGS GP C5. Assume the company sold only basic shirts (no custom designs) and incurred fixed costs of $790 per month. a. Calculate the contribution margin per unit, contribution margin ratio, required sales in units to break even, and required sales in dollars to break even. CM per unit=Req. sales in units=shirts CM Ratio=Req sales in $ = b. Determine the margin of safety in units and dollars. Margin of safety in units =shirts Margin of safety in $= c. Suppose the Tattered Threads Shirt Company wants to earn an operating income of $1240 per month. Compute the required sales in units and dollars to achieve this profit goal. Req. sales in units= Req. sales in $ = C6. The Tattered Threads Shirt Company is considering adding a new product line, a cloth shopping bag with custom screen printing that will be sold to grocery stores. If the current market price of cloth shopping bags is $2.12 and the company desires a net profit of 62% what is the target cost? The company estimates the full product cost of the cloth bags will be $0.70. Should the company manufacture the cloth bags? Why or why not? Target Cost Should they produce the bags? Explanation: Req2 Variable vs Absorption Costing2a. Compute the product cost per unit produced under absorption costing and under variable costing.Student Calculations / Notes: Nerdzy Computer company has been operating for a couple of years now and decided to do away with its desktop computer production. It now only sells laptops. Absorption CostingVariable Costing Recent company data reveals that under the current sales mix, the weighted average sales price per unit is 373 and the weighted-average variable cost per unit is 62.Variable Manufacturing cost Fixed MOH Total unit product cost The company does not expect the sales mix to vary for the next year. Assume the beginning balance in finished goods inventory is $0. Additional data for the first month of the year show: 2b. Prepare income statements for January using: 2b1. absorption costing. JANNerdzy Computer Co. Units produced and sold:Income Statement Sales1,025unitsMonth ended January 20XX Production1,200units Variable manufacturing cost per unit62Net Sales Revenue Sales commission cost per unit12Cost of Goods Sold Total fixed manufacturing overhead93,600Gross Profit Total fixed selling and administrative costs62,400Selling & Admin Costs Operating Income Requirements 2a. Compute the product cost per unit produced under absorption costing and under variable costing.2b2. variable costing. 2b. Prepare income statements for January using:Nerdzy Computer Co. 2b1. absorption costing.Income Statement 2b2. variable costing.Month ended January 20XX 2c. Is operating income higher under absorption costing or variable costing in January? What causes the difference? Net Sales Revenue Variable Costs Contribution Margin Fixed Costs Operating Income 2c. Is operating income higher under absorption costing or variable costing in January? What causes the difference?