Homework Help is Here – Start Your Trial Now!
Business
Accounting
Required: 1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two-thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.) 2. Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand. Complete this question by entering your answers in the tabs below. Gaming Table Cost Data Contribution Income Statements Fixed costs (per year) Selling price Variable cost Planned production Planned sales volume What If Sensitivity Analysis Per Unit $ 75 35 $ Required 1 2022 69,000 2,400 units 2,400 units < Gaming Table Cost Data Required 2 2023 $ 69,000 2,600 units 2,600 units Contribution Income Statements >

Required: 1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two-thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.) 2. Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand. Complete this question by entering your answers in the tabs below. Gaming Table Cost Data Contribution Income Statements Fixed costs (per year) Selling price Variable cost Planned production Planned sales volume What If Sensitivity Analysis Per Unit $ 75 35 $ Required 1 2022 69,000 2,400 units 2,400 units < Gaming Table Cost Data Required 2 2023 $ 69,000 2,600 units 2,600 units Contribution Income Statements >

FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
10th Edition
ISBN:9781259964947
Author:Libby
Publisher:Libby
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
See similar textbooks
icon
Related questions
Question

JJ. 156.

Required: 1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two-thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.) 2. Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand. Complete this question by entering your answers in the tabs below. Gaming Table Cost Data Fixed costs (per year) Selling price Variable cost Planned production Planned sales volume Gaming Table Cost Data Gaming Table Cost Data Contribution Income Statements Sales Variable costs Total contribution margin Fixed costs Operating profit Units Sold 1,500 1,500 1,500 1,500 Contribution Income Statements Contribution Income Statements Variable Cost per Unit $ What If Sensitivity Analysis Per Unit 30 35 40 45 $ <Gaming Table Cost Data What If Sensitivity Analysis $ 75 35 $ $ $ 2022 Amount 180,000 84,000 96,000 60,000 36,000 What If Sensitivity Analysis Required 1 Fixed Costs $ 69,000 69,000 69,000 69,000 2022 69,000 2,400 units 2,400 units Required 1 Percent 100.00 % $ 46.67 53.33 % $ Required 1 $ Selling Price per Unit $ Required 2 2023 $ 69,000 <Contribution Income Statements Amount 195,000 91,000 104,000 60,000 44,000 < Gaming Table Cost Data 2,600 units 2,600 units Required 2 Contribution Income Statements > 75 $ 75 75 75 2023 Required 2 Operating Profit Percent 100.00 % $ 46.67 53.33 % $ 7,500 0 -7,500 -15,000 $ Change Notes 15.000 7,000 8,000 53.33% is the contribution margin ratio 0 8,000 $8,000 = 0.5333 × $15,000 What If Sensitivity Analysis > Required 1 >
Transcribed Image Text:Required: 1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two-thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.) 2. Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand. Complete this question by entering your answers in the tabs below. Gaming Table Cost Data Fixed costs (per year) Selling price Variable cost Planned production Planned sales volume Gaming Table Cost Data Gaming Table Cost Data Contribution Income Statements Sales Variable costs Total contribution margin Fixed costs Operating profit Units Sold 1,500 1,500 1,500 1,500 Contribution Income Statements Contribution Income Statements Variable Cost per Unit $ What If Sensitivity Analysis Per Unit 30 35 40 45 $ <Gaming Table Cost Data What If Sensitivity Analysis $ 75 35 $ $ $ 2022 Amount 180,000 84,000 96,000 60,000 36,000 What If Sensitivity Analysis Required 1 Fixed Costs $ 69,000 69,000 69,000 69,000 2022 69,000 2,400 units 2,400 units Required 1 Percent 100.00 % $ 46.67 53.33 % $ Required 1 $ Selling Price per Unit $ Required 2 2023 $ 69,000 <Contribution Income Statements Amount 195,000 91,000 104,000 60,000 44,000 < Gaming Table Cost Data 2,600 units 2,600 units Required 2 Contribution Income Statements > 75 $ 75 75 75 2023 Required 2 Operating Profit Percent 100.00 % $ 46.67 53.33 % $ 7,500 0 -7,500 -15,000 $ Change Notes 15.000 7,000 8,000 53.33% is the contribution margin ratio 0 8,000 $8,000 = 0.5333 × $15,000 What If Sensitivity Analysis > Required 1 >
Gaming Table Cost Data DOL Contribution Income Statements Gaming Table Cost Data Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two- thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.) (Input your answer as a percentage rounded to 2 decimal places (i.e., 0.1567 = 15.67%).) % What If Sensitivity Analysis Contribution Income Statements Required 1 <What If Sensitivity Analysis Required 2 What If Sensitivity Required 1 Required 2 Analysis Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand. Operating profit < Required 1 Required 2 > Required 2 > Show less A
Transcribed Image Text:Gaming Table Cost Data DOL Contribution Income Statements Gaming Table Cost Data Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two- thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.) (Input your answer as a percentage rounded to 2 decimal places (i.e., 0.1567 = 15.67%).) % What If Sensitivity Analysis Contribution Income Statements Required 1 <What If Sensitivity Analysis Required 2 What If Sensitivity Required 1 Required 2 Analysis Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand. Operating profit < Required 1 Required 2 > Required 2 > Show less A
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Recommended textbooks for you
FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
Accounting
ISBN:
9781259964947
Author:
Libby
Publisher:
MCG
Accounting
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS