Please prepare a spreadsheet file using the information given below. The spreadsheet will show entries for the flow of costs in a job costing system. It will also cost out a job using actual and normal costing and make adjustments (using both the proration approach (using the amount of MOH in each relevant account) and the write-off to CGS approach for under- or over applied MOH.

The file should have three worksheets. The first worksheet must contain only the inputs to the problem. These facts will be referenced by the second and third worksheets that contain the transformation of the inputs to the solution to the problem.

The second worksheet must contain:

a) the necessary t-accounts and entries into the t-accounts for the transactions

listed below (see heading "Transactions") using the proration method, Be sure to show the unadjusted balances (i.e., balances before the adjustment ) of WIP, FG, and CGS;

b) the actual and normal MOH rates (use MHRS to allocate MOH);

c) two simple job costing sheets (one for actual costing and one for normal

costing) for Job 401 (just show the job number and separate line items for DL,

DM, MOH, and total job cost).

The third worksheet must contain:

1. the amount for the adjustments under both the proration approach (using the

amount of MOH in the accounts for the proration) and the write-off to CGS

approach;

2. for the proration approach the following two tables:

The first table should have columns for:

• the account;
• the amount of MOH in each account;
• the percentages;
• the total under- or over applied MOH;
• the amount of the adjustment;

and rows for each account.

The second table should have columns for:

• account titles;
• account balances.

and rows for:

• the unadjusted balance;
• the adjustment;
• the adjusted balance ;

3. for the write-off to cost of goods sold approach have a table

with columns for the accounts and then rows for:

3. • the unadjusted account balance;
   • the adjustment;
   • the adjusted account balance;
4. the entries (in the T-accounts in the second worksheet) for the adjustment under the proration approach;

A suggested template for the general solution structure is on pages 4 and 5.

Please upload your excel file to Moodle. The worksheets must include the following formatting:

1. All numeric values in units of dollars should be label with the dollar sign to differentiate them from numbers in non-dollar units such as labor hours;
2. Format cells for percentages and dollar amounts with no decimal places;
3. Labels for the T-accounts, budgeted and actual MOH rates.

Transactions: (hint see Exhibit 4-7 p. 122)

1) Purchase of materials;

2) Requisition of direct and indirect materials;

3) Pay direct and indirect labor;

4) Incur actual indirect manufacturing costs;

5) Allocate indirect manufacturing costs using normal costing;

6) Transfer finished goods from WIP to finished goods inventory;

7) Sale of goods;

8) Adjustment for underapplied or overapplied indirect manufacturing costs.

1. the amount for the adjustments under both the proration approach (using the

amount of MOH in the accounts for the proration)

2. the write-off to CGS approach.

SS#1 Job Costing
Materials Control BB 1/1/19	$1,800,000
WIP Control BB 1/1/19	$300,000
FG Control BB 1/1/19	$450,000
Materials purchased on account	$2,250,000
Direct materials issued to WIP Control	$1,650,000
Indirect materials	$954,000
Direct labor incurred	$1,350,000
Indirect labor incurred	$810,000
Depreciation on Plant Equipment	$1,200,000
Utilities in Plant	$144,000
Repairs and maintenance Plant	$312,000
Insurance on Plant	$108,000
Salaries on plant personnel	$360,000
CGM	$3,900,000
CGS	$2,250,000
Sales revenue	$6,000,000
Actual MOH	$3,888,000
Budgeted MOH	$4,797,500
Budgeted machine hours	38,380
Actual machine hours	32,400
Amount of MOH included in the respective accounts balance:
WIP	$1,764,000
FG	$1,260,000
CGS	$1,176,000
Job 401:
DM	$18,000
DL	$21,000
Actual machine hours used	90