The final answers for checking:
The information:
The templates for the answers needed to be done:
3rd 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 Notes Payable Raw Materials Inventory $ 28,929.60 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.60 TOTAL LIABILITIES & EQUITY $ 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 tan 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 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 CELL VALUE FG Inventory Cost per Unit Total FG Inventory Cost per Unit $40.75 Sales Budget Total Sales for Quarter $11,900,000.00 Expected Cash Collections Total Cash Collections for Quarter $10,875,000.00 Production Budget Production in Units for December 54,760 Production in Units for Quarter 118,910 RM Budget Raw Materials Purchases in October $418,287.80 Raw Materials Purchases in Quarter $1,681,429.40 DL Budget Total Direct Labor Cost for Quarter $1,783,650.00 OH Budget | Total Overhead Disbursements for Quarter $1,447, 142.50 S&A Budget Total S&A Disbursements for Quarter $916,480.00 Cash Budget Ending Cash Balance for October $1,110,595.37 Ending Cash Balance for Quarter $4,886,087.23 Income Statement Contribution Margin $6,173,720.00 Net Income $6,001,320.00 Balance Sheet Total Assets $6,902,663.73 Goal Seek $4.55 When you are done, compare your file against the project rubric on the next page Have correct answers that match the following check figures: Total Sales in December = $5,500,000 Cash Collections in October = $2,475,000 Desired Units of Ending Finished Goods Inventory for the Quarter = 1,410 Sweaters Raw Material Purchases in December = $758,594.20 Total Direct Labor Cost in October = $442,800 Total Overhead Disbursements in December = $658,430 November Ending Cash Balance = $2,429,404.41 Net Income for the Quarter = $6,001,320 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $6,902,663.73 Goal seek highest wool cost per yard = $4.55 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) 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. 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 Change units sold in December to 10,000 and the retained earnings on the balance sheet becomes $4,454,274.60. Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020 30,000 November 2020 34,000 December 2020 55,000 January 2021 47,000 February 2021 32,000 Sales in Units (Sweaters) Selling Price per Sweater $ 100.00 Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the Following Month 30% 50% 20% Inventory Policy Ending FG Inventory Requirement Ending FG Inventory, September 30, 2020 3% of next months unit sweater sales 1,500 sweaters 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 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 LIABILITIES & EQUITIES $ 150,000.00 Accounts Payable $ 14,500.00 $ 75,000.00 Notes Payable $ 28,929.60 Interest Payable $ 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 | Finished Goods Inventory Cost per Unit Per Unit (Sweater) Direct Material (wool & other materials) Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Sales Budget 2020 November December October QUARTER Unit Sales Selling Price per Unit TOTAL SALES member QUARTER Schedule of Expected Cash Collections 2020 October December Cash from Current Month Cash Sales Cash from Current Month Credit Sales Cash from Prior Month Credit Sales TOTAL CASH COLLECTIONS Production Budget 2020 December 2021 January February October November QUARTER Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Raw Materials Purchases Budget 2020 October November December Q UARTER 2021 January 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) Direct Labor Budget 2020 October November December 1 QUARTER Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Manufacturing Overhead Budget 2020 October November December | QUARTER Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS Selling & Administrative Expense Budget 2020 October November December QUARTER 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 Cash Budget 2020 December October November QUARTER 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 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 LIABILITIES & EQUITIES Accounts Payable Notes Payable ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES GOAL SEEK: Highest wool cost per yard while meeting goal of $5,500,000 in net income 3rd 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 Notes Payable Raw Materials Inventory $ 28,929.60 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.60 TOTAL LIABILITIES & EQUITY $ 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 tan 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 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 CELL VALUE FG Inventory Cost per Unit Total FG Inventory Cost per Unit $40.75 Sales Budget Total Sales for Quarter $11,900,000.00 Expected Cash Collections Total Cash Collections for Quarter $10,875,000.00 Production Budget Production in Units for December 54,760 Production in Units for Quarter 118,910 RM Budget Raw Materials Purchases in October $418,287.80 Raw Materials Purchases in Quarter $1,681,429.40 DL Budget Total Direct Labor Cost for Quarter $1,783,650.00 OH Budget | Total Overhead Disbursements for Quarter $1,447, 142.50 S&A Budget Total S&A Disbursements for Quarter $916,480.00 Cash Budget Ending Cash Balance for October $1,110,595.37 Ending Cash Balance for Quarter $4,886,087.23 Income Statement Contribution Margin $6,173,720.00 Net Income $6,001,320.00 Balance Sheet Total Assets $6,902,663.73 Goal Seek $4.55 When you are done, compare your file against the project rubric on the next page Have correct answers that match the following check figures: Total Sales in December = $5,500,000 Cash Collections in October = $2,475,000 Desired Units of Ending Finished Goods Inventory for the Quarter = 1,410 Sweaters Raw Material Purchases in December = $758,594.20 Total Direct Labor Cost in October = $442,800 Total Overhead Disbursements in December = $658,430 November Ending Cash Balance = $2,429,404.41 Net Income for the Quarter = $6,001,320 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $6,902,663.73 Goal seek highest wool cost per yard = $4.55 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) 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. 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 Change units sold in December to 10,000 and the retained earnings on the balance sheet becomes $4,454,274.60. Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020 30,000 November 2020 34,000 December 2020 55,000 January 2021 47,000 February 2021 32,000 Sales in Units (Sweaters) Selling Price per Sweater $ 100.00 Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the Following Month 30% 50% 20% Inventory Policy Ending FG Inventory Requirement Ending FG Inventory, September 30, 2020 3% of next months unit sweater sales 1,500 sweaters 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 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 LIABILITIES & EQUITIES $ 150,000.00 Accounts Payable $ 14,500.00 $ 75,000.00 Notes Payable $ 28,929.60 Interest Payable $ 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 | Finished Goods Inventory Cost per Unit Per Unit (Sweater) Direct Material (wool & other materials) Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Sales Budget 2020 November December October QUARTER Unit Sales Selling Price per Unit TOTAL SALES member QUARTER Schedule of Expected Cash Collections 2020 October December Cash from Current Month Cash Sales Cash from Current Month Credit Sales Cash from Prior Month Credit Sales TOTAL CASH COLLECTIONS Production Budget 2020 December 2021 January February October November QUARTER Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Raw Materials Purchases Budget 2020 October November December Q UARTER 2021 January 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) Direct Labor Budget 2020 October November December 1 QUARTER Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Manufacturing Overhead Budget 2020 October November December | QUARTER Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS Selling & Administrative Expense Budget 2020 October November December QUARTER 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 Cash Budget 2020 December October November QUARTER 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 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 LIABILITIES & EQUITIES Accounts Payable Notes Payable ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES GOAL SEEK: Highest wool cost per yard while meeting goal of $5,500,000 in net income
