Answered step by step
Verified Expert Solution
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
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 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 18Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started