Practice Cost-Volume-Profit analysis using Excel.  More specifically, you will be:

• Determining and graphing the Total Revenue and Total Cost functions,
• algebraically determining changes in profits/loss as the fixed costs change due to (1) new purchases, and (2) discounts,
• graphically determining the effects on break-even output levels as the fixed costs change.

To guide you through the completion of this case, please follow the breakdown below:

Case Description

The Task

The Process

The Evaluation

The Submission

 

Case Description

This case considers the implications of Grace (TysonBooks) changing prices of items that she has ordered for her bookstore.

The Task

The overall task for this case is to analyze the total revenue, total cost, and overall break-even values as fixed cost change.

Generate an Excel worksheet and name it CVP Analysis.

 

Generate simple tables (as shown). The values in Table 1 are based on the TysonBooks example in the Week 3 module ("Now It's Your Turn: Fixed and Variable Costs" topic), and the purchases made in Case 1. Table 2 is used to create table of values for graphing the TR and TC functions. Use cell references of Table 1 values when creating Table 2 values.

 

S, VC, FC, and Maximum capacity values can be found in the Week 3 module.

Invoice - Total Amount Due: This value is from your Case 1 invoice. Use the total due before any partial payment.

FC - New: add the Invoice - Total Amount Due to the original Fixed Costs (FC).

Invoice - Adjusted Total Amount Due: This value is calculated by reducing the Invoice - Total Amount Due by 8%.

FC - Adjusted: add Invoice - Adjusted Total Amount Due to the original Fixed Cost (FC).

 

Table 1
Selling price (S)
Unit variable cost (VC)
Fixed Costs (FC)
Invoice - Total Amount Due
Invoice - Adjusted Total Amount Due
New Fixed Costs (FC - New)
Adjusted Fixed Costs (FC - Adjusted)
Maximum capacity

 

In Table 2 replace Max capacity in the Units column with the value in Table 1. Calculate Mid point by dividing Max capacity by two. generate Excel formulas and use cell references to calculate revenue and cost values based on the Units value in the first column.

TR = Sx, TC = VCx + FC, New TC = VCx + "FC - New", and Adjusted TC = VCx + "FC - Adjusted", where x = units.

 

New TC is calculated after adding the Total Amount Due from the Case 1 invoice to the original Fixed Cost. The Adjusted TC is calculated from the supplier giving TysonBooks an overall discount of 8% on the Total Amount Duefrom the Case 1 invoice. The 8% discount is applied to the new purchases only and not the original fixed costs.

 

Table 2
Units	TR	TC	New TC	Adjusted TC
0
Mid point
Max capacity

 

Generate a graph that plots TR, TC, New TC and Adjusted TC vs. Units. The graph must have a title, a legend, and labels for each axis. Plot all four lines on the same graph.

 

This video will help you create line graphs in Excel: How To Create A Line Graph In Excel (With Multiple Lines). It is recommended that you use the desktop version of Excel (free download for Conestoga students) and not the online version.

 

 

1. DETERMINING BREAK-EVEN COMPONENTS AND GRAPHING ANALYSIS

Here, you will consider break-even components and the effect that changing the selling price has on the break-even point including how the graph changes.  The break-even point can be measured in three ways:  as a dollar amount BE[$], in units BE[units], and as units as a percentage of maximum capacity [% max cap].

Below your graph (on the same worksheet) answer the following questions. Use wrap-text or merge cells to make your answers easy to read. This video will help you wrap text How to Wrap Text in Microsoft Excel and this video will help you merge cells How to Merge Cells in Excel.

 

1. For the original fixed cost, calculate each of BE[$], BE[units], and % max cap.  Use appropriate cell references when referring to S, FC, VC, and maximum capacity (Table 1) in your calculation.  Make sure that the BE values that result from your calculations match your graph.

 

1. Describe what happens to the TC line when there is an increase in total fixed costs due to new purchases in the invoice.  If it is difficult to see this on your graph, zoom in on the TC lines.  Change the colours of the lines if it helps.

 

1. Describe what happens to the TC line when there is a subsequent reduction in total fixed costs due to supplier providing a discount.

 

1. Based on the graph, how does the BE[units] amount change as the fixed costs decrease?  How does the BE[$] change?

 

1. Confirm the changes to your graph by calculating the BE[units] and BE[$] for each of Question 2 and Question 3.

 

1. If TysonBooks continues to sell books at the same BE[units] quantity as originally (before any changes to FC), will TysonBooks be experiencing a loss or profit at the new break-even point (after the changes to FC)?

 

1. How would the graph and BE point change if the variable costs increased?

 

1. REFLECTION QUESTIONS (provide thoughtful responses):
2. Identify one challenge with content in this Case.
3. Identify one new thing you learned by doing this Case.

Your graphs(s), tables, and different parts of your Excel workbook where necessary as you reference them in your explanations.