Cost Volume Profit Analysis

MBA691 Managerial Accounting

Project 3 – Cost/volume/profit (CVP) analysis [30 points]

[can be started after chapter 3 case is complete]

 

First, find your company on the individual changes list posted in Canvas. Check that your company has had positive net operating income (also called earnings before income and taxes) for the last two years.

Second, obtain the latest balance sheet and income statement for your company (will be for 2016 if your company has a December 2016 ending fiscal year [FY], or 2017 they had a FY year ending between January and June 2017). The balance sheet and income statement must include data on your company’s most recent two years of activity. Download the financials into an Excel spreadsheet from any of these (or your own) sources:

  • Your company’s website. Go to Investor relations, and download the financials directly into Excel either from the annual report or the SEC’s 10K [you may have to right click and export to Excel]
  • SEC Edgar database http://www.sec.gov/edgar/searchedgar/companysearch.html , or
  • any other sources you are familiar with that provide detailed financial statements

 

Please identify your company and the source of your financial statement data on the first worksheet (Income Statement) of your Excel file. Verify that your company has net operating income (not a loss) for the most recent two years. This is not the same as net income! Finally, check that the data that you downloaded is in numeric and not text format. Try adding two cells – if you get a “VALUE!” error, it means that your data is in text format and is not usable. You will need to find another source for your company’s financial statements.

 

 

The financials must be downloaded into one Excel file with two worksheets in this order and named as follows: Income Statement and Balance Sheet. Please save and name the Excel file as YourLastNameFirstInitialProject3 (so my file would be BorkowskiSProject3). Then, save it again as a second file to be used later in the semester as YourLastNameFirstInitialProject11.

Third, open the Project 3 template (posted on Canvas) and copy the template worksheet exactly as it is set up into a third new worksheet in your Project3 Excel file. Name this worksheet Template. This template MUST be completed as part of the project. Do not submit your project for feedback unless this template is completed and included in your Excel file.

 

            Finally, assume the following allocations for your company’s product (COGS) and period (operating) costs:

  • Cost of goods sold (or Cost of sales, or however named on your company’s income statement) consists of 55% variable and 45% fixed costs for each of the last two years
  • Operating (or SG&A, or however named on your income statement) expenses consist of 25% variable and 75% fixed costs for each of the last two years.

In reality, both COGS and operating expenses would include a variety of variable, fixed and mixed costs, and percentages would vary by industry, and then by company within industry.

 

REQUIRED

You are now ready to undertake a CVP analysis of your company’s current and prior years.

 

You must use formulas in the cells, not typed-in numbers.  All data, formulas, etc. must be done on the Income Statement worksheet starting in row 1 in the second empty column to the right of your financial statement. All discussion components must be in a separate Word document – the discussion components of a question are NOT to be included in your Excel document.

 

  1. Calculate the break-even point in dollars for BOTH current and the prior years. All formulas must be done on the Income Statement worksheet, and the answers to the questions must be done in a separate Word document. Why do the numbers that you computed differ from one another across the two years?
  2. CURRENT YEAR ONLY: Are you able to calculate the break-even point in units for the current year? Why or why not?
  3. CURRENT YEAR ONLY: What sales volume would have been necessary for your company to attain a target income from operations that is US$950 million greater than the current operating income?
  4. Calculate the margin of safety and the margin of safety percentage for BOTH current and the prior years. Interpret/explain your margin of safety calculations. What do these numbers tell you about the riskiness of your company?
  5. What is your company’s degree of operating leverage for BOTH current and the prior years? Interpret/explain your OL calculations. Finally, if your company’s sales had been 2% LOWER than what is shown in the current year’s income statement, what net operating income would your company have earned? What percentage change in operating income does this represent?
  6. CURRENT YEAR ONLY: What income from operations would your company have earned if it had invested an additional US$500 million in advertising and promotions, which generated 2% increase in sales revenue? Should you go ahead with the additional advertising? Why or why not?
  7. Given ONLY your findings of this CVP analysis and what you see on the financial statements in your Excel file [do not calculate any additional ratios or do any additional research], would you recommend investing $100,000 in this company’s stock today? Why or why not?

 

SUMMARY OF REQUIREMENTS FOR PROJECT 3:

  1. one Excel spreadsheet file with three worksheets (income statement, balance sheet and your Project 3 template) and all required calculations using formulas on the income statement worksheet. This file must be posted to Canvas no later than the start of class on the due date.
  2. one Word document (with your analyses to any required discussions in items 1-7 clearly labeled) to be handed in as hard copy as you come into class AND posted to Canvas. Bring a copy for yourself for in-class discussion
  3. You may submit your Excel worksheet to me for feedback on your technical calculations until 6PM two days before the due date. You must have the Project 3 template included in your Excel file if you want feedback.

Order Now