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 Selling and Administrative Expense Budget in excel using formulas only

Answer 1

Selling and Administrative Expense Budget
	October	November	December	Quarter
Sales units	30,000	34,000	55,000	119,000
Variable S&A	$221,100	$250,580	$405,350	$877,030
Fixed S&A	$23,900	$23,900	$23,900	$71,700
Less: Noncash Fixed S&A	-$10,750	-$10,750	-$10,750	-$32,250
Total Selling and Administrative Expense	$234,250	$263,730	$418,500	$916,480
Working
Variable S&A	7.37 x 30000	7.37 x 34,000	7.37 x 55,000