What is a Cost volume profit analysis?

Download files from this location. You MUST use the Excel data file I started for your solution. Do NOT create a new Excel file. Just add to this file that you download. If you mess up and need to start over, just download this Excel file again. As noted in the instructions, failure to use this file will result in a 15 point deduction (out of 60 points for the assignment). Be sure you are finished with the assignment before you upload your files and submit them electronically. You will be uploading two files electronically–an Excel file and a Word file. You can upload more than one file with one submission. After you’ve uploaded your files, be sure to press the SUBMIT button. Don’t wait until the last minute to upload and submit your files. With technology, you never know what might happen.

Your Name:
Your Course:
Date:
ACCT 3333
19-Aug-24
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
Selling price per unit
Production costs:
Direct materials
Direct labor
Factory overhead
Marketing expenses:
Sales salaries and commissions
Advertising
Miscellaneous mktg. expenses
Administration expenses:
Office salaries
Supplies
Miscellaneous admin. expenses
TOTAL EXPENSES
52,000
$495.00
Fixed
Expenses
Variable
Expenses
(per unit sold
$6,565,000
$36.00
74.00
48.00
1,645,000
1,100,000
322,000
2,180,000
365,000
376,000
$12,553,000
15.00
2.70
$175.70
ACCT 3333
Spreadsheet Project
Fall 2024
Academic Honesty
You must work on this project individually. You are not to ask anybody for assistance other than
Amanda Bates or a tutor at the University of Houston-Clear Lake Student Success Center.
Failure to adhere to the academic honesty rules for this assignment will result in a score of zero.
Purpose of Assignment
This project will give you experience in designing and implementing a spreadsheet to assist you
in making decisions.
Estimated Time to Complete
This assignment will likely require four to five hours for a basic to intermediate spreadsheet user.
Guidelines
You may find it helpful to read the guidelines in the “Identifying Elements of a Financial
Model” section in Chapter 12 of the Hilton (2008) textbook (now out-of-print). I have uploaded
Chapter 12 of that textbook with this file of instructions. I will deduct points if you do not
follow the advice that “…parameters should be located and clearly identified in a defined
parameter or input-data area of the spreadsheet” and “…the formulas in the analysis
sections should never contain the actual numerical values of the parameters. Instead use
the parameters’ cell locations in all formulas where they occur” (Hilton, 2008, pages 476 and
477). In other words, you should have a parameters (input) section separate from the section to
answer a question. The sections used to answer the questions should only include cell reference
formulas and should not contain hard coded numbers. To help you adhere to these guidelines, I
have started your Excel solution file and created a ‘Parameters’ section for you. That file is
uploaded with this file of instructions.
Spreadsheet Format
Each sheet of your Excel file should include your name, the course number, and the date. Place
this information at the top, either in a header or in the first few rows. Label each sheet of your
workbook so it clearly identifies the sheet. I have labeled the first sheet as ‘Baseline Case.’
1
Requirements
The model parameters for the baseline case are entered in the Excel spreadsheet, which I have
uploaded with this assignment. The sheet for the baseline case is named ‘Baseline Case.’
Download that Excel file and complete your assignment using that file. Do not create a new
Excel file. Just add to the file that you download. Failure to use this file will result in a 15
point deduction (out of 75 points for the assignment). Use your last name to rename the file I
have uploaded. If you “mess up” and need to start over, download the Excel file again.
The assignment requires you to use and manipulate the baseline case model parameters in three
different ways:
1. Complete a CVP analysis for the baseline case;
2. complete four other scenarios (i.e., what-if analyses), and recommend the best scenario;
and
3. prepare a graph and derive information from the graph.
1. Make CVP Calculations for the Baseline Case
On the first sheet of your workbook (the sheet labeled ‘Baseline Case’), do a-d below.
(a) Prepare a contribution margin income statement (also called a variable-costing
income statement) for the manufacturing company for the upcoming year. Examples
of contribution income statements are on page 68, Exhibit 3-1 of the Datar and Rajan
textbook and in the Chapter 3 course notes. Create this statement below the baseline case
parameters in the Excel file you download. Label each line item.
Check figure: Operating profit (operating income) of $4,050,600.
(b) Compute the company’s contribution margin per unit and contribution margin
percentage for the upcoming year. The contribution margin percentage is calculated as
contribution margin per unit / selling price per unit or as total contribution margin / total
revenue. Make these calculations below your income statement. Clearly label these
calculations.
(c) Calculate the company’s breakeven point in units for the upcoming year. Make this
calculation below your contribution margin calculations. Use Excel’s “round” function
to round up to the nearest whole number. To do this, move your curser to the cell beside
the decimal number and key in the following formula: =ROUNDUP(cell reference,0).
The italicized cell reference means you need to key in the cell where the decimal number
is located (e.g., F12). The number 0 means zero decimal places. Clearly label this
calculation.
(d) Calculate the company’s breakeven point in sales dollars for the upcoming year.
Make this calculation below your breakeven calculation in units and use the company’s
contribution margin percentage to make this calculation. Clearly label this
calculation.
2
2. Complete Four Scenarios (What-if Analyses)
You want to determine whether the following four suggestions (i.e., e, f, g, h) would improve
the company’s performance. Determine the effects of each suggestion on operating
income, contribution margin per unit, contribution margin percentage, breakeven point
in units, and breakeven point in sales dollars.
Calculate the effects of each suggestion independently of the other suggestions. In other
words, use the original baseline case data and make the first change (e); use the original
baseline case data and make the second change (f); and so on. However, do not overwrite the
original baseline case. The easiest way is to copy the original data to a new sheet and then
replace the original data parameters. To copy a sheet, right click on the sheet name. Select
“Move or Copy.” Click on the “Create a copy” box. Click OK. Rename your new sheet to
indicate the name of the new scenario. For example, you could name the sheet for (e)
‘Commission.’ To rename a sheet, right click on the sheet name. Select “Rename.” Key in
the new name.
(e) Put all personnel on commission. This action would affect the sales salaries and
commissions expense by eliminating the fixed portion and increasing the variable portion
by $60.00 per unit. Sales would increase by 5,000 units.
(f) Redesign the package for the product. This would decrease the variable direct materials
cost by $1.00 per unit but would increase the fixed factory overhead by $60,000.
(g) Launch a new advertising campaign. This would increase fixed advertising expense by
$200,000 but would increase sales volume by 6,000 units.
(h) Reduce the selling price of the product by $25.00 per unit. This would increase sales
volume by 8,000 units.
(i) Write a memo, explaining whether or not the company should use each suggestion, and
make a recommendation about which suggestion is the best. Create your memo in
Word, not Excel. Use proper memo format. Word has a memo template (FILE | NEW |
type in ‘memo’ in the ‘search for online templates’ box).
3. Prepare a Graph and Derive Information from the Graph
(j) 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, and Total
Costs. In Excel, the type of chart you need to select is the “scatter (with straight lines and
markers).” 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 50,000 does not give enough detail. Showing the number of units in
increments of 2,000 gives too much detail.
3
(k) 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 50,000 units, the profit is approximately $_____. Create your memo in
Word, not Excel. Create this memo as a new memo in the same Word file you used for
the memo in 2. (i).
Grading
I will deduct points for failure to follow instructions, or if the spreadsheet or graph is difficult to
follow. Also, on this assignment, I count off for carry-through errors. After all, this is a
spreadsheet that would be used by a business.
The grading rubric is as follows:
Excel skills
1) Inputting data and
writing formula using cell
references (absolute and
relative references) (12 pts);
2) Generating CVP graph
with relevant data (13 pts)
Spreadsheet
1) The correctness of parameters
(10 pts);
2) The contribution margin
income statement (10 pts);
3)The contribution margin per
unit, the contribution margin
percentage, the break-even units,
and the break-even revenue (10
pts).
Two memos
1) For the recommendations
memo, identify the problem,
apply relevant concepts, use
relevant evidence (data), and
provide logical conclusions (16
pts)*;
2) For the CVP memo,
derivation of three pieces of
information from the CVP
graph (4 pts).
*The following is the grading rubric for the recommendation memo (your critical thinking skills are
evaluated here): You are expected to identify the problem, apply relevant concepts, use relevant evidence,
and provide logical conclusions in this memo. You will be graded by how you achieve each objective; for
poor, unsatisfactory, satisfactory, and excellent, you will get 1, 2, 3 and 4 points, respectively. For
example, for the objective of “use relevant evidence”, you will obtain 3 points if your work is
satisfactory; for the objective of “provide logical conclusions”, you will obtain 4 points if your work is
excellent.
Please see the syllabus for point deductions for late submissions.
Submitting Your Assignment
Submit an electronic copy of your assignment as a file upload in the ‘Assignments’ area of
Canvas. You can upload more than one file. Due to potential technical difficulties, it is not a
good idea to submit your assignment right before the deadline.
4
Instructions for Creating a Graph in Excel 2010
Note: The way to create a graph in more recent versions of Excel is very similar.
1. Create a table of data in Excel similar to the following example. These data are for
illustration purposes only. You need to use the data given to you in your spreadsheet
assignment. The numbers of units are up to you; just be sure your first row is 0 units so your
lines will intersect the y axis.
2. Select Insert. Click on the down arrow beside Scatter and then click on the picture of the
graph with data points connected by straight lines and markers.
3. Drag the graph box to approximately line up under the table of data.
4. Put your pointer on the graph. Click on the Design chart tool tab and then click on the Select
Data icon.
1
A dialog box will appear. You will see a blank box beside “Chart data range.” To fill that in,
highlight the entire table of data you created including the headings. The graph automatically
appears similar to the one below. Yours will look different because you used different data in
the table you created.
You can embellish your graph. For example, you could add a graph heading and add labels for
the x and the y axes. Also, you could format the y axis to currency, i.e., dollar signs and
commas. Additionally, you can choose the intervals for your points on the axes (e.g., on the xaxis, have points for every 10,000 units, or every 20,000 units, and so on.) This describes only a
portion of the different types of embellishments you can make.
2