Prepare a monthly production report

Reference no: EM133063466

ASSIGNMENT OVERVIEW

The Hotrock Company makes electric guitars which it sells to wholesalers and on the internet. There are two main departments involved in the production process, Manufacturing and Packing. As they mass produce hundreds of guitars a month they have decided that the best way to track their costs is to use a Process Costing model. You are required to use the information provided to prepare a monthly Production Report for the manufacturing department using the weighted average method.

Excel Assignment

The instructions below explain what caleculation is required for each cell.

All cells with a blue background require a calculation (except for the ledger account names).

Section A - The following questions are to be answered on the Labour Costs Sheet

A 1 Calculate the cost of each employees overtime using time and a half (divide monthly rate by 20 to get a daily rate, then 2 divide by 8 to get an hourly rate, then multiply by 1.5 to get time and a half and then multiply by the overtime hours worked).
A 2 Calculate Monthly Super for each employee using the Monthly Salary and specified Super rate, note this rate may be 1 subject to change. (Do not include Overtime.)
A 3 Calculate total wages (including overtime and super) for each employee 1
A 4 In row 29 Calculate the total Overtime Hours, Monthly Salary, Overtime Earnings, Super and Total Wages 1
A 5 In row 30 Calculate the average Overtime Hours, Monthly Salary, Overtime Earnings, Super and Total Wages 1
A 6 In row 31 Calculate the highest Overtime Hours, Monthly Salary, Overtime Earnings, Super and Total Wages 1
A 7 In row 32 Calculate the lowest Overtime Hours, Monthly Salary, Overtime Earnings, Super and Total Wages

Section B - The following questions are to be answered on the Material Costs Sheet
D 1 Use a formula to lookup the Description for each Part Code from the Inventory sheet 8 2 Use a formula to lookup the Unit Price for each Part Code from the Inventory sheet 8 3 Use a formula to lookup the Unit (per) for each Part Code from the Inventory sheet 8 4 Calculate the cost for each Part Code

Section C - The following questions are to be answered on the Summary InformationSheet
C 1 In B19 calculate the total Materials cost (using values in the Materials Costs sheet)
C 2 In 820 use a formula to pull through the total labour costs from the Labour Costs Sheet

Section D - The following questions are to be answered on the Production Report Sheet
D 1 In 85 use a formula to get the units in process from the Summary sheet
D 2 In 86 use a formula to get the units started into production from the Summary sheet
D 3 In 87 calculate the Total units to be accounted for
D 4 In 811 use a formula to get the Units Transferred Out of production from the Summary Sheet
D 5 In 812 calculate how many units will still be in process at the end of the month
D 6 In 813 Total the values in 811 and 812 (Hint: Check the answer should be the same as 87)
D 7 In Row 16 calculate the equivalent units transferred out for both Materials and Conversion (note Conversion is Labour + Ove
D 8 In Row 17 calculate the equivalent units still in process at the end of the month (use the relevant information in the Summai
D 9 In Row 18 calculate the total equivalent units (Material and Conversion)
D 10 In Row 22 calculate the Equivalent Unit Costs for Work in process at the beginning of the month
D 11 In Row 23 calculate the Equivalent Unit Costs for Costs added by the department
D 12 In Row 24 calculate the Total Cost to Account For
D 13 In Row 26 calculate the cost per equivalent unit
D 14 In Row 29 calculate the Cost of Units Completed and Transferred Out
D 15 In Row 30 calculate the cost of Work in process, at the end of the month
D 16 In Row 31 calculate the Total Costs Accounted For

Section E - The following questions are to be answered on the Inventory Sheet
E 1 In column H create a calculation to check whether the quantity in stock has fallen below the minimum stock level eneterd in 119. If it has put a capital "V" in the cell, otherwise leave it empty (not a space or any other character).
E 2 Some items may already be on backorder so we do not wish to order these again. In column I write a formula to check if the item is below min stock level and NOT on back order, if both these criteria are met look up the quantity to be reordered for that unit type (per), otherwise just put 0 (not in quotes).
E 3 In L4 create a flexible formula to look up how many products lines (part codes) are supplied by the supplier in K4 (the formula should work regardless of sort order). Copy the formula down to L9
E 4 In M4:M9 create formulas to calculate how many product lines need to be reordered for each supplier.
E 5 Use Excel help to investigate the SUMPRODUCT function. In L20 use a SUMPRODUCT to calculate the total value of all the stock in inventory. (If we have 5 Bridges at $2 each that stock is worth $10.)
E 6 Challenge question: In L21 calculate the total cost to reorder all the items that require reordering. (This is intended as a challenge and the only help given on this question will be clarifying requirements.)

Attachment:- Excel Assessment.rar

Reference no: EM133063466

Questions Cloud

Sexual assaults and stabbings and beatings : The Governor is concerned with the number of inmates who are being victimized by assaults, including sexual assaults and stabbings and beatings.

Structure of correctional agency : What is your previous experience with the structure of a correctional agency?

Discuss the threat or security problem : Identify and discuss the threat or security problem. Are there conflicting perspectives in the TE about the extent of the problem

Preparation for pretrial of case : Illuminate you that pretrial for the case concerning your sister's primary care physician and the specialist will be occurring in the gathering room of Law Firm

Prepare a monthly production report : Prepare a monthly Production Report for the manufacturing department using the weighted average method

What amount should Creative Sound Systems report : Creative Sound Systems purchased treasury stock, equipment, and a patent for $22 million, $26 million, and $13 million, What amount should Creative Sound System

Determine procedures in accounting for standard costs : Determine procedures in accounting for standard costs. How to prepare a variance analysis for standard costing and normal costing

What price should you pay : YOLO Inc. needs to raise $5 million in a rights offering. What price should you pay if you plan to buy when the market just opens on the ex-rights day

What is a bond issue cost and its accounting treatment : What is a bond issue cost and its accounting treatment? What is bond indenture? What are some contents of a bond indenture

User Account

All Pages