Answered step by step
Verified Expert Solution
Question
1 Approved Answer
What are the excel formulas for finding the commission costs as well as filling out the Product Analysis boxes? Steps to complete the first part
What are the excel formulas for finding the commission costs as well as filling out the Product Analysis boxes?
Steps to complete the first part of this assignment: 1) Complete the assumptions area based on the data about Jake's business. Identify and list all variable costs separately and all fixed costs separately before finding the total for each type of cost. 2) Complete the Product Analysis area assuming Jake only sells either Product \#1 (Launch-its) OR Product \#2 (Treat times). Check figures: B/E Product \#1 = 199 units; B/E Product \#2= 55 units 3) Complete the pro forma CM Income Statement for the month of June. HINT: On product line income statements such as this, the fixed costs are only listed in the total column. Make sure you also show the totals for all other line items. Finally, calculate the overall WACM\% for the company. Check figure: Operating income =$3,485.50 WACM %=53% 4) Calculate the weighted average contribution margin (WACM) per unit. Check figure: WACM/unit =$13.96 5) Use the WACM/unit to calculate the TOTAL number of units needed to breakeven. THEN, calculate the number of EACH type of product needed to breakeven. (Note: You start by calculating the Total and then break it down to be per product.) Finally, calculate the sales revenue associated with this volume for EACH product, and then the sales revenue to breakeven in total. Check figures: B/ E Product #1=63;B/E Product #2=37 6) Use the WACM/unit to calculate the total number of units needed to achieve Sue's target profit. THEN, calculate the number of EACH type of product needed to achieve the target profit. (Note: You start by calculating the Total and then the per product.) Finally, calculate sales revenue associated with this volume for EACH product, and then the sales revenue in total. Check figures: B/E Product #1=413;B/E Product #2=244 7) Calculate the MOS using June sales as the expected sales. Calculate the MOS in terms of sales revenue and as a percentage. Also calculate the current operating leverage factor (round to the nearest 2 decimal places) and use it to determine the expected percentage change in operating income stemming from an expected change in sales volume. Check figures: MOS%=71.3%; Operating leverage factor =1.40 8) Change the name of worksheet to "Original Assumptions". 9) Make sure you have cleaned up your worksheet using the formatting conventions listed above. 10) Go to the "Advising client" worksheet and follow the directions found there. Steps to complete the first part of this assignment: 1) Complete the assumptions area based on the data about Jake's business. Identify and list all variable costs separately and all fixed costs separately before finding the total for each type of cost. 2) Complete the Product Analysis area assuming Jake only sells either Product \#1 (Launch-its) OR Product \#2 (Treat times). Check figures: B/E Product \#1 = 199 units; B/E Product \#2= 55 units 3) Complete the pro forma CM Income Statement for the month of June. HINT: On product line income statements such as this, the fixed costs are only listed in the total column. Make sure you also show the totals for all other line items. Finally, calculate the overall WACM\% for the company. Check figure: Operating income =$3,485.50 WACM %=53% 4) Calculate the weighted average contribution margin (WACM) per unit. Check figure: WACM/unit =$13.96 5) Use the WACM/unit to calculate the TOTAL number of units needed to breakeven. THEN, calculate the number of EACH type of product needed to breakeven. (Note: You start by calculating the Total and then break it down to be per product.) Finally, calculate the sales revenue associated with this volume for EACH product, and then the sales revenue to breakeven in total. Check figures: B/ E Product #1=63;B/E Product #2=37 6) Use the WACM/unit to calculate the total number of units needed to achieve Sue's target profit. THEN, calculate the number of EACH type of product needed to achieve the target profit. (Note: You start by calculating the Total and then the per product.) Finally, calculate sales revenue associated with this volume for EACH product, and then the sales revenue in total. Check figures: B/E Product #1=413;B/E Product #2=244 7) Calculate the MOS using June sales as the expected sales. Calculate the MOS in terms of sales revenue and as a percentage. Also calculate the current operating leverage factor (round to the nearest 2 decimal places) and use it to determine the expected percentage change in operating income stemming from an expected change in sales volume. Check figures: MOS%=71.3%; Operating leverage factor =1.40 8) Change the name of worksheet to "Original Assumptions". 9) Make sure you have cleaned up your worksheet using the formatting conventions listed above. 10) Go to the "Advising client" worksheet and follow the directions found thereStep 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