SPREADSHEET EXERCISE: PERFORMING BREAKEVEN ANALYSIS AND SENSITIVITY ANALYSIS
Not what you're looking for?
SPREADSHEET EXERCISE: PERFORMING BREAKEVEN ANALYSIS AND SENSITIVITY ANALYSIS
Selmore Collectible Toy Company (SCTC) makes toy sets consisting of collectible trucks, vans, and cars for the retail market. The firm is developing a new toy set that includes a battery-powered tractor-trailer, complete with cab and trailer, sports car, and motorcycle. Each set sells for $100. Table 1 shows the major components of SCTC's annual fixed costs for the toy set. Each component includes the cost of purchases, depreciation, and operating expenses. Table 2 shows the major components of SCTC's variable costs.
Prepare a spreadsheet to support the decision making needs of SCTC's managers. The spreadsheet should show the fixed costs, variable costs per unit, the contribution margin, and the breakeven point for this product. How many sets does SCTC have to sell before it can start turning a profit? Include a data table to show alternative breakeven points, assuming variations in insurance costs and labour costs. How would increasing the sale price to $125 affect the breakeven point?
Table 1 SCTC Fixed Costs
Category Amount
Land $ 42 500
Buildings 332 500
Manufacturing machinery 532 000
Office equipment 212 800
Utilities 30 500
Insurance $1 250 000
Total $1 250 000
Table 2 SCTC Variable Costs
Category Amount
Labour $15.00
Advertising 1.00
Shipping & receiving 5.00
Total $21.00
The data for the file is attached below
Purchase this Solution
Solution Summary
This is a discussion on how to prepare a spreadsheet to support the decision making needs of SCTC's managers. The spreadsheet will show the fixed costs, variable costs per unit, the contribution margin, and the breakeven point for this product and also how many sets does SCTC have to sell before it can start turning a profit.
Solution Preview
Good day and welcome to BrainMass.
In order to assist you with this problem, I'll need to work through much of the answer for you, but I'm not going to finalise the answer completely, so you'll need to do some work at the end. I'll also explain as I go along.
The first paragraph of the instructions outlines the process for you to follow.
Firstly, determine the contribution per unit by adding together all the variable costs and deducting them from the selling price of $100 per set. I have attached this calculation in the first tab of the spreadsheet. You'll see the total variable costs per unit are $21 (this was given). Therefore the contribution is $100 - $21 = $79.
Next determine the total fixed costs. This is also given as $1 250 000, but I have again listed ...
Purchase this Solution
Free BrainMass Quizzes
Six Sigma for Process Improvement
A high level understanding of Six Sigma and what it is all about. This just gives you a glimpse of Six Sigma which entails more in-depth knowledge of processes and techniques.
Marketing Management Philosophies Quiz
A test on how well a student understands the basic assumptions of marketers on buyers that will form a basis of their marketing strategies.
Lean your Process
This quiz will help you understand the basic concepts of Lean.
Understanding the Accounting Equation
These 10 questions help a new student of accounting to understand the basic premise of accounting and how it is applied to the business world.
Business Ethics Awareness Strategy
This quiz is designed to assess your current ability for determining the characteristics of ethical behavior. It is essential that leaders, managers, and employees are able to distinguish between positive and negative ethical behavior. The quicker you assess a person's ethical tendency, the awareness empowers you to develop a strategy on how to interact with them.