Please solve the following problem using Excel commands

User Generated

cnqneb1975

Business Finance

Description

Please solve the following problem using the excel spreadsheet command. Also see the attached documents for instructions and example of typical problem solved.

SkiCo, Inc. manufacturers ski boots. The Company's projected income for coming year, based on sales of 160,000 units, is as follows:

sales.............................................................$16,000,000

Operating expenses:

Variables expenses..............$4,000,000

Fixed expenses....................$6,000,000

Total expenses.............................................$10,000,000

Net income.....................................................$6,000,000

Required: in completing the following requirements, ignore income taxes.

question 1: Prepare a CVP graph for SkiCo, Inc., for the coming year

question 2: Calculate the firm,s break even point for the year in sales dollars

Question 3: what is the company's margin of safety for the year?Step by step guidelines.pdf example of typical problem solved.pdf 

Unformatted Attachment Preview

Ginn Tips on Activity 5.3 Dr. Ginn’s Tips for Doing Problem 7-42 1, 3 A. Please check the data file in the questions and answers discussion forum. Make sure you are using the right data to solve the problem. B. Consider the fundamental concepts you are expected to demonstrate. Problem 7-42 is on cost volume profit analysis. It is about solving for the breakeven point. Breakeven charts graphically represent operating leverage. The higher the fixed costs are, the higher the operating leverage. Firms with high operating leverage show a steeper decline in losses as the volume drops below the breakeven point and steeper increase in profits after the volume passes the breakeven point. C. Find a model in the chapter that provides an example of breakeven analysis. There are two kinds of breakeven charts. I prefer the ones that have a line for fixed costs, variable costs, total costs, and total revenue. These illustrate the concept of operating leverage. D. Lay out the problem. Your task is to look at all the information given in the text and transfer it for analysis. E. Use Excel. Graphical Solution. To solve the problem graphically, first open an Excel file and create a worksheet labeled “Data”. Next, transcribe the information given in the text to the Data worksheet. It is always good practice to develop worksheets that are interactive, so create another worksheet and copy and paste all the information from the data worksheet onto the “Interactive” worksheet. Next, identify the cells that are a function of changes in volume, color them for easy reference, and construct formulae in the necessary cells to make the worksheet interactive for purposes of pro forma planning. Sales will be a function of volume multiplied by price, so create a volume cell and a price cell. The sales cell will be calculated by a formula in the cell that refers to volume and price. Similarly, for calculation of variable costs, use the same volume cell but create a variable cost per unit cell. Variable costs will be calculated by a formula in the cell that refers to cost per unit and volume. This interactive worksheet will help clarify the cells that change due to volume. For the graphical solution, you need the data to make a graph or chart. Open a new worksheet and label it “Graphs.” Construct a row that gives you the data you will need to make a graph. You will need lines for volume, net income, sales revenue, variable expenses, fixed expenses, and total expenses. You can generate this data fairly easily by establishing a column of volumes. After you have entered two or three cells to establish the pattern of numbers, you can extend the column using fill handle. If the first row is constructed using an algebraic formula to determine cell values. You can extend all the values from the first row downward using fill handle. Note that fixed costs are a constant and do not change. However, you need to generate a column of fixed expenses so that you can graph it. Select the relevant columns for your chart or charts. Go to the insert tab on the menu and choose the desired chart option. Note that there are two formats for CVP charts, so make a profit volume graph on one worksheet tab and do an alternate format graph with variable expenses, fixed expenses, total expenses, and sales revenue on another worksheet tab. Algebraic Solution. To check the accuracy of your graph. Take the formula from the first row and calculate the breakeven using “goal seek.” Go to Data/Data Tools/What If Analysis/Goal Seek. Set the Net Income to the desired level and see what volume is required. The goal is net income and volume is what you change. Compare the breakeven point derived from the Goal Seek to the breakeven points in each of your charts. If the all come out the same, then you can be confident that you know the breakeven point. F. Complete the analysis even if you have doubts. G. Take a break and allow your brain to synthesize. H. Note that the text provides a check figure of $8,000,000 for the breakeven point. Review and amend your work until you get these results. To solve any accounting problems I recommend that you first lay out the data given. Kelly Kettle Inc. Sales Volume Sales Operating Expenses Variable Expenses Fixed Expenses Total Expenses Net Income revenue per unit variable cost per unit contribution margin per unit breakeven point in units safety margin in units safety margin in dollars $ $ $ $ $ $ $ 160,000 16,000,000 $ $ 14,400,000 1,600,000 6,400,000 8,000,000 100 40 60 133333.3333 26,667 2,666,667 See how I coded the colored cells to accept changes in volume. When the data are created with formulae, you can enter changes in volume and the results will change. You can do a sensitivity analysis by subjectively determining an expected volume, a high volume, and a low volume You can do a very sophisticated analysis by combining Monte Carlo simulation with Excel. http://office.microsoft.com/en-us/excel-help/introduction-to-monte-carlo-simulation-HA010282777.aspx Kelly Kettle Inc. Sales Volume Sales Operating Expenses Variable Expenses Fixed Expenses Total Expenses Net Income revenue per unit variable cost per unit contribution margin per unit contribution margin percentage breakeven point in units safety margin in units safety margin in dollars 160,000 $ 16,000,000 $ 6,400,000 $ 8,000,000 $ 14,400,000 $ 1,600,000 $ $ $ 100 40 60 60% 133,333 26,667 $ 2,666,667 Kelly Kettle Inc. Sales Volume Sales Operating Expenses Variable Expenses Fixed Expenses Total Expenses Net Income revenue per unit variable cost per unit contribution margin per unit contribution margin percentage breakeven point in units safety margin in units safety margin in dollars 160,000 $ 16,000,000 $ $ 6,400,000 8,000,000 $ 14,400,000 $ 1,600,000 $ $ $ $ 100 40 60 60% 133,333 26,667 2,666,667 Put the data in a formula. Extend down at different volumes to have data to graph. Sales Volume Net Income Variable Expenses Fixed Expenses 0 10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 110000 120000 130000 140000 150000 160000 170000 180000 190000 200000 210000 220000 230000 240000 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ (8,000,000) (7,400,000) (6,800,000) (6,200,000) (5,600,000) (5,000,000) (4,400,000) (3,800,000) (3,200,000) (2,600,000) (2,000,000) (1,400,000) (800,000) (200,000) 400,000 1,000,000 1,600,000 2,200,000 2,800,000 3,400,000 4,000,000 4,600,000 5,200,000 5,800,000 6,400,000 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 400,000 800,000 1,200,000 1,600,000 2,000,000 2,400,000 2,800,000 3,200,000 3,600,000 4,000,000 4,400,000 4,800,000 5,200,000 5,600,000 6,000,000 6,400,000 6,800,000 7,200,000 7,600,000 8,000,000 8,400,000 8,800,000 9,200,000 9,600,000 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 Sales Revenue $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1,000,000 2,000,000 3,000,000 4,000,000 5,000,000 6,000,000 7,000,000 8,000,000 9,000,000 10,000,000 11,000,000 12,000,000 13,000,000 14,000,000 15,000,000 16,000,000 17,000,000 18,000,000 19,000,000 20,000,000 21,000,000 22,000,000 23,000,000 24,000,000 Total Expenses $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 8,000,000 8,400,000 8,800,000 9,200,000 9,600,000 10,000,000 10,400,000 10,800,000 11,200,000 11,600,000 12,000,000 12,400,000 12,800,000 13,200,000 13,600,000 14,000,000 14,400,000 14,800,000 15,200,000 15,600,000 16,000,000 16,400,000 16,800,000 17,200,000 17,600,000 Kelly Kettle Inc. You can solve by setting up the algebraic formula and using Goal Seek Go to Data/Data Tools/What If Analysis/Goal Seek. Set the Net Income to the desired level and see what volume is required. The goal is net income and volume is what you change. Net Income Kelly Kettle Inc. Sales Volume Sales Operating Expenses Variable Expenses Fixed Expenses Total Expenses Net Income revenue per unit variable cost per unit contribution margin per unit contribution margin percentage breakeven point in units safety margin in units safety margin in dollars $ $ $ $ $ $ $ 160,000 16,000,000 $ $ 14,400,000 1,600,000 6,400,000 8,000,000 100 40 60 60% 133,333 26,667 2,666,667 Goal Seek Sales Price $ $ Sales Volume 100.00 100.00 Sales Revenue Variable Expenses Variable Cost Per Unit Fixed Expenses Total Expenses Net income 0 $ $ $ 40 $ 8,000,000 $ 8,000,000 $ (8,000,000) 133333.3333 $ 13,333,333 $ 5,333,333 $ 40 $ 8,000,000 $ 13,333,333 $ - $30,000,000 $25,000,000 $20,000,000 Variable Expenses Fixed Expenses $15,000,000 Sales Revenue $10,000,000 Total Expenses $5,000,000 $1 2 3 4 5 6 7 8 9 10111213141516171819202122232425 8000000 6000000 4000000 2000000 0 -2000000 -4000000 -6000000 -8000000 -10000000 Sales Volume 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Net Income
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Super useful! Studypool never disappoints.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags