Spring 2020 Spreadsheet Project
Name:			Michael Arhin
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		$ 900,000.00
Credit Sales Collected in the Month of Sale		$1,500,000.00
Credit Sales Collected in the Following Month		$ 600,000.00
Inventory Policy
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		2	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)

Please post excel formulas for manufacturing overhead, Selling & Administrative Expenses and Factory Update and cash flow

please provide the excel formulas

E36 X v o 30750 DE FL Projected Budgeting Data 5 Sales & Collections October 2020 30,000 November 2020 34,000 December 2020 55,000 January 2021 47,000 Februar 2021 32,000 7 Sales in Units (Sweaters) 9 Selling Price per Sweater 100.00 10 11 Cash Sales Collected in the Month of Sale 12 Credit Sales Collected in the Month of Sale 13 Credit Sales Collected in the following Month 30% 50% 20% 15 Inventory Policy 16 Ending FG Inventory Requirement 17 Ending FG Inventory, September 30, 2020 3% of next months unit sweater sales 1,500 sweaters 19 Product Input Expenses 20 Direct Materials 21 Ending RM Inventory, September 30, 2020 22 Yards of Wool Required per Sweater 23 Row Materials Cost per Yard of Wool 24 Ending RM Inventory Requirement 0265.60 yards 4 yards per sweater 3.50 per yard 79 of next months sweater production needs 25 Wool Purchases Paid for in the Month of Purchase 27 Wool Purchases Paid for in the Month following the Purchase 85% 15% 29 Direct Labor 30 Number of Workers Required for the Making of Each 31 Lobor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cost per Hour 2 workers 0.5 hours 15.00 per hour 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fred Manufacturing Overhead 37 Noncosh Fixed Manufacturing Overhead included in above) $ $ 1175 per sweater 30,200.00 per month Oct 10.250.00 per month Oct $ 30.750 00 per month Nov & beyond) 15750.00 per month Nov & beyond) ive Expenses 39 Selg & Adr 40 Variable SEA 41 FSA 737 per sold $ 23,900.00 per month S110.750 00 entory Cost per Unit Sales Budget INPUTS INPUTS - Blance Sheet F Expected Cash Collections in File Huile et Paye Layout Formulas Data Review View Help RX Cut = Accounting - Paste Copy - Format Painter Clipboard Tw Cen MT BIU -12 - AA - AA E 9 Wrap Text Merge & Center - E E $ -% 8-98 Conditional FC Formatting Font Alignment Number E36 x fe 30750 G H 19 Product Input Expenses 20 Direct Materials 21 Ending RM Inventory, September 30, 2020 22 Yards of Wool Required per Sweater 23 Raw Materials Cost per Yard of Wool 24 Ending RM Inventory Requirement 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs 85% 15% 26 Wool Purchases Paid for in the Month of Purchase 27 Wool Purchases Paid for in the Month following the Purchase 28 29 Direct Labor 30 Number of Workers Required for the Making of Each 31 Labor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cost per Hour 33 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fixed Manufacturing Overhead 37 Noncash Fixed Manufacturing Overhead (included in above) 2 workers 0 5 hours 15.00 per hour $ $ 11.75 per sweater 30,200.00 per month (Oct) 10,250.00 per month (Od) $ $ 30,750.00 per month (Nov. & beyond) 15,750.00 per month (Nov. & beyond) 39 Selling & Administrative Expenses 40 Variable S&A 41 Fixed S&A 42 Noncash Fixed S&A included in above) 7.37 per unit sold $ 23,900.00 per month $ (10,750.00) per month 44 Factory Update & Cash Flow 45 Factory Update (PP&E) $ 400,500.00 paid on October 31, 2020 47 Principle Borrowed on October 1, 2020 48 Principle Repaid on November 30, 2020 49 Interest Payment on Borrowings in October & November $ 300,000.00 $ 300,000.00 S 9,000.00 per month paid in following month) 50 Autoeve 0 Home 2 Insert . Page Layout ACCT 285 - Excel Project Template - Spring 2000 (1) - Excel Formulas Data Review View Help -12 - AA == Wap Tot - -A- Merge Center - $ -% axat Copy To Con MT BIU- 9 Font Al Finished Goods Inventory Cost per Unit Per Unit (Sweater) 3 Direct Material (wool & other materials) 4 Direct Labor 5 Variable Overhead 6 TOTAL FG INVENTORY COST PER UNIT E36 X v o 30750 DE FL Projected Budgeting Data 5 Sales & Collections October 2020 30,000 November 2020 34,000 December 2020 55,000 January 2021 47,000 Februar 2021 32,000 7 Sales in Units (Sweaters) 9 Selling Price per Sweater 100.00 10 11 Cash Sales Collected in the Month of Sale 12 Credit Sales Collected in the Month of Sale 13 Credit Sales Collected in the following Month 30% 50% 20% 15 Inventory Policy 16 Ending FG Inventory Requirement 17 Ending FG Inventory, September 30, 2020 3% of next months unit sweater sales 1,500 sweaters 19 Product Input Expenses 20 Direct Materials 21 Ending RM Inventory, September 30, 2020 22 Yards of Wool Required per Sweater 23 Row Materials Cost per Yard of Wool 24 Ending RM Inventory Requirement 0265.60 yards 4 yards per sweater 3.50 per yard 79 of next months sweater production needs 25 Wool Purchases Paid for in the Month of Purchase 27 Wool Purchases Paid for in the Month following the Purchase 85% 15% 29 Direct Labor 30 Number of Workers Required for the Making of Each 31 Lobor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cost per Hour 2 workers 0.5 hours 15.00 per hour 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fred Manufacturing Overhead 37 Noncosh Fixed Manufacturing Overhead included in above) $ $ 1175 per sweater 30,200.00 per month Oct 10.250.00 per month Oct $ 30.750 00 per month Nov & beyond) 15750.00 per month Nov & beyond) ive Expenses 39 Selg & Adr 40 Variable SEA 41 FSA 737 per sold $ 23,900.00 per month S110.750 00 entory Cost per Unit Sales Budget INPUTS INPUTS - Blance Sheet F Expected Cash Collections in File Huile et Paye Layout Formulas Data Review View Help RX Cut = Accounting - Paste Copy - Format Painter Clipboard Tw Cen MT BIU -12 - AA - AA E 9 Wrap Text Merge & Center - E E $ -% 8-98 Conditional FC Formatting Font Alignment Number E36 x fe 30750 G H 19 Product Input Expenses 20 Direct Materials 21 Ending RM Inventory, September 30, 2020 22 Yards of Wool Required per Sweater 23 Raw Materials Cost per Yard of Wool 24 Ending RM Inventory Requirement 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs 85% 15% 26 Wool Purchases Paid for in the Month of Purchase 27 Wool Purchases Paid for in the Month following the Purchase 28 29 Direct Labor 30 Number of Workers Required for the Making of Each 31 Labor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cost per Hour 33 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fixed Manufacturing Overhead 37 Noncash Fixed Manufacturing Overhead (included in above) 2 workers 0 5 hours 15.00 per hour $ $ 11.75 per sweater 30,200.00 per month (Oct) 10,250.00 per month (Od) $ $ 30,750.00 per month (Nov. & beyond) 15,750.00 per month (Nov. & beyond) 39 Selling & Administrative Expenses 40 Variable S&A 41 Fixed S&A 42 Noncash Fixed S&A included in above) 7.37 per unit sold $ 23,900.00 per month $ (10,750.00) per month 44 Factory Update & Cash Flow 45 Factory Update (PP&E) $ 400,500.00 paid on October 31, 2020 47 Principle Borrowed on October 1, 2020 48 Principle Repaid on November 30, 2020 49 Interest Payment on Borrowings in October & November $ 300,000.00 $ 300,000.00 S 9,000.00 per month paid in following month) 50 Autoeve 0 Home 2 Insert . Page Layout ACCT 285 - Excel Project Template - Spring 2000 (1) - Excel Formulas Data Review View Help -12 - AA == Wap Tot - -A- Merge Center - $ -% axat Copy To Con MT BIU- 9 Font Al Finished Goods Inventory Cost per Unit Per Unit (Sweater) 3 Direct Material (wool & other materials) 4 Direct Labor 5 Variable Overhead 6 TOTAL FG INVENTORY COST PER UNIT