Business Finance
ACCT 202 CVP Analysis Spreadsheet

Acct 202

Question Description

I have attached excel spread sheet save it and solve it. it is for accounting 202 CVP analysis and save it as excel when you done.

Unformatted Attachment Preview

Accounting 202 PROJECT 2 - CVP ANALYSIS Name: Class time Enter the last 4 digits of your student ID number: (These digits will be used to for some variables in the project) Save this file in EXCEL FORMAT as: P2Lastname Highest = Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be Lowest other than 0= rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or .2075 Do not change rows or colums on this spreadsheet. Input areas are in yellow. You MUST show your work in the areas indicated to get credit for the problem. You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit. Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: Requirement 1: Replace the two unknown amounts (?) in A. and B. as directed. To find the unknown for B, you will have to complete Requirement 2. Then, complete the totals on the income statement. Baron Company Income Statement For the Year Ended December 31, 2018 Sales Cost of Goods Sold Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed) Gross Margin Operating Expenses Commissions (variable) Shipping (variable) Advertising (fixed) Billing (mixed) Sales and Administrative Salaries (fixed) $364,000 $70,200 39,000 64,000 173,200 190,800 29,120 90,000 A. Multiply the HIGHEST digit entered above above by 1,000 and enter here enter the result B. Complete Requirement 2 below and insert result here 70,000 Total Operating Expenses Net Operating Income (Loss) Selling price per unit is $14 and variable manufacturing overhead is $1 per unit. All variable expenses in the company vary in terms of units sold. There was no change in inventory levels between the beginning and end of the year. Baron's plant capacity is 40,000 units per year (relevant range). c. How many units of product did Baron sell in 2018? Requirement 2: Billing costs for the past 5 years, along with total units sold is presented in the table to the right: Year 2017 2016 2015 2014 2013 Sales in Units 23,580 23,900 21,000 23,000 20,850 Billing Costs $ 5,692 $ 5,717 $ 5,361 $ 5,293 $ 5,290 Use the high-low method to calculate the following: Show your work in this area: ANSWER: a. Variable billing cost per unit b. Total fixed billing costs c. Total cost equation for billing costs d. Determine total billing costs for 2018 based on the cost equation in 2)c. above and the total number of units sold in 2018. Insert your answer for 2)d. in the income statement above in the blank provided for Billing (Unknown B) Page 1 The company has been operating at a loss for several years. Management is trying to determine what it can do to make next year profitable. Requirement 3: Redo Baron's 2018 income statement in contribution format, showing both a total column and a per unit column in the space provided below. You MUST use formulas and cell refereces in the income statement to earn full credit. Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. Total Units Amount Per Unit Sales Variable Costs Direct materials Direct Labor Variable MOH Sales commissions Shipping Variable billing Total Variable Costs Contribution Margin Fixed Costs Fixed MOH Advertising Sales and admn. salaries Fixed billing Total Fixed Costs Net Operating Income (Loss) Hint: Divide total cost column by total units to get VC per unit. Hint: Operating Income (Loss) must equal the original income (loss) from above. Requirment 4: Calculate Baron's current breakeven point in both units and dollars. Show work in this area: Units: ANSWER Units Dollars: Dollars Requirement 5: Multiply the lowest digit (other than zero) from the last four digits of your student ID number by 10,000 and enter for C. below to get the reduction in advertising. a. The vice president suggests that selling price be lowered by 10% and advertising be reduced by She is confident that this action will increase sales volume by 25%. The new selling price price per unit would be: The new advertising amount would be: Page 2 C. b. Prepare a budgeted margin income statement, using the vice president's recommendation. Remember, when volume changes, total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new number of units. (Remember, VC per unit remains constant, but total VC changes proportionately with changes in volume. You MUST use formulas and cell refereces in the income statement to earn full credit. Total Units Sales Variable Costs Direct materials Direct Labor Variable MOH Sales commissions Shipping Variable billing Total Variable Costs Contribution Margin Fixed Costs Fixed MOH Advertising Sales and admn. salaries Fixed billing Total Fixed Costs Net Operating Income Amount Per Unit New from Req. 5 above Hint: Per unit Variable Costs are the same as the original income statement as VC per unit remains constant with changes in volume. However total variable costs will change 2 New from Req. 5 above Requirement 6: Using the budgeted income statement based on the vice president's proposal above in Req. 5.b., calculate the following: ANSWERS a. Breakeven in units: Show your work in the areas provided b. Margin of safety in units: c. Margin of safety in dollars of sales: d. Margin of safety percentage: Requirement 7: a. Calculate the operating leverage factor using the vice-president's proposed income statement above. Answer should use two decimal places. Show your work in this area: Answer b. If sales volume increases 12%, operating income will increase by: Percent Dollars Page 3 ...
Purchase answer to see full attachment

Final Answer

Hello, here is the work, Please indicate your name, class time and rename the file to complete. Thanks

Accounting 202
PROJECT 2 - CVP ANALYSIS
Name:

Class time

Enter the last 4 digits of your student ID number:

1179

1
1
7
(These digits will be used to for some variables in the project)
Save this file in EXCEL FORMAT as: P2Lastname
Highest =
Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be
Lowest other than 0=
rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or .2075
Do not change rows or colums on this spreadsheet. Input areas are in yellow.
You MUST show your work in the areas indicated to get credit for the problem.
You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit.

9
9
1

Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis:
Requirement 1: Replace the two unknown amounts (?) in A. and B. as directed. To find the unknown for B, you will have to
complete Requirement 2. Then, complete the totals on the income statement.
Baron Company
Income Statement
For the Year Ended December 31, 2018
Sales
Cost of Goods Sold
Direct Materials (variable)
Direct Labor (variable)
Manufacturing Overhead (mixed)
Gross Margin
Operating Expenses
Commissions (variable)
Shipping (variable)...

Tutortosh (2295)
Boston College

Anonymous
Solid work, thanks.

Anonymous
The tutor was great. I’m satisfied with the service.

Anonymous
Goes above and beyond expectations !

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors