$4.$ What was the Pen Division$$s operating income for $2023?$ Use normal costing and adjust for mis$-$applied MOH.

$5.$ Use the $2023$ actual monthly MOH information $($see table C$)$ to calculate annual estimates of cost behavior $($fixed and variable components$)$ for each MOH activity.

a$.$ Use the High$-$Low method to separate variable and fixed costs. For this exercise use @VLOOKUP, @MAX, and @MIN excel functions.

b$.$ Use the Regression function found under Data: Data Analysis in Excel. Place output on a separate sheet. Estimates from these regression results will be used for the $2024$ budget.

A$.$ Budget information assembled at the start of $2023$

Basic Pen Customized Pen

Budgeted production $2,500,0001,000,000$

Number of pens per batch $4,0002,000$

Setup labor$-$hours per batch $4$ hours $10$ hours

Budgeted machine hours $10,000$ hrs $8,000$ hrs

Budgeted direct manufacturing labor $50,000$ hrs $30,000$ hrs

Budgeted plastic per pen casing $1/16$ pound $1/8$ pound

Budgeted cartridges per pen $11$

Budgeted Manufacturing Operations Overhead Costs

Variable costs

Supplies $$10,000$

Indirect manuf. Labor $25,000$

Power $30,000$

Maintenance $20,000$ $$85,000$

Fixed costs

Depreciation $150,000$

Supervision $140,000$

Power $65,000$

Maintenance $100,000$ $$455,000$

Budgeted Setup Overhead Costs

Variable costs

Inspection costs $$80,000$

Supplies $60,000$

Indirect manuf. Labor $90,000$

Power $30,000$ $$260,000$

Fixed costs

Depreciation $95,000$

Supervision $125,000$

Power $85,000$

Engineering costs $200,000$ $

$505,000$

B$.$ Actual information for $2023$ that can be traced directly to each product:

Basic Pen Customized Pen

Direct material used $($plastic & cartridge$)$ $$1,920,000$ $$1,300,000$

Pen casings per pound of plastic $16$ Casings $8$ Casings

Plastic cost $*$ $$8$ per pound

Plastic in inventory $(12/31/2023)*15,000$ pounds

Ink cartridge cost $*$ $$0\mathrm{.}30$ per unit

Ink cartridges in inventory $(12/31/2023)*180,000$ cartridges

Direct labor cost $$720,000$ $$540,000$

Direct labor hours $48,000$ hours $30,000$ hours

Machine hours used in total $9,600$ hours $8,000$ hours

Number of inspection hours in total $1,000$ hours $2,000$ hours

Pens per batch $4,0002,000$

Number of batches $600500$

Number of engineering changes $010$

Setup labor hours $2,4005,000$

Total sales commissions $$176,000$ $$160,000$

Units produced $2,400,0001,000,000$

Units sold $2,200,0001,000,000$

Pens in inventory $(01/01/2023)200,000$@$$1\mathrm{.}25$ each $0$

Sales revenue $$3,850,000$ $$3,200,000$

$*$ The same plastic stock and ink cartridges are used for the Basic and Custom pens

C$.$ Additionally, the pen division had the following actual costs in $2023$ that were not directly traced to either product:

Actual Manufacturing Overhead Costs $-2023$

Month Machine$-$Hours Manufacturing Operations Overhead Cost Setup$-$Hours Setup Overhead Cost Direct Labor$-$ Hours

Jan $900$ $$41,313401$ $$56,0005,340$

Feb $1,100$ $$44,681426$ $$60,4524,440$

Mar $952$ $$45,962500$ $$58,2613,460$

Apr $1,752$ $$47,851499$ $$54,4446,180$

May $1,436$ $$49,621567$ $$64,6464,260$

Jun $1,569$ $$46,395600$ $$68,55511,240$

Jul $1,648$ $$50,961686$ $$72,5227,420$

Aug $2,052$ $$51,388702$ $$71,45610,542$

Sep $1,896$ $$51,382925$ $$72,6548,880$

Oct $1,530$ $$46,351862$ $$74,6618,283$

Nov $1,348$ $$47,513605$ $$64,5004,750$

Dec $1,417$ $$42,691627$ $$61,8883,205$

Total $17,600$ $$566,1097,400$ $$780,03978,000$

Actual Annual Period Costs $2023$

Advertising expense $$250,000$

Sales department salaries $$350,000$

Selling and administration depreciation $$50,000$

D$.$ Based on the information you have gathered you also make the following assumptions:

$$ Because of past financial trouble in the division, all expenses must be paid in cash. No sales are made on account $($all sales are cash sales$).$

$$ Information for the $2024$ budget is as follows:

o$$ The budgeted unit input quantities for materials, labor and MOH $($machine hours and setup labor hours$)$ used in $2023$ will also be used in the $2024$ budget.

o$$ The cost of plastic is expected to increase by $8\%$ in $2024$ over $2023$ actual costs. The cost of cartridges is expected to increase by $6\%$ in $2024$ over $2023$ actual costs.

o$$ Budgeted labor rates in $2024$ are expected to increase by $7\%$ over $2023$ actual rates for Basic pens and $12\%$ over $2023$ actual rates for Customized pens.

o$$ Calculate $2024$ budgeted variable moh rates and total fixed moh from $2023$ actual monthly cost and activity data $($Table C$).$ Use Regression Analysis to determine estimates for variable and monthly fixed costs. Convert monthly estimates of fixed costs into annual costs $($i$.$e$.,$ monthly estimate times $12).$

o$$ Assume non$-$cash $($e$.$g$.,$ depreciation$)$ MOH item amounts remain unchanged from budgeted $2023$ data.

o$$ Production and sales forecasts are within the relevant range.

o$$ Budgeted variable period costs per unit and fixed costs in tota