PLEASE ANSWER IN EXCEL FORMAT WITH FORMULAS INCLUDED

INVENTORY COSTING & CAPACITY ANALYSIS Overhead Allocation with Capacity Considerations Free work cells Answer cells Jeanie Smith is the plant controller for Castle Lager Brewery, a privately held brewery in New England. Castle is considering raising capital in the upcoming year so Jeanie is taking a serious look at how her accounting practices might affect the reported income for the company. Castle uses normal job costing. i.e. overhead is allocated into production using a budgeted allocation rate per barrel. All overhead variances are written off to COGS at year-end. Jeanie wants to examine how using different denominator-levels in the budgeted allocation rate FOR FIXED COSTS might affect the income for the company. OPTION 1: VARIABLE COSTING Under Variable Costing, only variable manufacturing costs are treated as product costs and are capitalized into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). Any fixed manufacturing costs incurred are treated as period costs and are immediately expensed as COGS. Since no fixed cost is allocaed into inventory, under Variable Costing there is no fixed overhead allocation rate. OPTION 2:ABSORPTION COSTING, THEORETICAL CAPACITY Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e.initially recorded as inventory assets and later expensed as COGS). "Theoretical Capacity" represents the volume that a factory could hypothetically produce at full efficiency, 100% utilization, and ideal conditions. OPTION 3: ABSORPTION COSTING, PRACTICAL CAPACITY Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). "Practical Capacity" represents a factory's theoretical capacity adjusted for (reduced for) unavoidable stoppages, scheduled maintenance, and holidays and weekends. Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). OPTION 4:ABSORPTION COSTING, NORMAL CAPACITY "Normal Capacity" represents the average demand level over several years, considering season, cyclical and trend information. OPTION 5:ABSORPTION COSTING, BUDGETED VOLUME Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). "Budgeted Volume" represents the current year's expected demand level. The Brewery is able to produce 0.5 barrels per hour at maximum efficiency. Due to unavoidable operating interruptions, production actually averages 0.475 barrels per working hour. There are three 8-hour shifts each day. Due to union and legal restrictions, the plant actually operates three 7-hour shifts per day and works 28 days per 30-day average month. Based on the current budget, Castle estimates that it will only be able to sell only 2,500 barrels this year but that demand in the future will normalize around 2,800 barrels. $ Additional Information Budgeted fixed overhead for the year: Actual production for the year (barrels): Actual sales for the year (barrels): Actual fixed overhead for the year: 27,900 2,671 2,460 $ 26,700 Answer cells must remain in the same location so do not insert/delete columns or rows in the file. Unless otherwise indicated, numeric answers must include a formula or reference so do not hard enter the answer. REQUIRED: For each option: 1. Calculate the fixed overhead allocation rate that will be established at the beginning of the year. 2. Calculate the amount of fixed overhead that will be allocated into inventory throughout the year. 3. Calculate the amount of over-or (under-) allocated fixed overhead for the year. 4. Calculate the amount of fixed overhead that will be (1) expensed to COGS for units sold and (2) written off to COGS for under-allocation. OPTION 1: VARIABLE COSTING OPTION 2: ABSORPTION COSTING THEORETICAL CAPACITY OPTION 3: ABSORPTION COSTING PRACTICAL CAPACITY OPTION 4: ABSORPTION COSTING, NORMAL CAPACITY OPTION 5: ABSORPTION COSTING, BUDGETED VOLUME Budgeted Fixed Overhead You can hard enter these numbers. You can hard enter these numbers. Denominator Level used in the Budgeted F-OH Allocation Rate Budgeted Fixed Overhead Allocation Rate $ $ $ $ $ Units produced Fixed Overhead Allocated into Inventory Actual fixed overhead Over-(Under-) allocated fixed overhead $ $ $ $ $ Fixed overhead expensed to COGS for sold units Fixed overhead written off to COGS (+ for expense, - for benefit) Total Fixed overhead expensed in year $ $ $ $ $ You can hard enter these numbers. Denominator Level used in the Budgeted F-OH Allocation Rate Budgeted Fixed Overhead Allocation Rate $ $ $ $ Units produced Fixed Overhead Allocated into Inventory Actual fixed overhead Over-(Under-) allocated fixed overhead $ $ $ $ $ Fixed overhead expensed to COGS for sold units Fixed overhead written off to COGS (+ for expense, - for benefit) Total Fixed overhead expensed in year $ $ $ $ $ REQUIRED: Complete the following statements: (Assuming that inventory levels are growing over time) As the denominator level increases, the allocation rate per unit As the denominator level increases, the amount of overhead booked into the value of inventory As the denominator level increases, the overhead expensed for sold units As the denominator level increases, the overhead expensed for under-allocated overhead As the denominator level increases, the total overhead expensed INVENTORY COSTING & CAPACITY ANALYSIS Overhead Allocation with Capacity Considerations Free work cells Answer cells Jeanie Smith is the plant controller for Castle Lager Brewery, a privately held brewery in New England. Castle is considering raising capital in the upcoming year so Jeanie is taking a serious look at how her accounting practices might affect the reported income for the company. Castle uses normal job costing. i.e. overhead is allocated into production using a budgeted allocation rate per barrel. All overhead variances are written off to COGS at year-end. Jeanie wants to examine how using different denominator-levels in the budgeted allocation rate FOR FIXED COSTS might affect the income for the company. OPTION 1: VARIABLE COSTING Under Variable Costing, only variable manufacturing costs are treated as product costs and are capitalized into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). Any fixed manufacturing costs incurred are treated as period costs and are immediately expensed as COGS. Since no fixed cost is allocaed into inventory, under Variable Costing there is no fixed overhead allocation rate. OPTION 2:ABSORPTION COSTING, THEORETICAL CAPACITY Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e.initially recorded as inventory assets and later expensed as COGS). "Theoretical Capacity" represents the volume that a factory could hypothetically produce at full efficiency, 100% utilization, and ideal conditions. OPTION 3: ABSORPTION COSTING, PRACTICAL CAPACITY Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). "Practical Capacity" represents a factory's theoretical capacity adjusted for (reduced for) unavoidable stoppages, scheduled maintenance, and holidays and weekends. Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). OPTION 4:ABSORPTION COSTING, NORMAL CAPACITY "Normal Capacity" represents the average demand level over several years, considering season, cyclical and trend information. OPTION 5:ABSORPTION COSTING, BUDGETED VOLUME Under Absorption Costing, both variable and fixed manufacturing costs are treated as product costs and are capitalizaed into inventory (i.e. initially recorded as inventory assets and later expensed as COGS). "Budgeted Volume" represents the current year's expected demand level. The Brewery is able to produce 0.5 barrels per hour at maximum efficiency. Due to unavoidable operating interruptions, production actually averages 0.475 barrels per working hour. There are three 8-hour shifts each day. Due to union and legal restrictions, the plant actually operates three 7-hour shifts per day and works 28 days per 30-day average month. Based on the current budget, Castle estimates that it will only be able to sell only 2,500 barrels this year but that demand in the future will normalize around 2,800 barrels. $ Additional Information Budgeted fixed overhead for the year: Actual production for the year (barrels): Actual sales for the year (barrels): Actual fixed overhead for the year: 27,900 2,671 2,460 $ 26,700 Answer cells must remain in the same location so do not insert/delete columns or rows in the file. Unless otherwise indicated, numeric answers must include a formula or reference so do not hard enter the answer. REQUIRED: For each option: 1. Calculate the fixed overhead allocation rate that will be established at the beginning of the year. 2. Calculate the amount of fixed overhead that will be allocated into inventory throughout the year. 3. Calculate the amount of over-or (under-) allocated fixed overhead for the year. 4. Calculate the amount of fixed overhead that will be (1) expensed to COGS for units sold and (2) written off to COGS for under-allocation. OPTION 1: VARIABLE COSTING OPTION 2: ABSORPTION COSTING THEORETICAL CAPACITY OPTION 3: ABSORPTION COSTING PRACTICAL CAPACITY OPTION 4: ABSORPTION COSTING, NORMAL CAPACITY OPTION 5: ABSORPTION COSTING, BUDGETED VOLUME Budgeted Fixed Overhead You can hard enter these numbers. You can hard enter these numbers. Denominator Level used in the Budgeted F-OH Allocation Rate Budgeted Fixed Overhead Allocation Rate $ $ $ $ $ Units produced Fixed Overhead Allocated into Inventory Actual fixed overhead Over-(Under-) allocated fixed overhead $ $ $ $ $ Fixed overhead expensed to COGS for sold units Fixed overhead written off to COGS (+ for expense, - for benefit) Total Fixed overhead expensed in year $ $ $ $ $ You can hard enter these numbers. Denominator Level used in the Budgeted F-OH Allocation Rate Budgeted Fixed Overhead Allocation Rate $ $ $ $ Units produced Fixed Overhead Allocated into Inventory Actual fixed overhead Over-(Under-) allocated fixed overhead $ $ $ $ $ Fixed overhead expensed to COGS for sold units Fixed overhead written off to COGS (+ for expense, - for benefit) Total Fixed overhead expensed in year $ $ $ $ $ REQUIRED: Complete the following statements: (Assuming that inventory levels are growing over time) As the denominator level increases, the allocation rate per unit As the denominator level increases, the amount of overhead booked into the value of inventory As the denominator level increases, the overhead expensed for sold units As the denominator level increases, the overhead expensed for under-allocated overhead As the denominator level increases, the total overhead expensed