A B D E Data Section: Fixed Variable Production costs: Direct materials $2.30 Direct labor 4.70 $225,000 Factory overhead Selling expenses: 3.00 Sales salaries & commissions 97,000 0.75 Advertising 47,500 Miscellaneous selling expense General expenses: 16,200 Office salaries 92,000 Supplies Miscellaneous general expense 12,300 0.25 15,000 Total $505,000.00 $11.00 Additional information: $16.00 Selling price per unit Expected unit sales Target profit 120,000 $20,000 Answer Section: $5.00 $600,000.00 Contribution margin per unit Contribution margin (Total) Contribution margin ratio Break-even point in units Break-even point in sales dollars Target profit (Unit sales needed) Target profit (Dollar sales needed) 31.25% 101,000 $1,616,000.00 105,000 $1,680,000.00 Total revenue at expected unit sales FORMULA10 Net income at expected unit sales FORMULA11 Margin of safety as a percentage of sales FORMULA12 Sheet1
A B D E Data Section: Fixed Variable Production costs: Direct materials $2.30 Direct labor 4.70 $225,000 Factory overhead Selling expenses: 3.00 Sales salaries & commissions 97,000 0.75 Advertising 47,500 Miscellaneous selling expense General expenses: 16,200 Office salaries 92,000 Supplies Miscellaneous general expense 12,300 0.25 15,000 Total $505,000.00 $11.00 Additional information: $16.00 Selling price per unit Expected unit sales Target profit 120,000 $20,000 Answer Section: $5.00 $600,000.00 Contribution margin per unit Contribution margin (Total) Contribution margin ratio Break-even point in units Break-even point in sales dollars Target profit (Unit sales needed) Target profit (Dollar sales needed) 31.25% 101,000 $1,616,000.00 105,000 $1,680,000.00 Total revenue at expected unit sales FORMULA10 Net income at expected unit sales FORMULA11 Margin of safety as a percentage of sales FORMULA12 Sheet1
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
Related questions
Question
Accounting excel practice problem - I already solved the first few sub parts to this question - I just need the last sub part answered. I attached a picture of the instructions and a picture of the excel spreadsheet to answer the remaining "formulas".
![20211_ACG2071_Module 5 CVP NEW (1) - Excel
alexandra
困
A
File
Home
Insert
Page Layout
Formulas
Data
Review
View
Help
Tell me what you want to do
& Share
X Cut
E AutoSum
Calibri
11
A A
22 Wrap Text
General
e Copy
V Fill
Paste
В I
$ • %
Conditional Format as
Cell
Insert Delete Format
Sort & Find &
.00
U
E Merge & Center
00
V Format Painter
Formatting
Table
Styles
O Clear
Filter - Select -
Clipboard
Font
Alignment
Number
Styles
Cells
Editing
М35
fx
A
B
F
G
H
K
M
Q
R
9
Data Section:
Fixed
Variable
10
11
Production costs:
12
Direct materials
$2.30
13
Direct labor
4.70
14
$225,000
Factory overhead
Selling expenses:
3.00
15
16
Sales salaries & commissions
97,000
0.75
Advertising
Miscellaneous selling expense
17
47,500
18
16,200
19
General expenses:
20
Office salaries
92,000
21
12,300
0.25
Supplies
Miscellaneous general expense
22
15,000
23
24
Total
$505,000.00
$11.00
25
========
======
26
27
Additional information:
$16.00
Selling price per unit
Expected unit sales
Target profit
28
120,000
$20,000
29
30
31
32
33
34
Answer Section:
35
Contribution margin per unit
Contribution margin (Total)
36
$5.00
37
$600,000.00
38
Contribution margin ratio
31.25%
39
101,000
Break-even point in units
Break-even point in sales dollars
Target profit (Unit sales needed)
Target profit (Dollar sales needed)
40
$1,616,000.00
41
105,000
42
$1,680,000.00
43
44
Total revenue at expected unit sales
FORMULA10
45
FORMULA11
Net income at expected unit sales
Margin of safety as a percentage of sales
46
FORMULA12
47
Sheet1
75%
9:26 PM
O Type here to search
99+
5/25/2021](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2Fe1336aa0-e91b-4a1a-a973-d70bd54c1ee9%2F3ea450f1-28e6-4998-a798-c9e39e85c64a%2Fwow7bvm_processed.png&w=3840&q=75)
Transcribed Image Text:20211_ACG2071_Module 5 CVP NEW (1) - Excel
alexandra
困
A
File
Home
Insert
Page Layout
Formulas
Data
Review
View
Help
Tell me what you want to do
& Share
X Cut
E AutoSum
Calibri
11
A A
22 Wrap Text
General
e Copy
V Fill
Paste
В I
$ • %
Conditional Format as
Cell
Insert Delete Format
Sort & Find &
.00
U
E Merge & Center
00
V Format Painter
Formatting
Table
Styles
O Clear
Filter - Select -
Clipboard
Font
Alignment
Number
Styles
Cells
Editing
М35
fx
A
B
F
G
H
K
M
Q
R
9
Data Section:
Fixed
Variable
10
11
Production costs:
12
Direct materials
$2.30
13
Direct labor
4.70
14
$225,000
Factory overhead
Selling expenses:
3.00
15
16
Sales salaries & commissions
97,000
0.75
Advertising
Miscellaneous selling expense
17
47,500
18
16,200
19
General expenses:
20
Office salaries
92,000
21
12,300
0.25
Supplies
Miscellaneous general expense
22
15,000
23
24
Total
$505,000.00
$11.00
25
========
======
26
27
Additional information:
$16.00
Selling price per unit
Expected unit sales
Target profit
28
120,000
$20,000
29
30
31
32
33
34
Answer Section:
35
Contribution margin per unit
Contribution margin (Total)
36
$5.00
37
$600,000.00
38
Contribution margin ratio
31.25%
39
101,000
Break-even point in units
Break-even point in sales dollars
Target profit (Unit sales needed)
Target profit (Dollar sales needed)
40
$1,616,000.00
41
105,000
42
$1,680,000.00
43
44
Total revenue at expected unit sales
FORMULA10
45
FORMULA11
Net income at expected unit sales
Margin of safety as a percentage of sales
46
FORMULA12
47
Sheet1
75%
9:26 PM
O Type here to search
99+
5/25/2021
![20211 ACG2071_Module 5 Excel Spreadsheet Instructions - Word
alexandra
困
A
File
Home
Insert
Design
Layout
References
Mailings
Review
View
Help
Tell me what you want to do
유 Share
% Cut
Arial
A A
:E - E -
O Find -
- 12
Aa v
AaBbCcDd AaBbCcDd AaBbC AABBCCD AaB AaBbCcD AaBbCcDd AaBbCcDd AaBbCcDd
E Copy
dac Replace
Paste
В I
U v abe x, X
aby v
1 Normal
1 No Spac. Heading 1
Heading 2
Title
Subtitle
Subtle Em.. Emphasis
Intense E.
V Format Painter
A Select -
Clipboard
Font
Paragraph
Styles
Editing
Inputs:
Data set #3
Below you will see three sets of inputs. After inputting all of your formulas, you should
Data Section:
Variable
Inputs:
Fixed
be able to use any of these sets of data and have the answers automatically update.
Data Section:
Fixed
Variable
Production costs:
Please choose one of the data sets above and input all of the necessary formulas to
find the answers. Once you are done, choose a different dats set, enter it into your
spreadsheet, and check the updated answers to ensure that everything is flowing
through the formulas appropriately. A check answer for each one has been provided.
Direct materials
$2.30
Production costs:
Direct labor
4.70
Direct materials
$2.30
$300,000
Factory overhead
Selling expenses:
Direct labor
Factory overhead
$300,000
5.50
Sales salaries & commissions
97,000
Advertising
Miscellaneous selling expense
Selling expenses:
Sales salaries & commissions
Data set #1
16,200
97,000
Advertising
Miscellaneous selling expense
47,500
General expenses:
Inputs:
16,200
Office salaries
92,000
General expenses:
Data Section:
Fixed
Variable
Supplies
12,300
0.50
Miscellaneous general expense
Ofice salaries
92,000
12,300
Supplies
Miscellaneous general expense
Production costs:
20,000
Total
Direct materials
$2.30
Direct labor
4.70
Total
Factory overhead
$225,000
3.00
E===
Additional information:
Selling expenses:
$18.00
Selling price per unit
Expected unit sales
Sales salaries & commissions
97,000
Additional information:
150,000
Advertising
47,500
Selling price per unit
$24.00
Miscellaneous selling expense
Target profit
$50,000
16,200
Expected unit sales
150,000
General expenses:
Office salaries
Check figure (Target profit – Dollar sales needed):
Target profit
$50,000
92,000
Supplies
12.300
0.25
$1,828,800.00
Check figure (Target profit – Dollar sales needed):
Miscellaneous general expense
$1,612,698.41
Total
=====
Additional information:
Selling price per unit
$16.00
Expected unit sales
120,000
Target profit
$20,000
Check figure (Target profit - Dollar sales needed):
$1,680,000.00
Data set #2
Page 1 of 3
122 words
50%
8:52 PM
O Type here to search
(99+
5/25/2021](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2Fe1336aa0-e91b-4a1a-a973-d70bd54c1ee9%2F3ea450f1-28e6-4998-a798-c9e39e85c64a%2Ff3vqnyd_processed.png&w=3840&q=75)
Transcribed Image Text:20211 ACG2071_Module 5 Excel Spreadsheet Instructions - Word
alexandra
困
A
File
Home
Insert
Design
Layout
References
Mailings
Review
View
Help
Tell me what you want to do
유 Share
% Cut
Arial
A A
:E - E -
O Find -
- 12
Aa v
AaBbCcDd AaBbCcDd AaBbC AABBCCD AaB AaBbCcD AaBbCcDd AaBbCcDd AaBbCcDd
E Copy
dac Replace
Paste
В I
U v abe x, X
aby v
1 Normal
1 No Spac. Heading 1
Heading 2
Title
Subtitle
Subtle Em.. Emphasis
Intense E.
V Format Painter
A Select -
Clipboard
Font
Paragraph
Styles
Editing
Inputs:
Data set #3
Below you will see three sets of inputs. After inputting all of your formulas, you should
Data Section:
Variable
Inputs:
Fixed
be able to use any of these sets of data and have the answers automatically update.
Data Section:
Fixed
Variable
Production costs:
Please choose one of the data sets above and input all of the necessary formulas to
find the answers. Once you are done, choose a different dats set, enter it into your
spreadsheet, and check the updated answers to ensure that everything is flowing
through the formulas appropriately. A check answer for each one has been provided.
Direct materials
$2.30
Production costs:
Direct labor
4.70
Direct materials
$2.30
$300,000
Factory overhead
Selling expenses:
Direct labor
Factory overhead
$300,000
5.50
Sales salaries & commissions
97,000
Advertising
Miscellaneous selling expense
Selling expenses:
Sales salaries & commissions
Data set #1
16,200
97,000
Advertising
Miscellaneous selling expense
47,500
General expenses:
Inputs:
16,200
Office salaries
92,000
General expenses:
Data Section:
Fixed
Variable
Supplies
12,300
0.50
Miscellaneous general expense
Ofice salaries
92,000
12,300
Supplies
Miscellaneous general expense
Production costs:
20,000
Total
Direct materials
$2.30
Direct labor
4.70
Total
Factory overhead
$225,000
3.00
E===
Additional information:
Selling expenses:
$18.00
Selling price per unit
Expected unit sales
Sales salaries & commissions
97,000
Additional information:
150,000
Advertising
47,500
Selling price per unit
$24.00
Miscellaneous selling expense
Target profit
$50,000
16,200
Expected unit sales
150,000
General expenses:
Office salaries
Check figure (Target profit – Dollar sales needed):
Target profit
$50,000
92,000
Supplies
12.300
0.25
$1,828,800.00
Check figure (Target profit – Dollar sales needed):
Miscellaneous general expense
$1,612,698.41
Total
=====
Additional information:
Selling price per unit
$16.00
Expected unit sales
120,000
Target profit
$20,000
Check figure (Target profit - Dollar sales needed):
$1,680,000.00
Data set #2
Page 1 of 3
122 words
50%
8:52 PM
O Type here to search
(99+
5/25/2021
Expert Solution
![](/static/compass_v2/shared-icons/check-mark.png)
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.Recommended textbooks for you
![FINANCIAL ACCOUNTING](https://compass-isbn-assets.s3.amazonaws.com/isbn_cover_images/9781259964947/9781259964947_smallCoverImage.jpg)
![Accounting](https://www.bartleby.com/isbn_cover_images/9781337272094/9781337272094_smallCoverImage.gif)
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
![Accounting Information Systems](https://www.bartleby.com/isbn_cover_images/9781337619202/9781337619202_smallCoverImage.gif)
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
![FINANCIAL ACCOUNTING](https://compass-isbn-assets.s3.amazonaws.com/isbn_cover_images/9781259964947/9781259964947_smallCoverImage.jpg)
![Accounting](https://www.bartleby.com/isbn_cover_images/9781337272094/9781337272094_smallCoverImage.gif)
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
![Accounting Information Systems](https://www.bartleby.com/isbn_cover_images/9781337619202/9781337619202_smallCoverImage.gif)
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
![Horngren's Cost Accounting: A Managerial Emphasis…](https://www.bartleby.com/isbn_cover_images/9780134475585/9780134475585_smallCoverImage.gif)
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
![Intermediate Accounting](https://www.bartleby.com/isbn_cover_images/9781259722660/9781259722660_smallCoverImage.gif)
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
![Financial and Managerial Accounting](https://www.bartleby.com/isbn_cover_images/9781259726705/9781259726705_smallCoverImage.gif)
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education