Factory Update & Cash Flow

Lexies borrowed $300,000 on Oct. 1, 2020, to prepare for a factory update. The full $300,000 was repaid on Nov. 30, 2020, but Lexies paid two interest payments of $4,500 for the borrowings.

The factory update cost $400,500 which was paid on Oct. 31,2020. This addition to depreciable PP&E caused the fixed manufacturing overhead to raise to $30,750 per month in November with $15,750 being non-cash.

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)

fill out a cash budget in excel using formulas only