Below is the result after inserting data. I am also providing excel formula for the same as well after the answer :
Assumptions | |
Product#1 | Throw-It |
Sales Price per Unit | 15.00 |
Variable Cost Per Unit | |
Procurement cost from China | 2.50 |
Shipping Cost | 6.00 |
Sales Commission | 1.50 |
Total Variable cost per unit | 10.00 |
Monthly volume | 600.00 |
Product#2 | Treat-it is |
Sales Price per Unit | 40.00 |
Variable Cost Per Unit | 24.00 |
Procurement cost from China | 11.00 |
Shipping Cost | 9.00 |
Sales Commission | 4.00 |
Total Variable cost per unit | 24.00 |
Monthly volume | 200.00 |
Fixed Cost per month | |
Trade Show Entry Fee | 800.00 |
Sales Staff Cost | 1,680.00 |
Total Fixed cost per Month | 2,480.00 |
Expected Change in Volume (%) | 6% |
Prdouct#1 | Throw-It |
Unit CM | 5.00 |
CM% | 33.33% |
Breakeven Point | |
in Units | 496.00 |
In Sales Revenue | 7,440.00 |
Target Profit Volume | |
in Units | 1,860.00 |
In Sales Revenue | 27,900.00 |
Prdouct#2 | Treat-it is |
Unit CM | 16.00 |
CM% | 40.00% |
Breakeven Point | |
in Units | 155.00 |
In Sales Revenue | 6200.00 |
Target Profit Volume | |
in Units | 581.25 |
In Sales Revenue | 23250.00 |
Consolidated Margin Income Statement | |||
for the month ending June 30 | |||
Product#1 | Product#2 | Total | |
Selling Price | 15.00 | 40.00 | 55.00 |
Contribution per unit | 5.00 | 16.00 | 21.00 |
Product Volume | 600.00 | 200.00 | 800.00 |
Total Revenue | 9,000.00 | 8,000.00 | 17,000.00 |
Total Contribution | 3,000.00 | 3,200.00 | 6,200.00 |
Fixed Cost | 2,480.00 | ||
Operating Profit | 3,720.00 | ||
WACM% (Total Contribution / Total Revenue) | 36.47% |
Calculation of Weighted average CM per unit | |||
Product#1 | Product#2 | Total | |
Contribution | 5.00 | 16.00 | 21.00 |
Product Volume | 600.00 | 200.00 | 800.00 |
Total Contribution | 3,000.00 | 3,200.00 | 6,200.00 |
WACM/Unit | 7.75 |
Calculation of Break Even Point | Product#1 | Product#2 | Total |
in Units | 240.00 | 80.00 | 320.00 |
In Sales Revenue | 3,600.00 | 3,200.00 | 6,800.00 |
Calculation to achieve Target profit | |||
Product#1 | Product#2 | Total | |
Number of Units required | 900.00 | 300.00 | 1,200.00 |
Sale revenue | 13,500.00 | 12,000.00 | 25,500.00 |
Margin of Safety in $ | 18,700.00 |
Margin of Safety % | 73.33% |
Degree of operating leverage | 1.67 |
Expected % change in operating income (%) | 10% |
Excel Formulas :
Assumptions | |
Prdouct#1 | Throw-It |
Sales Price per Unit | 15 |
Variable Cost Per Unit | |
Procurement cost from China | 2.5 |
Shipping Cost | 6 |
Sales Commission | =B47*10% |
Total Variable cost per unit | =SUM(B49:B51) |
Monthly volume | 600 |
Prdouct#2 | Treat-it is |
Sales Price per Unit | 40 |
Variable Cost Per Unit | =11+9+B57*10% |
Procurement cost from China | 11 |
Shipping Cost | 9 |
Sales Commission | =B57*10% |
Total Variable cost per unit | =SUM(B59:B61) |
Monthly volume | 200 |
Fixed Cost per month | |
Trade Show Entry Fee | 800 |
Sales Staff Cost | 1680 |
Total Fixed cost per Month | =SUM(B68:B70) |
Expected Change in Volume (%) | 0.06 |
The table starts from A45:B73
Prdouct#1 | Throw-It |
Unit CM | =B47-B52 |
CM% | =F47/B47 |
Breakeven Point | |
in Units | =B71/F47 |
In Sales Revenue | =F50*B47 |
Target Profit Volume | |
in Units | =(6820+B71)/F47 |
In Sales Revenue | =F54*B47 |
Prdouct#2 | Treat-it is |
Unit CM | =B57-B63 |
CM% | =F58/B57 |
Breakeven Point | |
in Units | =B71/F58 |
In Sales Revenue | =F61*B57 |
Target Profit Volume | |
in Units | =(6820+B71)/F58 |
In Sales Revenue | =F65*B57 |
The table starts from E46:F66
Consolidated Margin Income Statement | |||
for the month ending June 30 | |||
Product#1 | Product#2 | Total | |
Selling Price | =B47 | =B57 | =SUM(J47:K47) |
Contribution per unit | =F47 | =F58 | =SUM(J48:K48) |
Product Volume | =B53 | =B65 | =SUM(J49:K49) |
Total Revenue | =J47*J49 | =K47*K49 | =SUM(J50:K50) |
Total Contribution | =J48*J49 | =K48*K49 | =SUM(J51:K51) |
Fixed Cost | =B71 | ||
Operating Profit | =L51-L52 | ||
WACM% (Total Contribution / Total Revenue) | =L51/L50 |
The table starts from I44 :L55
Calculation of Weighted average CM per unit | |||
Product#1 | Product#2 | Total | |
Contribution | =F47 | =F58 | =SUM(J58:K58) |
Product Volume | =B53 | =B65 | =SUM(J59:K59) |
Total Contribution | =J58*J59 | =K58*K59 | =SUM(J60:K60) |
WACM/Unit | =L60/L59 |
Calculation of Break Even Point | Product#1 | Product#2 | Total |
in Units | =L66/L59*J59 | =L66/L59*K59 | =B71/L62 |
In Sales Revenue | =J66*B47 | =K66*B57 | =SUM(J67:K67) |
Calculation to achieve Target profit | |||
Product#1 | Product#2 | Total | |
Number of Units required | =L72/L59*J59 | =L72/L59*K59 | =(6820+B71)/L62 |
Sale revenue | =J72*B47 | =K72*B57 | =SUM(J73:K73) |
Margin of Safety in $ | =L73-L67 |
Margin of Safety % | =J79/L73 |
Degree of operating leverage | =L51/L53 |
Expected % change in operating income (%) | =J83*B73 |
PLEASE help! the paper are the instructions to fill in the boxes in the excel sheet...
CVP Modeling project Directions The purpose of this project is to give you experience creating a multiproduct profitability analysis that can be used to determine the effects of changing business conditions on the client's financial position. Your goal will be to use Excel in such a way that any changes to the assumptions will correctly ripple through the entire profitability analysis. You have been hired by Jake to build a CVP model that will help him understand the impact of...
please explain how to get the sales revenue at breakeven and the sales revenue at target profit (the gray boxes)! :) 131 Center $ . % Xv fx ) Conditional Format Forming as a St Launch Shipping and handling ---- 40.3951 94 11 1 523.4117647 se Wet Business & Directions Original Assumptions Advising client - on Ready Multiproduct Breakeven point: -in units Sales revenue at breakeven Product #1 53.78151261 Product #2 40.33613445 Total 94.11764706 + Product #1 Product #2 Multiproduct...
CVP Modeling project The purpose of this project is to give you experience creating a multiproduct profitability analysis that can be used to determine the effects of changing business conditions on the client's financial position. Your goal will be to use Excel in such a way that any changes to the assumptions will correctly ripple through the entire profitability analysis. If executed properly, the client should be able to use this spreadsheet over and over, using different "what if" assumptions....
I got the first 4 steps done. Just need to know what formulas from the data to use for steps 5,6, and 7. Step #1 ASSUMPTIONS Launch-it S10.00 Product #1: Sales price per unit Variable costs per unit: 1.00 2.00 1.00 4.00 Purchase Price Shipping and HandlingS Sales Comission Total variable cost per unit 200 Monthly volume Treat-Time $30.00 Product #2: Sales price per unit Variable costs per unit: 7.00 8.00 3.00 18.00 Purchase Price Shipping and Handling Sales Comission...
How is breakeven point determined for the answer to After taking business classes, Jake, an avid dog-lover, decided to start selling unique pet supplies at trade shows. He has two products: Product 1: "Launch-it"- a tennis ball thrower that will sell for $10. Product 2: "Treat-time"- an automatic treat dispenser that releases a treat when the dog places his paw on the pedal. The treat dispenser will sell for $30. Costs: Jake has hired an employee to work the trade...
Jake's Pet Supplies: Please see highlighted Table yellow portion: Need: Multiproduct Breakeven point: in units and sales revenue at break even and target profit point for Product 1, Product 2 and the totals Land it $6.00 60% Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 ASSUMPTIONS Product 31: Launch-it Sales price per unit $10.00 Variable costs per unit: Sales comission $1.00 Purchase price $1.00 Shipping and handling Total variable cost per unit $4.00...
Create a new What SUP Operating Cash Receipts Budget using the ch6-02 file to start your work, create a worksheet similar to the one created in this chapter to budget operating cash receipts by month for 3 months. Place assumption information in the cells provided, and define and use names extensively. The company expects to incur repair hours of 800, 900, and 950 in January, February, and March (respectively) at an expected hourly rate of $80. Product sales revenue of...
Hi, Please help!! I'm looking for an EXCEL SHEET answer shown with excel formulas. PLease show all of your work. Thank you so much. Question A company is trying to project if it needs to undertake a new project for replacement of a CRM system. The current operating cost to maintain the old CRM system is 850K growing at a rate of 10% year over year for the next 5 years. If a new System is built, the current Salaries...
Please help fill in the cells for steps 4, 5, and 6. Step 4 of 34 - Sales Assumptions D G B E F H 1 Spreadsheet ID 2 1234 Budget Assumptions 4 First Month's Sales in Units 5 Sales Growth Per Month 1 Your turn: Based on this paragraph enter these assumptions: Sales Price per Unit 6 Wally's Widgets is starting operations as of January 1. The company sells a single product and expects first month's sales to be...