90.2K

Verified Solution

Link Copied!

I need help on the blank boxes please

Cost-Volume-Profit: Break-Even Analysis Using Excel Completing this activity will help you learn to: 1. understand how costs and sales price interact to produce profit. 2. create a reusable spreadsheet. 3. create absolute Excel references that will not change when a formula is copied and pasted. 4. create a graph to visually see a break-even point. Case scenario: You are working for a jacket manufacturer. You are interested in how many jackets need to be produced and sold to make a profit. Two types of costs are relevant for production: 1. The fixed costs that your business will incur no matter the number of units produced, such as the manufacturing plant's rent expense and building security costs. 2. Variable costs that are incurred for each jacket produced, e.g., wool material. Assume that all produced jackets are sold. Sales price, variable cost, and fixed cost data are available within the Case Data Excel Spreadsheet. Create Excel calculations to determine the "break-even point." Then graph the units, profits, and cost to "see" the break-even point. Required: X Download spreadsheet BreakEven PointCaseData-27f52d.xlsx A B c D E F G I J K L 1 Job-Order Sheet 2 3 Customer Date Initiated ABC, Inc. 26 4 Job Number 5 Department 6 Date Completed Units Completed 4/1/20 4/30/20 17 1 7 8 Item # 9 Direct Labor per Unit Hours Amount 2 $45.00 2 $55.00 Direct Materials per Unit Utilized per Unit Amount 1 $40.00 10 $0.19 1 $75.00 52B 10 52D 11 60A Subtotal Employee # $40.00 123456 $1.90 234567 $75.00 $0.00 $0.00 $0.00 $116.90 Total Hours Job Manufacturing Overhead (MOH) Subtotal Hours DLH Rate Amount $90.00 $6.00 $110.00 $0.00 $0.00 $0.00 $0.00 $200.00 Total MOH $0.00 12 13 14 15 Total Materials 4 16 17 18 19 Cost Summary for Job #26 Direct Materials Direct Labor Manufacturing Overhead Total Cost 20 21 22 Unit Product Cost 23 You are to build a spreadsheet that can calculate break-even point. Break-even point is the number of units that a business has to sell to negate costs and begin producing a profit. The calculations will update when you change the costs (fixed or variable) or sales price. Hint: The break-even point cannot be calculated with one Excel function. Through this case you will learn how to accomplish a complex task by breaking the problem into smaller steps. This case will guide you through the steps. HINT: STEP-BY-STEP WALKTHROUGH To help you get started, the file already has jacket sales, cost data, and column titles for you to fill out. Complete the following steps: 1. Excel Formula Section: Perform the following calculations using the given data. a. In cell C7, calculate the contribution margin per jacket unit. b. In cell C8, calculate the break-even point in jacket units. 2. Data Table Section: Perform the following calculations using the given data. Hint: You need to use absolute references. Note: You must create this data table to be able to graph the break-even point. a. In cell C13, create a formula using the number of units in column B and costs (fixed and variable) to calculate the total cost for producing that many units. For example, in C13, what is the total costs for producing one jacket? Important: Although cost is often thought of as a negative number in accounting, keep your calculations in positive numbers. Copy and paste your formula into the remainder of column C. b. In cell D13, create a formula using the number of units in column B and the sales price to calculate the gross revenue for selling that many jackets. For example, in D13, what is the gross revenue for selling one jacket? Copy and paste your formula for the remainder of column D. 5. Calculate profits in column E. For example, in E13, what is the profit for producing one jacket? Use a minus sign to enter loss, if any. 6. Using the graph, explain how a break-even point changes in relation to cost and sell price. The input in the box below will not be graded, but may be reviewed and considered by your instructor. WALKTHROUGH: Cost Analysis of Job-Order Sheets 1. Naming Cells: Individual cells, cell ranges, and tables can be named within Excel for formula ease. For example, the Total Material Subtotal (cell E15) can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." Name cell G5 by selecting the cell and typing Units into the box at the top left of the toolbar: 2. Manufacturing Overhead: Review the manufacturing overhead rate in cell K9 of the job-order spreadsheet and observe how the manufacturing overhead is allocated for Job #26. (Hint: "DLH" stands for Direct Labor Hours). Manufacturing overhead is allocated based on the total direct labor hours. Therefore, $5 should be allocated to each direct labor hour the job utilized. a. Calculate the total hours for Job #26 in cell 39. Remember, your goal is to create a reusable spreadsheet. Total labor hours should NOT be hardcoded (or typed in), but a formula should be used to accommodate variations in total hours. Create a formula for total direct labor hours used in Job #26 by multiplying the subtotal of direct hours per unit (cell G15; 6) by the number of units in the job (cell G5; 20). b. Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26. Multiply the hours by the DLH rate to find the amount of manufacturing overhead. Is the manufacturing overhead amount you found for the JOB or for EACH UNIT? The manufacturing overhead amount is found for Job #26 as a whole, not per unit, because the rate is allocated based on the total labor for the job, not the labor for one unit. 3. Cost Summary a. Calculate total direct materials for Job #26 in cell G18. Hint: Is the direct material information given the total for the job or for each unit? You must multiply the direct materials used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $2,544.00. b. Calculate direct labor for Job #26 in cell G19. Hint: Is the direct labor information given the total for the job or for each unit? You must multiple the direct labor amount used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $5,700.00. c. Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code. The manufacturing overhead for Job #26 has already been calculated. Instead of typing the overhead amount ($600.00) in the cell, reference the cell (L15) in which the total is located. Note you do NOT need to multiple this rate by the 20 units because the amount is already a per job amount. The correct answer is $600.00. d. Calculate the total costs for Job #26 in cell G21. The total job costs can now be found for Job #26 by adding the direct labor, direct materials, and manufacturing overhead totals. The SUM formula is most efficient. The correct answer is $8,844.00. e. Calculate the unit product cost for Job #26 in cell G22. The unit product cost can be found by dividing the total cost (cell G21) by the number of units in Job #16 (cell G5; 20). The correct answer is $442.20. Cost-Volume-Profit: Break-Even Analysis Using Excel Completing this activity will help you learn to: 1. understand how costs and sales price interact to produce profit. 2. create a reusable spreadsheet. 3. create absolute Excel references that will not change when a formula is copied and pasted. 4. create a graph to visually see a break-even point. Case scenario: You are working for a jacket manufacturer. You are interested in how many jackets need to be produced and sold to make a profit. Two types of costs are relevant for production: 1. The fixed costs that your business will incur no matter the number of units produced, such as the manufacturing plant's rent expense and building security costs. 2. Variable costs that are incurred for each jacket produced, e.g., wool material. Assume that all produced jackets are sold. Sales price, variable cost, and fixed cost data are available within the Case Data Excel Spreadsheet. Create Excel calculations to determine the "break-even point." Then graph the units, profits, and cost to "see" the break-even point. Required: X Download spreadsheet BreakEven PointCaseData-27f52d.xlsx A B c D E F G I J K L 1 Job-Order Sheet 2 3 Customer Date Initiated ABC, Inc. 26 4 Job Number 5 Department 6 Date Completed Units Completed 4/1/20 4/30/20 17 1 7 8 Item # 9 Direct Labor per Unit Hours Amount 2 $45.00 2 $55.00 Direct Materials per Unit Utilized per Unit Amount 1 $40.00 10 $0.19 1 $75.00 52B 10 52D 11 60A Subtotal Employee # $40.00 123456 $1.90 234567 $75.00 $0.00 $0.00 $0.00 $116.90 Total Hours Job Manufacturing Overhead (MOH) Subtotal Hours DLH Rate Amount $90.00 $6.00 $110.00 $0.00 $0.00 $0.00 $0.00 $200.00 Total MOH $0.00 12 13 14 15 Total Materials 4 16 17 18 19 Cost Summary for Job #26 Direct Materials Direct Labor Manufacturing Overhead Total Cost 20 21 22 Unit Product Cost 23 You are to build a spreadsheet that can calculate break-even point. Break-even point is the number of units that a business has to sell to negate costs and begin producing a profit. The calculations will update when you change the costs (fixed or variable) or sales price. Hint: The break-even point cannot be calculated with one Excel function. Through this case you will learn how to accomplish a complex task by breaking the problem into smaller steps. This case will guide you through the steps. HINT: STEP-BY-STEP WALKTHROUGH To help you get started, the file already has jacket sales, cost data, and column titles for you to fill out. Complete the following steps: 1. Excel Formula Section: Perform the following calculations using the given data. a. In cell C7, calculate the contribution margin per jacket unit. b. In cell C8, calculate the break-even point in jacket units. 2. Data Table Section: Perform the following calculations using the given data. Hint: You need to use absolute references. Note: You must create this data table to be able to graph the break-even point. a. In cell C13, create a formula using the number of units in column B and costs (fixed and variable) to calculate the total cost for producing that many units. For example, in C13, what is the total costs for producing one jacket? Important: Although cost is often thought of as a negative number in accounting, keep your calculations in positive numbers. Copy and paste your formula into the remainder of column C. b. In cell D13, create a formula using the number of units in column B and the sales price to calculate the gross revenue for selling that many jackets. For example, in D13, what is the gross revenue for selling one jacket? Copy and paste your formula for the remainder of column D. 5. Calculate profits in column E. For example, in E13, what is the profit for producing one jacket? Use a minus sign to enter loss, if any. 6. Using the graph, explain how a break-even point changes in relation to cost and sell price. The input in the box below will not be graded, but may be reviewed and considered by your instructor. WALKTHROUGH: Cost Analysis of Job-Order Sheets 1. Naming Cells: Individual cells, cell ranges, and tables can be named within Excel for formula ease. For example, the Total Material Subtotal (cell E15) can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." Name cell G5 by selecting the cell and typing Units into the box at the top left of the toolbar: 2. Manufacturing Overhead: Review the manufacturing overhead rate in cell K9 of the job-order spreadsheet and observe how the manufacturing overhead is allocated for Job #26. (Hint: "DLH" stands for Direct Labor Hours). Manufacturing overhead is allocated based on the total direct labor hours. Therefore, $5 should be allocated to each direct labor hour the job utilized. a. Calculate the total hours for Job #26 in cell 39. Remember, your goal is to create a reusable spreadsheet. Total labor hours should NOT be hardcoded (or typed in), but a formula should be used to accommodate variations in total hours. Create a formula for total direct labor hours used in Job #26 by multiplying the subtotal of direct hours per unit (cell G15; 6) by the number of units in the job (cell G5; 20). b. Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26. Multiply the hours by the DLH rate to find the amount of manufacturing overhead. Is the manufacturing overhead amount you found for the JOB or for EACH UNIT? The manufacturing overhead amount is found for Job #26 as a whole, not per unit, because the rate is allocated based on the total labor for the job, not the labor for one unit. 3. Cost Summary a. Calculate total direct materials for Job #26 in cell G18. Hint: Is the direct material information given the total for the job or for each unit? You must multiply the direct materials used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $2,544.00. b. Calculate direct labor for Job #26 in cell G19. Hint: Is the direct labor information given the total for the job or for each unit? You must multiple the direct labor amount used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $5,700.00. c. Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code. The manufacturing overhead for Job #26 has already been calculated. Instead of typing the overhead amount ($600.00) in the cell, reference the cell (L15) in which the total is located. Note you do NOT need to multiple this rate by the 20 units because the amount is already a per job amount. The correct answer is $600.00. d. Calculate the total costs for Job #26 in cell G21. The total job costs can now be found for Job #26 by adding the direct labor, direct materials, and manufacturing overhead totals. The SUM formula is most efficient. The correct answer is $8,844.00. e. Calculate the unit product cost for Job #26 in cell G22. The unit product cost can be found by dividing the total cost (cell G21) by the number of units in Job #16 (cell G5; 20). The correct answer is $442.20

Answer & Explanation Solved by verified expert

See Answer