P20-47 , Requirements 1-4 . Horngren's Financial and Managerial Accounting
Chapter 20 Using Excel P20-47 Using Excel for cost-volume-profit (CVP) analysis. Requirements: The Oceanside Garden Nursery buys flowering plants in four-inch pots for $1.00 each and sells them for $2.50 each. Management budgets monthly fixed costs of $2. 100 for 1. Use the contribution margin approach to compute the company's monthly breakeven point in 2. Use the contribution margin ratio approach to compute the breakeven point in sales dollars. 3. Use the contribution margin approach to compute the monthly sales level (in units) required to earn a target operating income of $5.000. 4. Prepare a graph of the company's CVP relationships. Include the sales revenue line. the fixed cost line, and the total cost line. Create a chart title and label the Excel Skills: 1. Create formulas with cell references 2. Format the cells as accounting number format or percent st 3. Prepare a breakeven chart. Add chart title and x-axis description. 4. Use the Excel function ROUNDUP to obtain units in whole numbers needed to achieve a minimum target operating ir Chart assistance: The table below computes the revenue, variable costs, fixed costs, and total costs for each volume. Volume 200 400 600 Revenue 800 0 1,000 500 1,000 1,200 1,500 1,400 2,000 1,600 1,800 2,500 3,000 2,000 2,200 2,400 Variable Costs 200 400 3,500 4,000 4,500 1,000 5,000 ,500 6,000 Fixed Costs 600 800 2, 100 2,100 2,100 1,200 1,400 2,100 1,600 2,100 1,800 2,200 2,100 2,300 2,500 2,700 2,100 2,400 Total Cost 2, 100 2,100 2,000 2,100 2,900 3,100 2, 100 3,300 2,100 3,500 2,100 3,700 3,900 2, 100 4,100 4,300 4,500 To create the graph: 1. Once the data are entered, select the following four rows using the CTRL key to select non-contiguous cells: Volume, Revenue, Fixed Costs, and Total Cost. This means you select Rows 22, 23, 25 and 26; Column B through Column C 2. Select the Insert tab, select "scatter or bubble chart" from the chart menu, then click "Scatter with Smooth Lines and Markers." Right click and move the chart to the Graph tab. Home Insert Draw Page Layout Formulas Tell me what you want to do 17 12 commended Table Butrations My Add- Chart 30 Ling Column Ch Scatter . CWwn\\Dian \\Documents\\Hon" W X Soutter with Smooth Lines and Markers I've this chart type Bos . Compare at least two sets of values or pairs of data 3. On the Design tab, select "Add Chart Element" then select "Chart Title" then select "Above Chart." Add the title "Breakeven."DATA Description Amount Sales price per unit $ 2.50 Variable cost per unit 1.00 Contribution margin per unit S 1.50 Total fixed costs Profit at breakeven Target profit Requirement 1 Use the contribution margin approach to compute the company's monthly breakeven in units. Reference the DATA cell(s) in your formula below. Format as a number. Breakeven in Units Requirement 2 Use the contribution margin ratio approach to compute the breakeven point in sales dollars. Reference the DATA cell(s) in your formula for contribution margin ratio below. Format the ratio as a percentage, breakeven in dollars as accounting number format in whole dollars. Contribution margin ratio Breakeven in Dollars Requirement 3 Use the contribution margin approach to compute the monthly sales level (in units) required to earn a Reference the DATA cell(s) in your formula below. Format as number. Use the Excel ROUNDUP function to obtain whole units to reach the target operating income. Target sales level (in units)Requirement 4 Prepare a graph of the company's CVP relationships. Include the sales revenue line, the fixed cost line, and the total cost line. Create a chart title and label the axes
