Question

Spring 2020 Spreadsheet Project
Name:
Lexie's Wool Sweaters
Projected Budgeting Data
Sales & Collections
		October 2020	November 2020	December 2020	January 2021	February 2021
Sales in Units (Sweaters)		30,000	34,000	55,000	47,000	32,000
Selling Price per Sweater		$          100.00
Cash Sales Collected in the Month of Sale		30%
Credit Sales Collected in the Month of Sale		50%
Credit Sales Collected in the Following Month		20%
Ending FG Inventory Requirement		3%	of next months unit sweater sales
Ending FG Inventory, September 30 , 2020		1,500	sweaters
Product Input Expenses
Direct Materials
Ending RM Inventory, September 30, 2020		8265.60	yards
Yards of Wool Required per Sweater		4	yards per sweater
Raw Materials Cost per Yard of Wool		$               3.50	per yard
Ending RM Inventory Requirement		7%	of next months sweater production needs
Wool Purchases Paid for in the Month of Purchase		85%
Wool Purchases Paid for in the Month following the Purchase		15%
Direct Labor
Number of Workers Required for the Making of Each Sweater		5	workers
Labor Hours Required per Worker per Unit of FG (Sweater)		0.5	hours
Labor Cost per Hour		$             15.00	per hour
Manufacturing Overhead
Variable Manufacturing Overhead		$             11.75	per sweater
Fixed Manufacturing Overhead		$     30,200.00	per month (Oct.)	$     30,750.00	per month (Nov. & beyond)
Noncash Fixed Manufacturing Overhead (included in above)		$     10,250.00	per month (Oct.)	$     15,750.00	per month (Nov. & beyond)
Selling & Administrative Expenses
Variable S&A		$               7.37	per unit sold
Fixed S&A		$     23,900.00	per month
Noncash Fixed S&A (included in above)		$    (10,750.00)	per month
Factory Update & Cash Flow
Factory Update (PP&E)		$   400,500.00	paid on October 31, 2020
Principle Borrowed on October 1, 2020		$   300,000.00
Principle Repaid on November 30, 2020		$   300,000.00
Interest Payment on Borrowings in October & November		$       9,000.00	per month (paid in following month)

Create a Direct Labor Budget in Excel using Formulas only

Answer 1

Direct Labor Budget
	October	November	December	Quarter
Units Produced	29,520	34,630	54,760	118,910
Labor Hours per Unit	2.5	2.5	2.5	2.5
Total Labor Hours Required	73,800	86,575	136,900	297,275
Rate per labor hour	$15.00	$15.00	$15.00	$15.00
Total Direct Labor Cost	$1,107,000	$1,298,625	$2,053,500	$4,459,125
Working
2020					2021
	October	November	December	Quarter	January	February
Sales	30,000	34,000	55,000	119,000	47,000	32,000
Plus: Desired units of ending inventory	1,020	1,650	1,410	1,410	960
Total needs	31,020	35,650	56,410	120,410	47,960
Less: Units in Beginning inventory	1,500	1,020	1,650	1,500	1,410
Production in Units	29,520	34,630	54,760	118,910	46,550