Open MFG2 and click the Chart sheet tab. The management of Twisp is convinced that the quality of its products is highly dependent on their relative labor costs. Experience has shown that direct labor should account for at least 45% of the total product cost. According to the pie chart that appears on the screen, Twisp did not achieve this goal in 2011. How much should Twisp have spent on direct labor to reach its 45% goal?
To find out, try different values for direct labor (cell C8), clicking the Chart sheet tab after each attempt. When you find a direct labor level that increases the direct labor percentage to 45%, enter the answer in the space provided:
In 2011, direct labor needed to be $________________.
Open MFG3 and click the Chart sheet tab. Did Twisp achieve its 45% goal in 2012? If not, answer the following:
In 2012, direct labor needed to be $________________.
When the assignment is complete, close the files without saving them again.
Worksheet. The MFG2 worksheet presents the companyʼs manufacturing activities for 2011. The company also had the following selling and general activities in 2011: sales of $13,503,000, selling expenses of $2,400,000, and general expenses of $1,200,000. Modify the worksheet to include this information in the Data Section and change the Answer Section so that it is in the form of an income statement. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as MFGT.
Hint: Expand the Data Section to include these additional input items alphabetically. Insert a row for sales under the heading in the Answer Section. Add the rest of the income statement information to the bottom of the schedule. You will need to enter formulas for gross profit, all the expenses, and net income. You will also need to change the statement name.
Chart. Using the MFG2 file, prepare a single 3-D bar chart to show the dollar amount of materials, labor, and overhead incurred by Twisp in 2011 and 2012. Complete the Chart Data Table and use it as a basis for preparing the chart. Enter all appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as MFG2. Print the chart.
Want to see the full answer?
Check out a sample textbook solutionChapter 16 Solutions
Excel Applications for Accounting Principles
- Kimball Company has developed the following cost formulas: Materialusage:Ym=80X;r=0.95Laborusage(direct):Yl=20X;r=0.96Overheadactivity:Yo=350,000+100X;r=0.75Sellingactivity:Ys=50,000+10X;r=0.93 where X=Directlaborhours The company has a policy of producing on demand and keeps very little, if any, finished goods inventory (thus, units produced equals units sold). Each unit uses one direct labor hour for production. The president of Kimball Company has recently implemented a policy that any special orders will be accepted if they cover the costs that the orders cause. This policy was implemented because Kimballs industry is in a recession and the company is producing well below capacity (and expects to continue doing so for the coming year). The president is willing to accept orders that minimally cover their variable costs so that the company can keep its employees and avoid layoffs. Also, any orders above variable costs will increase overall profitability of the company. Required: 1. Compute the total unit variable cost. Suppose that Kimball has an opportunity to accept an order for 20,000 units at 220 per unit. Should Kimball accept the order? (The order would not displace any of Kimballs regular orders.) 2. Explain the significance of the coefficient of correlation measures for the cost formulas. Did these measures have a bearing on your answer in Requirement 1? Should they have a bearing? Why or why not? 3. Suppose that a multiple regression equation is developed for overhead costs: Y = 100,000 + 100X1 + 5,000X2 + 300X3, where X1 = direct labor hours, X2 = number of setups, and X3 = engineering hours. The coefficient of determination for the equation is 0.94. Assume that the order of 20,000 units requires 12 setups and 600 engineering hours. Given this new information, should the company accept the special order referred to in Requirement 1? Is there any other information about cost behavior that you would like to have? Explain.arrow_forwardThe management of Hartman Company is trying to determine the amount of each of two products to produce over the coming planning period. The following information concerns labor availability, labor utilization, and product profitability: a. Develop a linear programming model of the Hartman Company problem. Solve the model to determine the optimal production quantities of products 1 and 2. b. In computing the profit contribution per unit, management does not deduct labor costs because they are considered fixed for the upcoming planning period. However, suppose that overtime can be scheduled in some of the departments. Which departments would you recommend scheduling for overtime? How much would you be willing to pay per hour of overtime in each department? c. Suppose that 10, 6, and 8 hours of overtime may be scheduled in departments A, B, and C, respectively. The cost per hour of overtime is 18 in department A, 22.50 in department B, and 12 in department C. Formulate a linear programming model that can be used to determine the optimal production quantities if overtime is made available. What are the optimal production quantities, and what is the revised total contribution to profit? How much overtime do you recommend using in each department? What is the increase in the total contribution to profit if overtime is used?arrow_forwardA new product is being designed by an engineering team at Golem Security. Several managers and employees from the cost accounting department and the marketing department are also on the team to evaluate the product and determine the cost using a target costing methodology. An analysis of similar products on the market suggests a price of $132.00 per unit. The company requires a profit of 0.20 of selling price. How much is the target cost per unit? Round to two decimal places.arrow_forward
- The controller for Camden's Bike Shop has gathered the following cost and activity level information: Average total cost per unit $225.00 Production level used to calculate average cost 1,000 Total fixed costs $80,000 If the controller uses the cost equation to predict total costs, what total cost would the controller calculate for 2,500 racing bicycles? Is there a difference between the forecasted total cost using average cost versus the cost equation? If there is a difference, what creates the difference? If there is no difference, when would there be a difference?arrow_forwardConley Company has fixed costs of $23,460,000. The unit selling price, variable cost per unit, and contribution margin per unit for the company's two products follow: Product Selling Price Variable Cost per Unit Contribution Margin per Unit Yankee $355 $220 $135 Zoro 255 200 55 The sales mix for products Yankee and Zoro is 75% and 25%, respectively. This information has been collected in the Microsoft Excel Online file. Open the spreadsheet, perform the required analysis, and input your answers in the question below. Open spreadsheet Determine the break-even point in units of Yankee and Zoro of the overall (total) product, E. If required, round your answers to the nearest whole number. Product Yankee: fill in the blank 2 unitsProduct Zoro: fill in the blank 3 unitsarrow_forwardZiegler Inc. has decided to use the high-low method to estimate the total cost and the fixed and variable cost components of the total cost. The data for various levels of production are as follows: Units Produced Total Costs 97,500 $27,165,000 112,500 29,715,000 124,500 31,755,000 This information has been collected in the Microsoft Excel Online file. Open the spreadsheet, perform the required analysis, and input your answers in the questions below. Open spreadsheet Determine the variable cost per unit and the total fixed cost. Round your answer for variable cost to the nearest cent. Round your answer for total fixed costs to the nearest dollar. Variable cost: $fill in the blank 2 per unit Total fixed cost: $fill in the blank 3 Based on part (a), estimate the total cost for 80,000 units of production. Round your answer to the nearest dollar. Total cost for 80,000 units: $fill in the blank 4arrow_forward
- Rockness asked, "Do you see any problems here? Should we drop any of these products? Should we reprice any of these products?" He said, "I am suspicious of these cost data, Rocky. Here we are assigning indirect costs to these products using a 260 percent rate. I really wonder whether that rate is accurate for all products. I want you to dig into the indirect cost data, figure out what drives those costs, and see whether you can give me more accurate cost numbers for these products." Rocky first learned from production that the process required four activities: (1) setting up production runs, (2) managing production runs, and (3) managing products. The fourth activity did not require labor; it was simply the operation of machinery. Next, he went to the accounting records to get a breakdown of indirect costs. Here is what he found: ---------------------------------------------------IMAGE 2----------------------------------------------------------------- Then, he began a series of…arrow_forwardListed below are a company’s monthly unit costs to manufacture and market a particular product.Unit Costs Variable Cost Fixed CostsDirect materials P2.00 Direct labor 2.40 Indirect Manufacturing 1.60 P1.00Marketing 2.50 1.50The company must decide to continue making the product or buy it from an outside supplier. The supplier has offered to make the product at the same level of quality that the company can make it. Fixed marketing costs would be unaffected, but variable marketing costs would be reduced by 30% if the company were to accept the proposal. What is the maximum amount per unit that the company can pay the supplier without decreasing its operating income? • P5.25 • P7.75 • P6.75 • P8.50 • None of the abovearrow_forwardRR Company uses the high-low method to analyze cost behavior. The company observed that at 2,000 machine hours of activity, total maintenance costs averaged ₱40.00 per hour. When activity jumped to 6,000 machine hours, the average cost per machine hour totaled ₱35.00. If the number of machine hours would exceed 5,000, fixed cost would increase by ₱20,000. On the other hand, if it will be below 1,000 machine hours, fixed cost would decrease by ₱10,000. But, regardless of the number of machine hours, variable cost per unit remains constant. 1. If RR plans to incur 4,500 machine hours, the total cost would be _______. 2. If RR plans to incur 5,300 machine hours, the total cost would be _______.arrow_forward
- DK manufactures three products, W, X and Y. Each product uses the same materials and the same type of direct labour but in different quantities. The company currently uses a cost plus basis to determine the selling price of its products. This is based on full cost using an overhead absorption rate per direct labour hour. However, the managing director is concerned that the company may be losing sales because of its approach to setting prices. He thinks that a marginal costing approach may be more appropriate, particularly since the workforce is guaranteed a minimum weekly wage and has a three month notice period. Required: a) Given the managing director’s concern about DK’s approach to setting selling prices, discuss the advantages and disadvantages of marginal cost plus pricing AND total cost-plus pricing.arrow_forwardDK manufactures three products, W, X and Y. Each product uses the same materials and the same type of direct labour but in different quantities. The company currently uses a cost plus basis to determine the selling price of its products. This is based on full cost using an overhead absorption rate per direct labour hour. However, the managing director is concerned that the company may be losing sales because of its approach to setting prices. He thinks that a marginal costing approach may be more appropriate, particularly since the workforce is guaranteed a minimum weekly wage and has a three month notice period.arrow_forwardDK manufactures three products, W, X and Y. Each product uses the same materials and the same type of direct labour but in different quantities. The company currently uses a cost plus basis to determine the selling price of its products. This is based on full cost using an overhead absorption rate per direct labour hour. However, the managing director is concerned that the company may be losing sales because of its approach to setting prices.He thinks that a marginal costing approach may be more appropriate, particularly since the workforce is guaranteed a minimum weekly wage and has a three month notice period. Required:a) Given the managing director’s concern about DK’s approach to setting selling prices, discuss the advantages and disadvantages of marginal cost plus pricing AND total cost-plus pricing. The direct costs of the three products are shown below: Product W X Y Budgeted annual production (units) 15,000 24,000 20,000 $ per unit $ per unit $ per unit Direct…arrow_forward
- Excel Applications for Accounting PrinciplesAccountingISBN:9781111581565Author:Gaylord N. SmithPublisher:Cengage LearningEssentials of Business Analytics (MindTap Course ...StatisticsISBN:9781305627734Author:Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. AndersonPublisher:Cengage LearningCornerstones of Cost Management (Cornerstones Ser...AccountingISBN:9781305970663Author:Don R. Hansen, Maryanne M. MowenPublisher:Cengage Learning