Cost, Volume, Profit Analysis spreadsheet

Question:
you received your instructor’s approval of your company and actual planned investment. begin working on the Cost, Volume, Profit Analysis spreadsheet provided for the project to outline the revenues, costs, expenses, and resulting cash flows. If you selected a manufacturing company and the project deals with a product or change in project, you will need to consider direct and indirect costs. Detail out your costs and cash flows. Make sure these are reasonable for the project you have selected.
 you should have all of your revenues, expenses, investments, profit, and resulting cash flows on an Excel worksheet complete with all calculations performed using Excel functions.
  • Name of Public company approved for project. JC Penney. Symbol JCP.
  • Reason chosen; to restore flagging sales back to strong financial footing.
  • Nature of investment for this company; explanation as follows….

Plan is to introduce two or three lines of product previously not carried by JC PENNEY. These products will surely bring in more clients who then will be exposed to more of the products that JC PENNEY has to offer. In addition I plan to revamp the online shopping experience, statistics show that consumers are more comfortable and adept at online shopping, Currently shoppers have to meet a certain amount of purchase before they qualify for free shipping. My plan will be to eliminate that and make shipping free of any amount spent to draw in more shoppers, introduce hassle free returns and exchanges while making sure that the profit line improves over a period of few years.

Instructions for the project excel template;

you will calculate the break-even point for the project and the expected financial returns.

  • Open the Cost-Volume-Profit spreadsheet that you have been working in and calculate the break-even point. Of your proposed project. (Access the CVP template if you have not yet begun this work)
  • The project must use a 6.5% cost of capital and a tax rate of 25%.
  • Complete IRR (Internal Rate of Return) and NPV (Net Present Value) for the project.
  • Make sure you show your Excel formulas or provide calculations so your instructor can review your work.
  • You should have also considered key points of any intangible benefits or costs associated with the project and begun supplementing your pro forma statement with sufficient background information to enable a prospective investor to decide if your company is worth investing in.
Advertisements

case study

From the case study below, prepare a report for the Board that either recommends the proposed changes or does not recommend the changes.  Support the decision with details from the case and outside sources.  (300-500 words)

Explain the following

  • Cost of Quality
  • Total Quality management (TQM)
  • Statistical Process Control (SPC)
  • Six Sigma
  • Relevant Costs
  • Sunk Costs
  • Cost Volume Profit (CVP)

—————————————————————————————————————————

 

Case study question: Swift Airlines Swift Airlines has a daily return flight from London to Nice. The aircraft for the flight has a capacity of 120 passengers. Swift sells its tickets at a range of prices. Its business plan works on the basis of the following mix of ticket prices for each day’s flight:

 

Business 30 @ £300 £9,000
Economy regular 40 @ £200 £8,000
Advance purchase 20 @ £120 £2,400
7-day-purchase 20 @ £65 £1,300
Stand-by 10 @ £30 £300
Revenue 120 £21,000

 

Swift’s head office accounting department has calculated its costs as follows:

 

Cost per passenger (to cover additional fuel, insurance, baggage handling etc.) assuming full load £25 per passenger

Flight costs (to cover aircraft lease, flight and cabin crew costs, airport and landing charges etc.) £3,000 (120 @ £25) £7,500 per flight

Route costs (to cover the support needed for each destination) £2,000 (based on ½ of the daily cost of £4,000 (balance charged to return flight)) Business overhead £3,000 (allocation of head office overhead)

 

Total £15,500

 

This results in a budgeted profit of £5,500 per flight, assuming that all seats are sold at the budgeted price. The head office accountant for European routes has advised the route manager for Nice that while the Nice–London inbound leg is breaking even, losses are being made on the London–Nice outbound leg. If profits cannot be generated, the route may need to be closed, with the aircraft and crew being assigned to another route. The route manager for Nice has extracted recent sales figures, a typical flight having the following sales mix:

 

% of tickets sold Business 60 18 @ £300 £5,400
Economy regular 70 28 @ £200 £5,600
Advance purchase 80 16 @ £120 £1,920
7-day purchase 75 15 @ £65 £975
Stand-by 100 10 @ £30 £300
Revenue 87 £14,195

 

The route manager has calculated a loss on each outbound flight of £1,305. She believes that there is a market for 48-hour ticket purchases if a new fare of £40 was introduced, as this would be £5 less than the price charged by a competitor for the same ticket. She estimates that she could sell 15 seats per flight on this basis. This would not affect either the 7-day purchase, which is used by business travelers, or stand-by fares, which are usually oversubscribed. The additional revenue of £600 (15 @ £40) would cover almost half the loss. The route manager has prepared a report for her manager asking that the new fare be approved and allowing her three months to prove that the new tickets could be sold. Comment on the route manager’s proposal. Case studies provide the reader with the opportunity to interpret and analyze financial information produced by an accountant for use by non-accounting managers in decision-making. There is a suggested answer for the case in Part IV, although the nature of case studies is that there is rarely a single correct answer, as different approaches to the problem can highlight different aspects of the case and a range of possible approaches are possible.

 

Accounting

Objective: Evaluate and apply managerial accounting methods, describe double-entry accounting, and explain the financial planning process.  Introduction: Just as meteorologists use data and models to forecast the weather, business managers use data and models to forecast the future for the business. Businesses require accurate, complete and up-to-date financial information. The financial information shows the health of a business at any point in time and provides information for managers to use in strategic planning. Each component of the accounting process is a building block upon which financial decision-making is formalized.  Deliverables: The essay for this lesson is required to be a minimum of 750 words that clearly demonstrate your understanding of the activity. Essays should have a clear introduction, thesis statement and conclusion, written in APA format (APAstyle.org). A minimum of three sources must be cited in-text and in the Reference list.   Activity Details:

 Step 1: 

Read the following questions, and use what you have learned about this lesson’s objectives to summarize your responses.

•What are the three main accounts a business maintains and what information is included in accounts? Why?

•What is double-entry accounting and why is double-entry accounting the only way to provide the most accurate financial information for business managers?

• Describe the accounting process used by a business manager to forecast the firm’s financial performance and financial needs over the next five years?

 

Step 2: Write a paper. Please review the essay rubric prior to proceeding.  Use these writing guidelines:

•Include a cover page and references in addition to your required word count.

• Use correct APA format.

•Double-space text.

•Use size 12 Times New Roman.

•Use section headings to organize. (optional)

•Indent paragraphs.

•Include in-text citations as appropriate.

•Use correct spelling, grammar, sentence structure and verb tense.

Accounting

Purpose of Assignment

The purpose of this assignment is to help you become familiar with the parts of the multiple‐step income statement.

Assignment Steps

Resources: Financial Accounting: Tools for Business Decision Making

Scenario: An inexperienced accountant prepared this condensed income statement for Simon Company, a retail firm that has been in business for a number of years.

SIMON COMPANY

Income Statement
For the Year Ended December 31, 2017

Revenues

Net sales

$850,000

Other revenues

22,000

872,000

Cost of goods sold

555,000

Gross profit

317,000

Operating expenses

Selling expenses

109,000

Administrative expenses

103,000

212,000

Net earnings

$105,000

As an experienced, knowledgeable accountant, you review the statement and determine the following facts:

  1. Net sales consist of: sales $911,000, less freight-out on merchandise sold $33,000, and sales returns and allowances $28,000.
  2. Other revenues consist of sales discounts $18,000 and rent revenue $4,000.
  3. Selling expenses consist of salespersons’ salaries $80,000, depreciation on equipment $10,000, advertising $13,000, and sales commissions $6,000.  The commissions represent commissions paid. At December 21, $3,000 of commissions have been earned by salespersons but have not been paid.  All compensation should be recorded as Salaries and Wages Expense.
  4. Administrative expenses consist of office salaries $47,000, dividends $18,000, utilities $12,000, interest expense $2,000, and rent expense $24,000, which includes prepayments totaling $6,000 for the first quarter of 2018.

Prepare a detailed multi-step income statement with a brief explanation of 700 words. Assume a 25% tax rate.

Show your work on the Excel® spreadsheet and submit with your explanation.

ACC403, Cost Behavior and Cost-Volume-Profit Analysis

Show computations in good format and explain answers as required. Write comments below the computations in Excel. MUST BE COMPLETED IN EXCEL.

Scenario A

Compute the break-even point in sales dollars if fixed costs are $200,000 and the total contribution margin is 20% of revenue.

Show the analysis in an excel table format, and write a one-paragraph interpretation of the information presented in the table.

Scenario B 

Danny Company makes and sells stuffed animals.  One product, Panda Bear, sells for $28 per bear.  Panda Bears incur fixed costs of $100,000 per month and a variable cost of $12 per bear.  How many Panda Bears must be produced and sold each month to break even?

Show the analysis in an excel table format, and write a one-paragraph interpretation of the information presented in the table..

Scenario C

Jerry is considering buying a company if it will break even or earn net income on revenues of $80,000 per month.  The company that Peter is considering sells each unit it produces for $5.  Use the following cost data to compute the variable cost per unit and the fixed cost for the period.  Calculate the break-even point in sales dollars.  Should Jerry purchase this company?

Volume (units)     Cost

8,000                          $70,000

68,000                       $190,000

Show the analysis in an excel table format, and write a one-paragraph interpretation of the information presented in the table.

Scenario D

Reliable Delivery currently delivers packages for $9 each. The variable cost is $3 per package, and fixed costs are $60,000 per month. Compute the break-even point in both sales dollars and units under each of the following independent assumptions. Comment on why the break-even points are different.

1. The costs and selling price are as just given.

2. Fixed costs are increased to $75,000.

3. Selling price is increased by 10%. (Fixed costs are $60,000.)

4. Variable cost is increased to $4.50 per unit. (Fixed costs are $60,000 and selling price is $9.)

5. Show the analysis in an excel table format, and write a one-paragraph interpretation of the information presented in the table.

accounting

Assignment 3: Excel ProblemsAt the  end of each module, you will apply the module’s concepts by completing  comprehensive assignments from the textbook. Complete problems P9-28A (p. 517), P10-A-9B (p. 586), and  P11-29A (p. 631) in your textbook. Present  your analysis of the assigned problems in Excel format. Enter non-numerical  responses in the same worksheet using textboxes. By Week 4, Day 7 deliver your  assignment to the M4: Assignment 3  Dropbox.Create  the file with the following name: LastnameFirstInitial_M4A3.Excel.xls. Assignment 3 Grading Criteria

Maximum Points

P9-28A:
Preparation of depreciation schedule for each depreciation method showing asset, depreciation expense, accumulate depreciation and asset  book value.

15

P10-A-9B:
Present value of Plan A

6

Present value of Plan B

6

Plan selected based on least cost

6

P11-29A:
Assess the total market value of the common stock.

5

Compute the book value per share of the common stock.

7

Accuracy and timeliness.

5

Total:

50

Assignment 1: LASA # 2—Capital Budgeting Techniques

 

As a financial consultant, you have contracted with Wheel Industries to evaluate their procedures involving the evaluation of long term investment opportunities.  You have agreed to provide a detailed report illustrating the use of several techniques for evaluating capital projects including the weighted average cost of capital to the firm, the anticipated cash flows for the projects, and the methods used for project selection.  In addition, you have been asked to evaluate two projects, incorporating risk into the calculations.

You have also agreed to provide an 8-10 page report, in good form, with detailed explanation of your methodology, findings, and recommendations.

Company Information

Wheel Industries is considering a three-year expansion project, Project A.  The project requires an initial investment of $1.5 million. The project will use the straight-line depreciation method. The project has no salvage value. It is estimated that the project will generate additional revenues of $1.2 million per year before tax and has additional annual costs of $600,000.  The Marginal Tax rate is 35%.

Required:

  1. Wheel has just paid a dividend of $2.50 per share. The dividends are expected to grow at a constant rate of six percent per year forever. If the stock is currently selling for $50 per share with a 10% flotation cost, what is the cost of new equity for the firm? What are the advantages and disadvantages of using this type of financing for the firm?
  2. The firm is considering using debt in its capital structure. If the market rate of 5% is appropriate for debt of this kind, what is the after tax cost of debt for the company? What are the advantages and disadvantages of using this type of financing for the firm?
  3. The firm has decided on a capital structure consisting of 30% debt and 70% new common stock. Calculate the WACC and explain how it is used in the capital budgeting process.
  4. Calculate the after tax cash flows for the project for each year. Explain the methods used in your calculations.
  5. If the discount rate were 6 percent calculate the NPV of the project. Is this an economically acceptable project to undertake? Why or why not?
  6. Now calculate the IRR for the project. Is this an acceptable project? Why or why not? Is there a conflict between your answer to part C? Explain why or why not?

Wheel has two other possible investment opportunities, which are mutually exclusive, and independent of Investment A above.  Both investments will cost $120,000 and have a life of 6 years. The after tax cash flows are expected to be the same over the six year life for both projects, and the probabilities for each year’s after tax cash flow is given in the table below.

 

Investment B                                                      Investment C

 

Probability      After Tax                               Probability      After Tax
Cash Flow                                                             cash flow

0.25                      $20,000                                       0.30               $22,000

 

0.50                        32,000                                        0.50                   40,000

 

0.25                        40,000                                        0.20                    50,000

  1. What is the expected value of each project’s annual after tax cash flow? Justify your answers and identify any conflicts between the IRR and the NPV and explain why these conflicts may occur.
  2. Assuming that the appropriate discount rate for projects of this risk level is 8%, what is the risk-adjusted NPV for each project? Which project, if either, should be selected? Justify your conclusions.

Assignment 1 Grading Criteria Maximum Points 

Correctly calculated the cost of new equity and explained the calculations, as well as the advantages and disadvantages of using this type of financing for the firm. (CO4) 20

Correctly calculated the cost of new debt and explained the calculations, as well as the advantages and disadvantages of using this type of financing for the firm. (CO4) 20

Correctly calculated the weighted average cost of capital and explained how and why it is used in the capital budgeting process. (CO4) 20 Correctly calculated the annual cash flows for the projects and explained the methods used in the calculations. (CO1)  44 Evaluated the projects using the NPV method and came to the correct conclusions based on the decision rules for the NPV. (CO2)44 Evaluated the projects using the IRR method and came to the correct conclusion based on the decision rules for the IRR. Identified any conflicts between the IRR and the NPV and explained why these conflicts may occur. (CO 3) 44

Correctly introduced risk into the evaluation by using the expected values as the cash flows and evaluated these cash flows using risk adjusted discounted rates. (CO 5) 44

Written in a clear, concise, and organized manner; demonstrated ethical scholarship in accurate representation and attribution of sources; displayed accurate spelling, grammar, and punctuation.64

Total:300