Prepare a Graph and Derive Information from the Graph

1. Prepare a cost-volume-profit (CVP) graph using only the baseline case. Below are some instructions for creating a graph. For detailed instructions, download the instructions that are available to you in the spreadsheet assignment link.

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, Variable cost, 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.

PARAMETERS FOR 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	150,000
Selling price per unit	$220.00
	Fixed Expenses	Variable Expenses (per unit sold
Production costs:
Direct materials		$16.00
Direct labor		32.00
Factory overhead	$2,360,000	22.00
Marketing expenses:
Sales salaries and commissions	520,000	6.50
Advertising	320,000
Miscellaneous mktg. expenses	120,000
Administration expenses:
Office salaries	650,000
Supplies	103,000	2.00
Miscellaneous admin. expenses	75,000
TOTAL EXPENSES	$4,148,000	$78.50