How do you compute the budgets?

Accounting 285 Due Friday, April 3rd Spring 2020 @ 11:59 PM Excel Spreadsheet via CANVAS Project Lexie's Wool Sweaters Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Lexie's Wool Sweaters is a small business that makes 100% wool sweaters to sell. The information 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. ........................................................ . ........................................................ . 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.60 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. ........................................................ . ........................................................ . 3rd Quarter Balance Sheet ........................................................ . 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 tan cells within the INPUTS tab. Prepare a master budget for the quarter ended December 31, 2020 that includes: Finished Goods Inventory Cost per Direct Labor Budget Unit Manufacturing Overhead Budget Sales Budget Selling & Administrative Expense Schedule of Expected Cash Budget Collections Cash Budget Production Budget Budgeted Income Statement Raw Materials Purchases Budget Budgeted Balance Sheet Each schedule should be on a separate worksheet as appears in your template. Complete the tan shaded areas of the template only. All of your spreadsheets must be formula driven! 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 referenced values or the input values. (The exception is the inputs tab and the goal seek value on the last tab.) 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 click 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. As you work, compare your spreadsheet against the check figures that follow. This will allow you to ensure you are on the right track and catch your mistakes early so that you aren't hunting through the entire file if you realize you have mistakes at the end. These tabs all build on one another so catching your mistakes early will make this a much smoother process. CHECK FIGURES: TAB FG Inventory Cost per Unit Sales Budget Expected Cash Collections Production Budget RM Budget CELL Total FG Inventory Cost per Unit Total Sales for Quarter Total Cash Collections for Quarter Production in Units for December Production in Units for Quarter Raw Materials Purchases in October Raw Materials Purchases in Quarter DL Budget S&A Budget Total Direct Labor Cost for Quarter Total Overhead Disbursements for Quarter Total S&A Disbursements for Quarter Cash Budget Ending Cash Balance for October OH Budget Ending Cash Balance for Quarter Income Statement Contribution Margin Net Income Balance Sheet Total Assets Goal Seek VALUE $40.7 5 $11,900,000.00 $10,875,000.00 54,760 118,910 $418,287.80 $1,681,429.4 0 $1,783,650.0 0 $1,447,142.5 0 $916,480.0 0 $1,110,595.3 7 $4,886,087.2 3 $6,173,720.0 0 $6,001,320.00 $6,902,663.7 3 $4.5 5 When you are done, compare your file against the project rubric on the next page! Once you are happy with your finished project, upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version. Good luck! Have fun. I hope you enjoy this project! Evaluation Rubric that will be used to grade your project Point s Possi ble Have correct answers that match the following check figures: Total Sales in December = $5,500,000 1 Cash Collections in October = $2,475,000 1 Desired Units of Ending Finished Goods Inventory for the Quarter = 1,410 Sweaters 1 Raw Material Purchases in December = $758,594.20 1 Total Direct Labor Cost in October = $442,800 1 Total Overhead Disbursements in December = $658,430 1 November Ending Cash Balance = $2,429,404.41 1 Net Income for the Quarter = $6,001,320 1 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $6,902,663.73 1 Goal seek highest wool cost per yard = $4.55 1 15 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) Your Scor e Your worksheet must pass our tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value before moving on to the next test). On your input tab we will: Change the number of workers required to 5, and net income should become $3,323,820. 5 Change the cash payment of raw materials to 50% in month of sale and 50% in next month, and the ending cash balance for the quarter on the cash budget is $5,151,595.20. 5 Change units sold in December to 10,000 and the retained earnings on the balance sheet becomes $4,454,274.60. 5 Total Score Comments 40 Spring 2020 Spreadsheet Project Name: Nancy Vue Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020 30,000 Sales in Units (Sweaters) Selling Price per Sweater $ Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the Following Month December 2020 55,000 January 2021 47,000 February 2021 32,000 100.00 30% 50% 20% Inventory Policy Ending FG Inventory Requirement Ending FG Inventory, September 30 , 2020 Product Input Expenses Direct Materials Ending RM Inventory, September 30, 2020 Yards of Wool Required per Sweater Raw Materials Cost per Yard of Wool Ending RM Inventory Requirement November 2020 34,000 3% of next months unit sweater sales 1,500 sweaters $ Wool Purchases Paid for in the Month of Purchase following the Purchase 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs 85% 15% Direct NumberLabor of Workers Required for the Making of Each LaborSweater Hours Required per Worker per Unit of FG (Sweater) Labor Cost per Hour $ Manufacturing Overhead Variable Manufacturing Overhead Fixed Manufacturing OverheadOverhead (included Noncash Fixed Manufacturing in above) $ 11.75 per sweater month (Oct.) $ 30,200.00 per month $ 10,250.00 (Oct.) Selling & Administrative Expenses Variable S&A Fixed S&A Noncash Fixed S&A (included in above) $ 150,000.00 per unit sold $ 75,000.00 per month $ 28,929.60 per month Factory Update & Cash Flow Factory Update (PP&E) $ 400,500.00 paid on October 31, 2020 Principle Borrowed on October 1, 2020 PrinciplePayment Repaid on 30, Interest onNovember Borrowings in2020 October & November $ 300,000.00 $ 300,000.00 $ 9,000.00 per month (paid in following month) 2 workers 0.5 hours 15.00 per hour $ 30,750.00 per month (Nov. & beyond) $ 15,750.00 per month (Nov. & beyond) Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 ASSETS Cash $150,000.00 Accounts Receivable $ 75,000.00 Raw Materials Inventory $ 28,929.60 Finished Goods Inventory $ 61,125.00 PP&E, net $487,000.00 TOTAL ASSETS $802,054.60 LIABILITIES & EQUITIES Accounts Payable $ 14,500.00 Notes Payable $ Interest Payable $ TOTAL LIABILITIES $ 14,500.00 Retained Earnings $787,554.60 TOTAL LIABILITIES & EQ $802,054.60 Finished Goods Inventory Cost per Unit Per Unit (Sweater) Direct Material (wool & other material $ 14.00 Direct Labor $ 15.00 Variable Overhead $ 11.75 TOTAL FG INVENTORY COST PER $ 40.75 Sales Budget 2020 October November December QUARTER Unit Sales 30,000 34,000 55,000 119,000 Selling Price per Un$ 100.00 $ 100.00 $ 100.00 $ 100.00 TOTAL SALES ### ### ### ### Schedule of Expected Cash Collections 2020 October November Cash from Current Month Cash Sale $ 900,000.00 $ 1,020,000.00 Cash from Current Month Credit Sal $ 1,500,000.00 $ 1,700,000.00 Cash from Prior Month Credit Sales $ 75,000.00 $ 600,000.00 TOTAL CASH COLLECTIONS ### ### ash Collections 2020 December QUARTER $ 1,650,000.00 $ 3,570,000.00 $ 2,750,000.00 $ 5,950,000.00 $ 680,000.00 $ 1,355,000.00 ### ### Production Budget 2020 October November December Sales in Units 30,000 34,000 55,000 Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory 1,500 PRODUCTION IN UNITS Budget QUARTER 118,910 2021 January February Raw Materials Purchases Budget October Units (Sweaters) Produced Total Yards of Wool Used in Production Plus: Desired Wool in Ending Inventory Total Wool Required Less: Wool in Beginning Inventory RAW MATERIALS PURCHASES (UNITS) Cost per yard RAW MATERIALS PURCHASES (COST) 2020 November chases Budget 2020 December QUARTER 2021 January Direct Labor Budget October Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST 2020 November December QUARTER Manufacturing Overhead Budget 2020 October Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS November ad Budget 2020 December QUARTER Selling & Administrative Expense Budget October Unit Sales Variable S&A per Unit Total Variable S&A Fixed S&A TOTAL S&A EXPENSE Noncash S&A Expenses TOTAL S&A DISBURSEMENTS 2020 November December nse Budget QUARTER Cash Budget October Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior Direct Material Current Direct Labor Overhead S&A Factory Update Total Cash Disbursements Borrowings Interest Repayments ENDING CASH BALANCE 2020 November December QUARTER Lexie's Wool Sweaters Budgeted Income Statement For the Quarter Ending December 31, 2020 Sales Variable Expenses: Variable Manufacturing Expense Variable S&A Expense Contribution Margin Fixed Expenses: Fixed Manufacturing Expense Fixed S&A Expense Net Operating Income Interest Expense Net Income Lexie's Wool Sweaters Budgeted Balance Sheet As of December 31, 2020 ASSETS LIABILITIES & EQUITIES Cash Accounts Payable Accounts Receivable Notes Payable Raw Materials Inventory Interest Payable Finished Goods Inventory TOTAL LIABILITIES PP&E, net Retained Earnings TOTAL LIABILITIES & EQUITIES TOTAL ASSETS S & EQUITIES GOAL SEEK: Highest wool cost per yard while meeting goal of $5,500,000 in net income