Concept explainers
This Excel worksheet relates to the Dickson Company example that is summarized in Exhibit 2-5. Download the workbook containing this form from Connect, where you will also find instructions about how to use this worksheet form.
You should proceed to the requirements below only after completing your worksheet.
Required:
2. Change the total fixed
Selling price: The cost incurred by selling the product in the market is known as the selling price.
Determine the missing figures in the sheet. After finding the missing figures determine the selling price of Job 408 by replacing the current data with the data given in the question.
Answer to Problem 2AE
Solution: The selling price of Job 408 using current data is $4348.75 and the selling price of Job 408 after replacing the current data with the given data is $2800.
Explanation of Solution
This calculation is done in the workbook and it is explained below,
Part -1- The missing figures in the sheet is determined here.
Chapter 2: Applying Excel | |||
Data | |||
Mark-up on job cost | 75% | ||
Department | |||
Milling | Assembly | ||
1 | Machine hours | 60000 | 3000 |
2 | Direct Labour Hours | 8000 | 80000 |
3 | Total fixed manufacturing overhead cost | $3,90,000.00 | $5,00,000.00 |
4 | Variable manufacturing overhead per machine hour | $2.00 | $ - |
5 | Variable manufacturing overhead per direct labour hour | $ - | $ 3.75 |
Cost Summary for job 407 | Department | ||
Milling | Assembly | ||
6 | Machine hours | 90 | 4 |
7 | Direct Labour Hours | 5 | 20 |
8 | Direct Materials | $800.00 | $370.00 |
9 | Direct Labour cost | $70.00 | $280.00 |
Enter a formula into each of the cells marked with? below | |||
Step 1: Calculate the estimated total manufacturing overhead cost for each department | |||
Milling | Assembly | ||
10 | Total fixed manufacturing overhead (given) | $3,90,000.00 | $5,00,000.00 |
11 | Variable manufacturing overhead per machine hour or direct labour hour (given) | $2.00 | $3.75 |
12 | Total machine hours or direct labour hours (given) | 60000 | 80000 |
13 | Total Variable manufacturing overhead (11 x 12) | $1,20,000.00 | $3,00,000.00 |
14 | Total manufacturing overhead (10 + 13) | $5,10,000.00 | $8,00,000.00 |
Step 2: Calculate the pre-determined overhead rate in each department | |||
Milling | Assembly | ||
15 | Total manufacturing overhead (14) | $5,10,000.00 | $8,00,000.00 |
16 | Total machine hours or direct labour hours (given) | 60000 | 80000 |
17 | Pre-determined overhead rate per machine hour or direct labour hour (15 divided by 16) | $8.50 | $10.00 |
Step 3: Calculate the amount of overhead applied to both departments to Job 407 | |||
Milling | Assembly | ||
18 | Pre-determined overhead rate per machine hour or direct labour hour (17) | $8.50 | $10.00 |
19 | Machine hours or direct labour hours for the job (given) | 90 | 20 |
20 | Manufacturing overhead applied (18 x 19) | $765.00 | $200.00 |
Step 4: Calculate the total job cost for Job 407 | |||
Milling | Assembly | ||
21 | Direct Materials (given) | $800.00 | $370.00 |
22 | Direct Labour cost (given) | $70.00 | $280.00 |
23 | Manufacturing overhead applied (20) | $765.00 | $200.00 |
24 | Total cost of Job 407 (21 + 22 + 23) | $1,635.00 | $850.00 |
Total cost of Job 407 (Milling + Assembly) | $2,485.00 | ||
Step 5: Calculate the selling price for Job 407 | |||
Milling | Assembly | ||
25 | Total cost of Job 407 ( 24) | $1,635.00 | $850.00 |
26 | Mark-up (24 x 75%) | $1,226.25 | $637.50 |
27 | Selling price of Job 407 (25 + 26) | $2,861.25 | $1,487.50 |
Total Selling Price (Milling + Assembly) | $4,348.75 |
Part 2 - The selling price of Job 408 by replacing the current data with data given
Chapter 2: Applying Excel | |||
Data | |||
Mark-up on job cost | 75% | ||
Department | |||
Milling | Assembly | ||
1 | Machine hours | 60000 | 3000 |
2 | Direct Labour Hours | 8000 | 80000 |
3 | Total fixed manufacturing overhead cost | $3,90,000.00 | $5,00,000.00 |
4 | Variable manufacturing overhead per machine hour | $2.00 | $ - |
5 | Variable manufacturing overhead per direct labour hour | $ - | $3.75 |
Cost Summary for job 408 | Department | ||
Milling | Assembly | ||
6 | Machine hours | 40 | 10 |
7 | Direct Labour Hours | 2 | 6 |
8 | Direct Materials | $700.00 | $360.00 |
9 | Direct Labour cost | $50.00 | $150.00 |
Enter a formula into each of the cells marked with? Below | |||
Step 1: Calculate the estimated total manufacturing overhead cost for each department | |||
Milling | Assembly | ||
10 | Total fixed manufacturing overhead (given) | $3,00,000.00 | $5,00,000.00 |
11 | Variable manufacturing overhead per machine hour or direct labour hour (given) | $2.00 | $3.75 |
12 | Total machine hours or direct labour hours (given) | 60000 | 80000 |
13 | Total Variable manufacturing overhead (11 x 12) | $1,20,000.00 | $3,00,000.00 |
14 | Total manufacturing overhead (10 + 13) | $4,20,000.00 | $8,00,000.00 |
Step 2: Calculate the pre-determined overhead rate in each department | |||
Milling | Assembly | ||
15 | Total manufacturing overhead (14) | $4,20,000.00 | $8,00,000.00 |
16 | Total machine hours or direct labour hours (given) | 60000 | 80000 |
17 | Pre-determined overhead rate per machine hour or direct labour hour (15 divided by 16) | $7.00 | $10.00 |
Step 3: Calculate the amount of overhead applied to both departments to Job 408 | |||
Milling | Assembly | ||
18 | Pre-determined overhead rate per machine hour or direct labour hour (17) | $7.00 | $10.00 |
19 | Machine hours or direct labour hours for the job (given) | 40 | 6 |
20 | Manufacturing overhead applied (18 x 19) | $280.00 | $60.00 |
Step 4: Calculate the total job cost for Job 408 | |||
Milling | Assembly | ||
21 | Direct Materials (given) | $700.00 | $360.00 |
22 | Direct Labour cost (given) | $50.00 | $150.00 |
23 | Manufacturing overhead applied (20) | $280.00 | $60.00 |
24 | Total cost of Job 407 (21 + 22 + 23) | $1,030.00 | $570.00 |
Total cost of Job 407 (Milling + Assembly) | $1,600.00 | ||
Step 5: Calculate the selling price for Job 408 | |||
Milling | Assembly | ||
25 | Total cost of Job 407 ( 24) | $1,030.00 | $570.00 |
26 | Mark-up (24 x 75%) | $772.50 | $427.50 |
27 | Selling price of Job 407 (25 + 26 ) | $1,802.50 | $997.50 |
28 | Total Selling Price of Job 408 (Milling + Assembly) | $2,800.00 |
Thus, the selling price of Job 408 after replacing the current data with the given data is $2800.
Want to see more full solutions like this?
Chapter 2 Solutions
Introduction To Managerial Accounting
- UPVOTE WILL BE GIVEN! INPUT THE COMPLETE SOLUTIONS. YOU MAY USE EXCEL. BOX THE FINAL ANSWERS. a. Calculate the Equivalent Units of Production - Conversion Cost. = b. Calculate for the total material cost per unit =arrow_forwardThis section has a nine-part comprehensive problem with multiple questions to address. Download the Chapter 9 Comprehensive Problem Template below to complete all parts. You will need your Bergevin and MacQueen book for reference. Redlands Inc. reported standard and actual costs for the product that it manufactures: Item Direct material price Direct materials quantity Direct labor price Direct labor quantity Factory overhead cost Machine hours per unit Number of finished products made Number of finished products sold Sales price per unit Standard $3 per lb. 2 lbs. $5 3 hours $2 per machine hour 2 machine hours 10 10 $40 Actual $2 per lb. 4 lbs. $7 2 hours 3 machine hours 12 11 $40arrow_forwardUse the data below to create Pivot Table(s) in Excel. You will use the pivot table(s) to fill out your answers in the requirements (worksheets) in Part 2. Submit your Excel File containing the Pivot Tables in Part 3. Decker Screw Manufacturing Company produces special screws made to customer specifications. During June, the following data pertained to these costs: Summary of Direct Materials Requisitions Department Job Requisition Number Cost per Number Number Quantity Unit 1 2906 B9766 4,950 $ 1.30 2 2907 B9767 190 22.00 1 2908 B9768 1,160 9.00 1 2906 B9769 4,830 1.31 2 2908 B9770 31 48.00 Summary of Direct Labor Time Tickets Department Job Ticket Cost per Number Number Number Hours Unit 1 2906 1056-1168 1,118 $ 6.50 2 2907 2121-2130 152 9.00 1 2908 1169-1189 167 6.50 2 2908 2131-1239 48 9.00 1 2906 1190-1239 826 6.50 Department Number 2 Summary of Factory Overhead Application Rates Basis of Application Rates $3 per direct labor hour 150% of direct labor cost Decker had no beginning…arrow_forward
- please answer within the format by providing formula the detailed workingPlease provide answer in text (Without image)Please provide answer in text (Without image)Please provide answer in text (Without image)arrow_forwardHazelnut Corporation manufactures lawn ornaments. It currently has two product lines, the basic and the luxury. Hazelnut has a total of $143,484 in overhead. The company has identified the following information about its overhead activity pools and the two product lines: Quantity or Amount Consumed by Basic Activity Pools Materials handling Quality control Machine maintenance Cost Driver Number of moves Number of inspections Number of machine hours Required 1 Complete this question by entering your answers in the tabs below. Basic Model Luxury Model Required 2 Required 3 Required 4 Required: 1. Suppose Hazelnut used a traditional costing system with machine hours as the cost driver. Determine the amount of overhead assigned to each product line. 2. Calculate the activity rates for each activity pool in Hazelnut's ABC system. 3. Calculate the amount of overhead that Hazelnut will assign to the basic line if it uses an ABC system. 4. Determine the amount of overhead Hazelnut will assign…arrow_forwardPlease help here with formulas that I need to use and all the calculations to get to these answers in Excel. Thank youarrow_forward
- Please show your computations using good accounting form through excel. Thank you! Problem: By products In Department III of SAMCIS Company, a portion of the materials (a by-product) is removed further processed and sold. The Company uses the reversal cost method to account for the by-product. Data for June include: Amount of by-product removed is 2,000 units; Estimated sales price of by-product after processing further is P1.20/unit. Estimated processing cost after separation is P0.30 per unit and estimated selling expenses is 10% of the sales price. The estimated profit margin is 5% of the sales price. What is the gain (loss) on sale of the by-product if all of the units are sold at P1.50?arrow_forwardPlease show your work.arrow_forwardHello, Please fill the table with answers. PFA screenshot for the table and question with values.arrow_forward
- UPVOTE WILL BE GIVEN! INPUT THE COMPLETE SOLUTIONS. YOU MAY USE EXCEL. BOX THE FINAL ANSWERS. e. How much is the total cost for Work in Process, Ending Inventory?arrow_forwardDisplay Design Incorporated (DDI) is a new company that creates display cases for various types of collectibles. DDI has hired you as an accountant to help measure and evaluate costs. Your first day on the job, they provide you with the following interactive dashboard concerning the prior month's costs.arrow_forwardUsing the direct method, compute for the factory overhead rate of the D department if it apply overhead based on direct labor hours and budgeted direct labor hours for the period total to 75,000 hours. (SOLUTIONS MUST BE IN GOOD ACCOUNTING FORM. EXCEL FORM WOULD BE GREAT. ACCOUNT TITLES ON THE LEFT, AMOUNTS ON THE RIGHT. USE TABLE IF NECESSARY. AVOID PARENTHETICAL SOLUTIONS. THANKS, A LOT!)arrow_forward
- Excel Applications for Accounting PrinciplesAccountingISBN:9781111581565Author:Gaylord N. SmithPublisher:Cengage LearningPrinciples of Accounting Volume 2AccountingISBN:9781947172609Author:OpenStaxPublisher:OpenStax College