Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Case 2 builds on Case 1 . In this case, you will practice Cost - Volume - Profit analysis using Excel. More specifically, you will
Case builds on Case In this case, you will practice CostVolumeProfit analysis using Excel. More specifically, you will be: determining and graphing the Total Revenue and Total Cost functions, algebraically determining changes in profitsloss as the fixed costs change due to new purchases, and discounts, graphically determining the effects on breakeven 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 from the invoice created in Case The Task The overall task for this case is to analyze the total revenue, total cost, and overall breakeven values as fixed cost change. The Process Task SETUP RAW DATA TABLES & CVP GRAPHS. Create an Excel worksheet and name it CVP Analysis. Create simple tables as shown The values in Table are based on the TysonBooks example in the Week module Now Its Your Turn: Fixed and Variable Costs topic and the purchases made in Case Table is used to create table of values for graphing the TR and TC functions. Use cell references of Table values when creating Table values. S VC FC and Maximum capacity values can be found in the Week module. Invoice Total Amount Due: This value is from your Case 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 FC Adjusted: add Invoice Adjusted Total Amount Due to the original Fixed Cost FC Table 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 replace Max capacity in the Units column with the value in Table Calculate Mid point by dividing Max capacity by two. Create 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 invoice to the original Fixed Cost. The Adjusted TC is calculated from the supplier giving TysonBooks an overall discount of on the Total Amount Due from the Case invoice. The discount is applied to the new purchases only and not the original fixed costs. Table Units TR TC New TC Adjusted TC Mid point Max capacity Create 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. Task DETERMINING BREAKEVEN COMPONENTS AND GRAPHING ANALYSIS Here, you will consider breakeven components and the effect that changing the selling price has on the breakeven point including how the graph changes. The breakeven point can be measured in three ways: as a dollar amount BE$ in units BEunits and as units as a percentage of maximum capacity max cap Below your graph on the same worksheet answer the following questions. Use wraptext 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. For the original fixed cost, calculate each of BE$ BEunits and max cap. Use appropriate cell references when referring to S FC VC and maximum capacity Table in your calculation. Make sure that the BE values that result from your calculations match your graph. 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. Describe what happens to the TC line when there is a subsequent reduction in total fixed costs due to supplier providing a discount. Based on the graph, how does the BEunits amount change as the fixed costs decrease? How does the BE$ change? Confirm the changes to your graph by calculating the BEunits and BE$ for each of Question and Question If TysonBooks continues to sell books at the same BEunits quantity as originally before any changes to FC will TysonBooks be exp
Case builds on Case
In this case, you will practice CostVolumeProfit analysis using Excel. More specifically, you will be:
determining and graphing the Total Revenue and Total Cost functions,
algebraically determining changes in profitsloss as the fixed costs change due to new purchases, and discounts,
graphically determining the effects on breakeven 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 from the invoice created in Case
The Task
The overall task for this case is to analyze the total revenue, total cost, and overall breakeven values as fixed cost change.
The Process
Task SETUP RAW DATA TABLES & CVP GRAPHS.
Create an Excel worksheet and name it CVP Analysis.
Create simple tables as shown The values in Table are based on the TysonBooks example in the Week module Now Its Your Turn: Fixed and Variable Costs topic and the purchases made in Case Table is used to create table of values for graphing the TR and TC functions. Use cell references of Table values when creating Table values.
S VC FC and Maximum capacity values can be found in the Week module.
Invoice Total Amount Due: This value is from your Case 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
FC Adjusted: add Invoice Adjusted Total Amount Due to the original Fixed Cost FC
Table
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 replace Max capacity in the Units column with the value in Table Calculate Mid point by dividing Max capacity by two. Create 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 invoice to the original Fixed Cost. The Adjusted TC is calculated from the supplier giving TysonBooks an overall discount of on the Total Amount Due from the Case invoice. The discount is applied to the new purchases only and not the original fixed costs.
Table
Units TR TC New TC Adjusted TC
Mid point
Max capacity
Create 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.
Task DETERMINING BREAKEVEN COMPONENTS AND GRAPHING ANALYSIS
Here, you will consider breakeven components and the effect that changing the selling price has on the breakeven point including how the graph changes. The breakeven point can be measured in three ways: as a dollar amount BE$ in units BEunits and as units as a percentage of maximum capacity max cap
Below your graph on the same worksheet answer the following questions. Use wraptext 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.
For the original fixed cost, calculate each of BE$ BEunits and max cap. Use appropriate cell references when referring to S FC VC and maximum capacity Table in your calculation. Make sure that the BE values that result from your calculations match your graph.
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.
Describe what happens to the TC line when there is a subsequent reduction in total fixed costs due to supplier providing a discount.
Based on the graph, how does the BEunits amount change as the fixed costs decrease? How does the BE$ change?
Confirm the changes to your graph by calculating the BEunits and BE$ for each of Question and Question
If TysonBooks continues to sell books at the same BEunits quantity as originally before any changes to FC will TysonBooks be exp
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started