how do i solve it in excel

always used the following method for determining job costs: (1) Direct material and direct labor are traced to each job based on timecards and materials requisition requests. (2) Manufacturing Overhead is applied based on total direct labor hours for the month. The selling price of each job is determined using a cost-plus pricing method. The final selling price is 115% of the cost determined on the job cost sheet. The introduction of the paper chain product line has been a great success. During the six months that the company has offered the paper chains, demand has exceeded all the initial projections, During September, PPF made and sold 80,000 paper chains, which is 50% higher than their initial projections. Shahira Osama is thrilled with her son's performance but has been less impressed with the Paper Hats Product Manager, Hashim Hakem Mrs Shahira remarked, "Hashim Hakim -- what is the problem with the sales for hats latelyYour sales have been down for the past five months. Maybe I should go out and hire another MBA like Ahmed. He sure knows how to bring in the sales and some fresh ideas. Hashim Hakem has been an excellent and loyal employee for over ten years. He has become frustrated with the decline in the sales of paper hats too. During September PPF made and sold only 160,000 hats, which is far below their planned sales of 300,000 hats. I just can't compete in the marketplace anymore. My unit costs keep rising, and corporate forced us to raise prices to our customers in order to cover our product costs and meet targeted selling price. My unit costs were always around $0.75 per unit and now they are over $0.80 per unit. My previously loyal customers have switched to other lower cost providers, I need to better understand the manufacturing process and talk to the cost accountants to see if they can help figure out why my costs keep rising." (a) Using the information below, you are required to complete the Job Cost sheet (provided in EXCEL) to show all the total product costs for Job A, which includes 15 units of paper hats and 10 units of chains. (5%) (b) Shahira Osama, the company CEO, has asked you to help in identifying some other method for allocating overhead in the determination of product cost. Identify at least one other possible way of allocating the overhead and hence RECALCULATE the product cost of the job "A" (using sheet 2 of the EXECL).(5% JOB A Costs for the Month of September Production and sales of Hats Production and sales of Chains 15 units 10 units Direct Material #Sheets of paper used to make HATS #Sheets of paper used to make CHAINS #stickers to make HATS #stickers to make CHAINS 1 per unit 1 4 8 per unit per unit per unit s $ S $ 0.05 per sheet 0.05 per sheet 0 01 cach 0.01 each Hats Direct Labor HOURS Cutting labor hours Assembly labor hours Finishing labor hours Total Direct Labor Hours (September) 2,500 Chains 1,500 3,000 1,500 6,000 Total hours 1,500 5,500 4,000 11,000 2.500 5.000 Cost per Hats Chains Total Direct Labor COSTS Cutting labor Assembly labor Finishing labor TOTAL DIRECT LABOR COSTS hour S 15.00 $ 15.00 S 15.00 Manufacturing Overhead (TOTALS) Quality control hours 320 Indirect labor - - Supervisor 360 Other indirect labor (stockroom, materials 960 handling) Depreciation on tape machines $1,000 hours hours hours $ s s 40.00 per hour 60.00 15.00 per hour per hour Depreciation on stapling machines $2,000 Depreciation on cutting machine $3,000 per month per month per month per month per month Rent and taxes on the factory S10,000 Utilities SI 500 Staples Tape S100 S150 Clipboard XII M O B D F G H Job Cost Sheet obortis Customer details: Date Description Reference Applied overhead Direct labor Direct materials Quantity Total Costunit Rate Hours Rate Basis Total Total Quantity 15 16 12 13 19 Total JO Cabo Det za always used the following method for determining job costs: (1) Direct material and direct labor are traced to each job based on timecards and materials requisition requests. (2) Manufacturing Overhead is applied based on total direct labor hours for the month. The selling price of each job is determined using a cost-plus pricing method. The final selling price is 115% of the cost determined on the job cost sheet. The introduction of the paper chain product line has been a great success. During the six months that the company has offered the paper chains, demand has exceeded all the initial projections, During September, PPF made and sold 80,000 paper chains, which is 50% higher than their initial projections. Shahira Osama is thrilled with her son's performance but has been less impressed with the Paper Hats Product Manager, Hashim Hakem Mrs Shahira remarked, "Hashim Hakim -- what is the problem with the sales for hats latelyYour sales have been down for the past five months. Maybe I should go out and hire another MBA like Ahmed. He sure knows how to bring in the sales and some fresh ideas. Hashim Hakem has been an excellent and loyal employee for over ten years. He has become frustrated with the decline in the sales of paper hats too. During September PPF made and sold only 160,000 hats, which is far below their planned sales of 300,000 hats. I just can't compete in the marketplace anymore. My unit costs keep rising, and corporate forced us to raise prices to our customers in order to cover our product costs and meet targeted selling price. My unit costs were always around $0.75 per unit and now they are over $0.80 per unit. My previously loyal customers have switched to other lower cost providers, I need to better understand the manufacturing process and talk to the cost accountants to see if they can help figure out why my costs keep rising." (a) Using the information below, you are required to complete the Job Cost sheet (provided in EXCEL) to show all the total product costs for Job A, which includes 15 units of paper hats and 10 units of chains. (5%) (b) Shahira Osama, the company CEO, has asked you to help in identifying some other method for allocating overhead in the determination of product cost. Identify at least one other possible way of allocating the overhead and hence RECALCULATE the product cost of the job "A" (using sheet 2 of the EXECL).(5% JOB A Costs for the Month of September Production and sales of Hats Production and sales of Chains 15 units 10 units Direct Material #Sheets of paper used to make HATS #Sheets of paper used to make CHAINS #stickers to make HATS #stickers to make CHAINS 1 per unit 1 4 8 per unit per unit per unit s $ S $ 0.05 per sheet 0.05 per sheet 0 01 cach 0.01 each Hats Direct Labor HOURS Cutting labor hours Assembly labor hours Finishing labor hours Total Direct Labor Hours (September) 2,500 Chains 1,500 3,000 1,500 6,000 Total hours 1,500 5,500 4,000 11,000 2.500 5.000 Cost per Hats Chains Total Direct Labor COSTS Cutting labor Assembly labor Finishing labor TOTAL DIRECT LABOR COSTS hour S 15.00 $ 15.00 S 15.00 Manufacturing Overhead (TOTALS) Quality control hours 320 Indirect labor - - Supervisor 360 Other indirect labor (stockroom, materials 960 handling) Depreciation on tape machines $1,000 hours hours hours $ s s 40.00 per hour 60.00 15.00 per hour per hour Depreciation on stapling machines $2,000 Depreciation on cutting machine $3,000 per month per month per month per month per month Rent and taxes on the factory S10,000 Utilities SI 500 Staples Tape S100 S150 Clipboard XII M O B D F G H Job Cost Sheet obortis Customer details: Date Description Reference Applied overhead Direct labor Direct materials Quantity Total Costunit Rate Hours Rate Basis Total Total Quantity 15 16 12 13 19 Total JO Cabo Det za