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		$ 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 an excel for Direct materials and Direct labor

excel formulas

AutoSave Search 9 Insert ACCT 285 - Excel Project Template - Spring 2020 (1) - Excel Data Review View Help Fle Home Page Layout Formulas == - G 3 Xcut Copy Format Painter Normal Tw Cen MT BIU -12 - AA A Waplet Merge Center - S - % 9 33 * Conditional Formatting - Format as calculation The Che For Agner October Q UARTER Direct Labor Budget 2020 November December 4 Units Produced 5 Labor Hours per Unit 6 TOTAL LABOR HOURS REQUIRED 7 Rate per Labor Hour 8 TOTAL DIRECT LABOR COST Case: Lexie's Wool Sweaters is a small business that makes 100% wool sweaters 10 below pertains to the company's budgeting process during their busiest time of year. Lexie's uses variable costing Each student must complete this assignment individually. Sales & Collections Each sweater is considered a unit of finished good which is sold for $100.00. Budgeted sales in units (sweaters) are as follows: October 2020 30,000 sweaters November 2020 34,000 sweaters December 2020 55,000 sweaters January 2021 47,000 sweaters February 2021 32,000 sweaters 30% of the company's sales are cash sales which are collected immediately. The remaining 50% of total sales are collected in the month of sale and the other 20% are collected in the month following the sale. Inventory Policy The company desires to have very little finished goods inventory on hand. At the end of each month, ending inventory is 3% of the following month's budgeted sales in units. On September 30, the company had 1,500 units on hand. 1 of 5 1293 words Product Input Expenses Direct Materials Each sweater requires 4 yards of wool. The cost per yard and other materials used is $3.50. Lexie's desires to have materials on hand at the end of each month equal to 7% of the following month's production needs. On September 30, the company had 8,265.00 yards of wool on hand. The company pays 85% of the month's material purchases in the month of purchase and delays the payment for the other 15% of the month's material purchases until the month following the purchase. Direct Labor The process to make each sweater requires 2 workers to contribute 5 hours of labor time. The workers are currently being paid $15.00/hour. Wages are paid in the month incurred. Manufacturing Overhead Variable manufacturing overhead is $11.75 per sweater. Fixed manufacturing overhead is $30,200 per month including $10,250 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the month incurred. See below for changes to fixed overhead after October Selling & Administrative Expenses Variable selling and administrative expenses is $7.37 per sweater sold. Fixed selling and administrative expense is $23,900 per month including $10,750 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Factory Update & Cash Flow Lexie's borrowed $300,000 on Oct. 1, 2020, to prepare for a factory update. The full $300,000 was repaid on Nov. 30, 2020, but Lexie's 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. D Focus 16 - 3 Quarter Balance Sheet Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 ASSETS LIABILITIES & EQUITIES Cash $ 150,000.00 Accounts Payable $ 14,500.00 Accounts Receivable $ 75,000.00 Noles Payable Raw Materials Inventory $ 28,929.00 Interest Payable Finished Goods Inventory $ 61,125.00 TOTAL LIABILITIES $ 14,500.00 PP&E, net $ 487,000.00 Retained Earnings $ 787,554.60 TOTAL ASSETS $ 802,054.00 TOTAL LIABILITIES & EQUITY S 802,054.60 Requirements Open the template provided to you in the assignments link in the Excel Project folder in Canvas Enter your name at the top of the INPUTS tab. Use the information above to fill in the ton cells within the INPUTS tab. Prepare a master budget for the quarter ended December 31, 2020 that includes: Finished Goods Inventory Cost per Unit Manufacturing Overhead Budget Sales Budget Selling & Administrative Expense Budget Schedule of Expected Cash Collections Cash Budget Production Budget Budgeted Income Statement Raw Materials Purchases Budget Budgeted Balance Sheet Direct Labor Budget Each schedule should be on a separate worksheet os appears in your template. Complete the fan shaded areas of the template only. All of your spreadsheets must be formula drivent This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the Styles Lexie's is considering using a new wool supplier to get a little higher quality input. However, the overall goal is company growth so they also want to ensure they aren't sacrificing too much net income at this time for the change. Use Goal Seek to find the highest wool cost per yard that could be paid to the new supplier while still pulling in net income of $5,500,000 for the quarter. HINT: To find goal seek, click on the Data tab and under Data Tools dick on the What-If Analysis After you find your answer, manually enter your answer on the Goal Seek tab and then change the materials cost per yard back to $3.50 on the INPUTS tab. 3rd Quarter Balance Sheet Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 ASSETS LIABILITIES & EQUITIES $ 150,000.00 Accounts Payable $ 14,500.00 Accounts Receivable $ 75,000.00 Notes Payable Raw Materials Inventory $ 28,929.60 Interest Payable Finished Goods Inventory $ 61,125.00 TOTAL LIABILITIES $ 14,500.00 $ 487,000.00 Retained Earnings $ 787,554.60 $ 802,054.60 TOTAL LIABILITIES & EQUITY $ 802,054.60 Cash PP&E, net TOTAL ASSETS