Question

Managerial Accounting

Can anyone Help me with these, and include the calculations in order for me not to hardcode the numbers because I need to use the excel formulas.

Thank you

Project #2 Sales Projections in Units January February March April |May 13,402 45,819 44,164 53,722 52,482 Projected Sales Price/Unit $ 44.00 Monthly Projected Selling & Administrative Expenses Variable Cost/Unit $16.00 Fixed Costs $5,519 Production: Desired Ending Inventory Beginning Inventory (new business) 59.0% 0 Materials 64.6% 11.0 Desired Ending Inventory Number of Materials per Unit Projected Cost/Material Unit Beginning Inventory (new business) $23.00 Lo Direct Labor Time per Unit (in hours) Cost per Hour 1.00 $21.00 Manufacturing Overhead Variable Cost/Unit Fixed Costs $5.00 $5,625

Answer 1

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!

Your question is very long and as per HOMEWORKLIB POLICY I am not allowed to solve questions with so many subparts. Still I have attached all the budgets till Income Statement. Request you to raise another question for Sensitivity Analysis.

Managerial Accounting
Sales Budget	January	February	March	April	May	Note
Projected Sales units	13,402.00	45,819.00	44,164.00	53,722.00	52,482.00	A
Sell price per unit	44.00	44.00	44.00	44.00	44.00	B
Anticipated Sales Dollars	589,688.00	2,016,036.00	1,943,216.00	2,363,768.00	2,309,208.00	C=A*B
Total Selling & admin expenses Budget	January	February	March
Sales in units	13,402.00	45,819.00	44,164.00			See A
Variable Selling & admin expenses per unit	16.00	16.00	16.00			D
Anticipated Variable costs	214,432.00	733,104.00	706,624.00			E=A*D
Fixed Selling & admin cost	5,519.00	5,519.00	5,519.00			F
Total Anticipated Selling & admin cost	219,951.00	738,623.00	712,143.00			G=E+F
Budgeted Production in Units	January	February	March	April	May
Sales in units	13,402.00	45,819.00	44,164.00	53,722.00	52,482.00	See A
Add: Desired Ending Inventory	27,033.21	26,056.76	31,695.98	30,964.38		H= 59% of A of next month. Because Desired Ending Inventory is 59% of next month sales units.
Total units needed	40,435.21	71,875.76	75,859.98	84,686.38
Less: Beginning Inventory	-	27,033.21	26,056.76	31,695.98		I= 59% of A of same month. Because company is keeping 59% of current month sales as opening stock and January stock is 0 as mentioned in question.
Budgeted Production in Units	40,435.21	44,842.55	49,803.22	52,990.40		J
Material requirement Budget	January	February	March	April
Units to be produced	40,435.21	44,842.55	49,803.22	52,990.40		See J
Material required per unit	11.00	11.00	11.00	11.00		K
Total Production need	444,787.31	493,268.05	547,835.42	582,894.40		L=J*K
Add: Desired Ending Inventory	318,651.16	353,901.68	376,549.78	-		M= 64.6% of L of next month. Because Desired Ending Inventory is 64.6% of next month production units.
Total Materials needed	763,438.47	847,169.73	924,385.20
Less: Beginning Inventory	-	318,651.16	353,901.68	376,549.78		N= 64.6% of L of same month. Because company is keeping 64.6% of current month production as opening stock and January stock is 0 as mentioned in question.
Total materials needed to be purchased	763,438.47	528,518.57	570,483.52			O
Cost per unit of material	23.00	23.00	23.00			P
Total materials cost	17,559,084.82	12,155,927.13	13,121,120.98			Q=P*O
Direct Labor Budget	January	February	March
Number of Units to be produced	40,435.21	44,842.55	49,803.22			See J
Labor Hour required per unit	1.00	1.00	1.00			R
Total Labor Hour needed	40,435.21	44,842.55	49,803.22			S=J*R
Labor Rate per Hour	21.00	21.00	21.00			T
Total Direct Labor cost	849,139.41	941,693.55	1,045,867.62			U=S*T
Labor Hour required per unit	1.00					See R
Labor Rate per Hour	21.00					See T
Direct Labor cost per product	21.00					V= R*T
Manufacturing overhead Budget	January	February	March
Production in units	40,435.21	44,842.55	49,803.22			See J
Variable OH rate per unit	5.00	5.00	5.00			W
Anticipated VOH costs	202,176.05	224,212.75	249,016.10			X= J*W
Fixed OH costs	5,625.00	5,625.00	5,625.00			Y
Total Anticipated OH costs	207,801.05	229,837.75	254,641.10			Z= X+Y
Predetermined OH Rate
Total estimated OH costs (for the quarter)	692,279.90	5.12	Per DLH
Total estimated DLHs (for the quarter)	135,080.98
MOH per unit
Predetermined MOH Rate	5.12
DLHs per unit	1.00
MOH per unit	5.12					AA
Product Cost Per unit
Direct Materials	23.00					See P
Direct Labor	21.00					See T
Manufacturing Overhead	5.12					See AA
Total Unit Cost	49.12					AB
Projected Income Statement	January	February	March
Sales in Units	13,402.00	45,819.00	44,164.00			See A
Sales Revenue	589,688.00	2,016,036.00	1,943,216.00			See C
Cost of Good sold
Direct Materials	308,246.00	1,053,837.00	1,015,772.00			This is P*A
Direct Labor	281,442.00	962,199.00	927,444.00			This is T*A
Manufacturing Overhead	68,684.25	234,818.94	226,337.19			This is AA*A
Gross Profit (margin)	(68,684.25)	(234,818.94)	(226,337.19)			AC
Selling & admin (operating) expenses	219,951.00	738,623.00	712,143.00			See G
Operating Income	(288,635.25)	(973,441.94)	(938,480.19)			AD=AC-G