ACCT 210 Excel CVP and Incremental Analysis Case Study - F23 (AutoRecovered)

xlsx

School

University Of Arizona *

*We aren’t endorsed by this school

Course

210

Subject

Accounting

Date

Feb 20, 2024

Type

xlsx

Pages

10

Uploaded by DukePowerOtter7

Report
ACCT 210 Cost-Volume-Profit & Incremental Analysis Case Study 10 Total Points Name Maddy Raker Name Learning Objectives: • Apply Cost-Volume-Profit and Incremental Analysis to a business case. • Practice basic Excel skills such as cell referencing and formula generation. Instructions: Grading Rubric: 8 of the 10 possible points will be awarded through the "Excel Case Study Submission Quiz" in D2L. 2 of the 10 possible points will be awarded based on your use of formulas and cell references in this w Read the case study details on the "Case Study" tab and complete the questions on the "Part 1" an tabs. When preparing the analysis on these two tabs, please use formulas and cell references as muc possible (rather than typing numbers into the cells). After you have completed the "Part 1" and "Part will complete the "Excel Case Study Submission Quiz" in D2L to record your answers. After submit D2L quiz, please upload this completed Excel workbook to the assignment dropbox folder on D2L. On from your team needs to submit the workbook. Please make sure that both of your names are of the Excel workbook. 2 pts = at least 10 cell references and formulas used throughout the workbook. 1 pt = fewer than 10 cell references and formulas used. 0 pts = no formulas or cell references used.
y workbook. nd "Part 2" ch as t 2" tabs, you tting your nly 1 person on this page
Case Study Details Child’s Play Company Sales Price (Current) $ 15.00 per rattle Units Sold (Current) Sales Price (Proposed) $ 14.00 per rattle Units Sold (Proposed) 2023 Cost Data and Estimated 2024 Cost Data Manufacturing Costs for rattles (based on production volume of 100,000 units): Per Unit Costs Total Costs Expected Increase Direct Materials $ 1.60 Direct Labor $ 1.80 Packaging $ 0.25 Variable Manufacturing Overhead $ 1.20 Fixed Manufacturing Overhead $ 360,000 $ 20,000 per machi Selling and Administrative Costs for rattles (based on sales volume of 100,000 units): Per Unit Costs Total Costs Expected Increase Sales Commissions $ 1.50 Shipping Costs $ 0.75 Advertising and Promotion (fixed) $ 90,000 $ 2,000 total Fixed Selling and Admin Expenses $ 150,000 Child’s Play Company makes a plastic rattle for toddlers. The rattle is generally marketed through exc in upscale shopping malls. In late 2023, Diana Suarez, the president of the company, was considering marketing plan for 2024 that was presented to her by Bill Duffy, the marketing manager. Based on sa through October 2023, Diana expected that 2024 sales would amount to 100,000 units. Bill’s alternati presented below: 2024 Marketing Plan: “At the present time, we sell the product to retailers for $15.00 per rattle. Reta the consumers between $16 and $16.50. If we cut our selling price to retailers to $14.00, I expect that much better. The retailers' increased markup will give them the incentive to display our product more promote it more vigorously to customers. We should support this strategy by supplying more promotio retailers, which I expect would be an increase of $2,000 in Advertising and Promotion costs. Based on increase in advertising and promotion, I expect that we will be able to boost our sales volume by 20 p in 2024.” Diana received cost data from the company’s CFO, Don Capp. Don expects that the cost data below estimates for 2024 for a production volume up to 150,000 units. Beyond 150,000 units, the company w additional machines (with a capacity of 50,000 units each), which would increase fixed manufacturing $20,000 per machine.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
100,000 120,000 ine clusive retailers located g an alternative ales from January ive marketing plan is ailers generally charge t the product will do prominently and to onal materials to n the price cut and the percent to 120,000 units are also reliable would have to rent g overhead costs by
Part 1 Child's Play Company CVP Income Statement For the Year Ended December 31, 2023 Total Per Unit Sales $ 1,500,000 $ Variable Costs $ 710,000 $ Contribution Margin $ 790,000 $ Fixed Costs $ 600,000 Net Income $ 190,000 Current Unit Cm $ 7.90 Current BEP/Units $ 600,000 = 75949 units $ 7.90 Child's Play Company CVP Income Statement For the Year Ended December 31, 2024 Total Per Unit Sales $ 1,680,000 $ Variable Costs $ 852,000 $ Contribution Margin $ 828,000 $ Using the information on the "Case Study" tab, answer the following questions. Include all costs ( administrative costs) in your calculations. 1.     Prepare a CVP Income Statement for 2023 using the current production and sales volume data, assuming no changes to selling price or costs. 2.     Determine the number of rattles the company would need to sell in 2023 in order to break-e price or costs . Please show your work and round to the nearest next whole unit. 3.     Assuming the selling price and cost changes in the Marketing Plan are adopted, prepare a assuming sales and production increase by 20% as outlined in the Marketing Plan.
Fixed Costs $ 602,000 New Net Income $ 226,000 Proposed Cm/Unit $ 6.90 New BEP/unit $ 602,000 = 87246 $ 6.90 $ 602,000 + 300,000 = $ 902,000 $ 6.90 = $ 130,725 units sold to ea 4.     Assuming the selling price and cost changes in the Marketing Plan are adopted, determin would need to sell in 2024 in order to break-even . Please show your work and round to the ne 5.     Assuming the selling price and cost changes in the Marketing Plan are adopted, determin would need to sell in 2024 in order to earn $300,000 in profit . Please show your work and rou
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
15.00 7.10 7.90 14.00 7.10 6.90 (manufacturing costs and selling and (100,000 rattles) and the current cost even, assuming no changes to selling a CVP Income Statement for 2024 ,
arn $300,000 profit ne the number of rattles the company earest next whole unit. ne the number of rattles the company und to the nearest next whole unit.
Part 2 # of Rattles Needed by Gov. 75,000 Sales Price Paid by Gov. $ 9.00 per rattle Increase in fixed costs from additional machine $ 20,000 per machine Sales 675000 FC $ 620,000 20000 20000 Profit $ 55,000 New NI $ 245,000 55000 55000 Old NI $ 190,000 Increase in NI $ 55,000 Accept! Child’s Play has been approached by the government, which is seeking to buy 75,000 rattles for i proposed government contract states that the government would pay Child’s Play a price of $9.00 decides to accept this special order, they would avoid packaging costs for this contract as and administrative costs. The company’s capacity is limited to only 150,000 units. If they accep will need to increase their capacity by renting an additional machine. Refer to the "Instructions" ta cost data and additional machine rental cost. Assume that Child’s Play does not adopt the proposed Marketing Plan and that the company’s p without the government contract is expected to be 100,000 rattles for 2024. 1.     What is the increase or (decrease) in Net Income that Child’s Play would recognize if they a 2.   Based on your above analysis, should Child’s Play accept or reject the government contrac
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
its day care centers in 2024. The 0 per rattle. If Child’s Play s well as all variable selling pt the government contract, they ab for the company’s estimated production and sales level accept this special order? ct?