Question
Prepare a Graph and Derive Information from the Graph BASELINE CASE The following numbers are estimates for the upcoming year for a manufacturing company. Since
Prepare a Graph and Derive Information from the Graph
BASELINE CASE | ||||
The following numbers are estimates for the upcoming year for a manufacturing company. | ||||
Since the company is effective at implementing a JIT inventory system, assume there is | ||||
no beginning or ending inventory. | ||||
No. of units sold | 120,000 | |||
Selling price per unit | $240.00 | |||
Fixed Expenses | Variable Expenses (per unit sold | |||
Production costs: | ||||
Direct materials | $18.00 | |||
Direct labor | 36.00 | |||
Factory overhead | $2,160,000 | 24.00 | ||
Marketing expenses: | ||||
Sales salaries and commissions | 540,000 | 7.50 | ||
Advertising | 360,000 | |||
Miscellaneous mktg. expenses | 108,000 | |||
Administration expenses: | ||||
Office salaries | 720,000 | |||
Supplies | 105,000 | 1.50 | ||
Miscellaneous admin. expenses | 72,000 | |||
TOTAL EXPENSES | $4,065,000 | $87.00 | ||
Contribution Margin Income Statement | ||||
Sales Revenues (120,000 Units at $240) | $28,800,000.00 | |||
Variable Costs: | ||||
Direct Materials (120,000 Units at $18) | $2,160,000 | |||
Direct Labor (120,000 Units at $36) | $4,320,000 | |||
Variable factory Overhead (120,000 x $24) | $2,880,000 | |||
Variable selling expenses (120,000 x 7.50) | $900,000 | |||
Variable Adminstrative Expenses (120,000 x 1.50) | $180,000 | |||
Total Variable Cost | $10,440,000.00 | |||
Contribution Margin (Sales - Total Variable Cost) | $18,360,000.00 | |||
Total Fixed Costs | $4,065,000.00 | |||
Operating Income | $14,295,000.00 | |||
Contribution Margin Per Unit (B11 - C26) | ||||
=Unit selling price Unit total variable cost | ||||
=$240 - $87 | ||||
=$153 per unit | ||||
Contribution Margin Percentage (A46 / B11 * 100) | ||||
=Unit Contribution Margin / Unit Selling Price * 100 | ||||
=$153 / $240 * 100 | ||||
=63.75% | ||||
Breakeven Point in Units (C40 / A51) | ||||
=Total Fixed Costs / Contribution Percentage | ||||
=$4,065,000 / 63.75% | ||||
=$6,376,471 |
Prepare a cost-volume-profit (CVP) graph using only the baseline case. Below are some instructions for creating a graph.
You need to first prepare a table with your data so that you can instruct Excel to graph the table. You will need the following columns: Units, Revenue, Fixed Costs, and Total Costs. In Excel, the type of chart you need to select is the scatter. This makes both axes what Excel calls value axes. Since both x and y axes have values, you will be able to control the tick marks on both axes. The graph should be readable and easy to interpret. For example, showing the number of units on the graph in increments of 100,000 does not give enough detail. Showing the number of units in increments of 100 gives too much detail.
Write a memo stating at least three pieces of information you can derive from reading the graph. For example, one item of information could be that if the manufacturing company sells 20,000 units, the revenue is approximately $_____.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started