H 9196 AB | 1 Accounting 202 2 PROJECT 2 - CVP ANALYSIS (40 pts) 3 4 Name: Spencer Brooks Class time 5 6 Enter the last 4 digits of your student ID number: 7 (These digits will be used to for some variables in the project) 8 Save this file in EXCEL FORMAT as: P2Lastname Highest = 9 Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be Lowest other than 0= 10 rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or 2075 11 Do not change rows or colums on this spreadsheet. Input areas are in yellow. 12 You MUST show your work in the areas indicated to get credit for the problem. 13 You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit. 14 15 Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: 16 17 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. $375,000 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) $72,500 45,000 67,200 184,700 190,300 Gross Margin Operating Expenses Commissions (variable) Chinning lvariablol Proj 2 = 30,000 o non LA Multinku tho VIGUECT dinit ontoradabavo G H Year 2017 2016 C D E F 43 c. How many units of product did Baron sell in 2018? 44 45 46 Requirement 2: 47 Billing costs for the past 5 years, along with total units sold is presented 48 in the table to the right: 49 50 51 Use the high-low method to calculate the following: 52 Show your work in this area: 53 a. Variable billing cost per unit Sales in Units Billing Costs 23,420 $ 5,692 23,600 $ 5,717 22,100 $ 5,380 21,250 $ 5,294 21,600 $ 5,330 2015 2014 2013 ANSWER: 54 55 56 57 b. Total fixed billing costs 59 c. Total cost equation for billing costs 61 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) 65 66 The company has been operating at a loss for several years. Management is trying to determine what it can do to make next year 67 profitable. 68 69 Requirement 3: 70 Redo Baron's 2018 income statement in contribution format, showing both a total column and a per unit column in the space provided below. 71 You MUST use formulas and cell refereces in the income statement to earn full credit. 72 73 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. H 9196 AB | 1 Accounting 202 2 PROJECT 2 - CVP ANALYSIS (40 pts) 3 4 Name: Spencer Brooks Class time 5 6 Enter the last 4 digits of your student ID number: 7 (These digits will be used to for some variables in the project) 8 Save this file in EXCEL FORMAT as: P2Lastname Highest = 9 Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be Lowest other than 0= 10 rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or 2075 11 Do not change rows or colums on this spreadsheet. Input areas are in yellow. 12 You MUST show your work in the areas indicated to get credit for the problem. 13 You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit. 14 15 Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: 16 17 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. $375,000 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) $72,500 45,000 67,200 184,700 190,300 Gross Margin Operating Expenses Commissions (variable) Chinning lvariablol Proj 2 = 30,000 o non LA Multinku tho VIGUECT dinit ontoradabavo G H Year 2017 2016 C D E F 43 c. How many units of product did Baron sell in 2018? 44 45 46 Requirement 2: 47 Billing costs for the past 5 years, along with total units sold is presented 48 in the table to the right: 49 50 51 Use the high-low method to calculate the following: 52 Show your work in this area: 53 a. Variable billing cost per unit Sales in Units Billing Costs 23,420 $ 5,692 23,600 $ 5,717 22,100 $ 5,380 21,250 $ 5,294 21,600 $ 5,330 2015 2014 2013 ANSWER: 54 55 56 57 b. Total fixed billing costs 59 c. Total cost equation for billing costs 61 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) 65 66 The company has been operating at a loss for several years. Management is trying to determine what it can do to make next year 67 profitable. 68 69 Requirement 3: 70 Redo Baron's 2018 income statement in contribution format, showing both a total column and a per unit column in the space provided below. 71 You MUST use formulas and cell refereces in the income statement to earn full credit. 72 73 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing