Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Help to work out these instructions and what formulas to use in excel would be greatly appreciated Information Hourly Wage Rates: Department Engineering Programming Assembly

image text in transcribedHelp to work out these instructions and what formulas to use in excel would be greatly appreciated image text in transcribedimage text in transcribed

Information Hourly Wage Rates: Department Engineering Programming Assembly Rate Hour $20.00 $21.00 $32.00 Manufacturing overhead is applied to jobs using a pre-determined factory-wide rate per direct labour hour based on the following annual budgets: Budgeted manufacturing overhead Budgeted direct labour hours $68,500.00 $7,600.00 During September Job 4971 was started and completed Job 4971 was invoiced on 18 September for the quoted price of: $37,850.00 Journal Accounts Accounts Payable Accounts Receivable Manufacturing Overhead Cost of Goods Sold Finished Goods Inventory Materials Inventory Sales Wages Payable Work in Process Inventory Question Instruction Marks Done Section A 12 A1 A2 A3 A4 The following are to be completed in the Completed Jobs sheet: This sheet contains a list of completed jobs for the year. Column B containing the Manger information has been hidden, unhide column B. In E4 use a formula to calculate the number of days between date started and date completed. For example, if the job started on the 4th and finished on the 24th of January, the days taken would be 20. Copy the formula down to E24. Change the formatting of Days Taken values to be General (number with no decimals). In 14:124, use the Man Hours provided along with the information on overheads in the Information sheet, to calculate the manufacturing overhead for each job In 34:J24 calculate the total costs for each job. Widen column J so that all the values are clearly visible. In L4:L24 calculate the profit margin ($) for each job. In M4:M24 calculate the Gross Profit Margin for each job. (Note: in some cases there may be a loss which will give a negative answer.) Format the Gross Profit Margin figures to show as a percentage. Convert the data in A3:M24 to a Table. Name the table Jobs. (NB name the table, not the range). 00 00 00 00 A5 A6 AZ A8 A9 A10 Overhead Total Cost $ Margin GP Percent Man Hours 135 704 44 480 480 258 Date Started 2/1/19 25/1/19 26/1/19 26/1/19 11/2/19 16/2/19 20/2/19 22/2/19 6/3/19 19/3/19 2/4/19 15/4/19 17/4/19 27/4/19 12/5/19 5/6/19 7/6/19 13/6/19 26/7/19 1/8/19 3/8/19 Date Completed Days Taken 15/1/19 10/3/19 25/2/19 25/2/19 6/3/19 6/4/19 26/2/19 9/4/19 23/3/19 5/4/19 13/5/19 28/4/19 9/5/19 13/5/19 2/6/19 21/6/19 14/7/19 27/6/19 13/9/19 15/8/19 21/8/19 706 82 442 177 272 590 114 264 269 269 166 326 235 666 258 230 Material Cost $24,856.00 $74,448.00 $54,330.00 $54,210.00 $28,888.00 $44,737.00 $5,946.00 $68,632.00 $20,434.00 $17,153.00 $77,613.00 $13,923.00 $20,526.00 $27,920.00 $37,779.00 $24,288.00 $53,687.00 $18,578.00 $76,538.00 $24,416.00 $17,586.00 Labour Cost $3,375.00 $21,120.00 $11,040.00 $12,000.00 $5,160.00 $14,120.00 $1,640.00 $9,724.00 $4,071.00 $5,712.00 $17,700.00 $2,736.00 $5,808.00 $7,801.00 $7,801.00 $3,486.00 $7,824.00 $6,110.00 $13,986.00 $7,482.00 $6,670.00 Invoice Amount $36,700.00 $101,300.00 $64,060.00 $77,470.00 $41,200.00 $69,450.00 $8,720.00 $83,060.00 $28,430.00 $22,180.00 $98,170.00 $21,820.00 $28,440.00 $42,510.00 $53,780.00 $34,720.00 $63,970.00 $29,870.00 $105,910.00 $33,490.00 $28,380.00 49 18

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Accounting questions