I need help finding the cash budget, the cash budget picture will be added at the bottom.
Accounting 285 Fall 2019 Excel Spreadsheet Project DUE THURS., NOV 7th @ 11:59 PM via CANVAS APPLE ANNIE'S Complete this assignment individually. Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Apple Annie's is a small business that makes delicious apple pies to sell using apples from their own orchard. The information below pertains to the company's budgeting process during their busiest time of year. Each pie is considered a unit of finished good. Budgeted sales in units (pies) are as follows: October November December January February 56,000 pies 71,000 pies 64,000 pies 1 32,000 pies 36,000 pies Each pie sells for $12.00. The company's collection pattern is as follows: o 40% of sales are cash sales which are collected immediately o 45% of sales are collected in the month of sale o 15% of sales are collected in the month following the sale The company desires to have very little finished goods inventory on hand in order to offer customers the freshest product possible. At the end of each month, ending inventory is 2% of the following month's budgeted sales in units. On September 30, the company had 1,120 units on hand. Each pie requires one crust. The cost per crust and other ingredients used is $1.15. Annie's desires to have ingredients on hand at the end of each month equal to 5% of the following month's production needs. On September 30, the company had 2,815 crusts on hand. The company's payment pattern for raw materials is as follows: o 90% of the month's ingredient purchases are paid for in the month of purchase o 10% is paid for in the month following the purchase The process to make each pie requires 3 kitchen staff members contributing to the process with each member contributing 6 minutes (0.1 hours) of labor time. The kitchen staff are currently being paid $15/hour. Wages are paid in the month incurred. Variable manufacturing overhead is $1.40 per pie. Fixed manufacturing overhead is $20,500 per month including $8,500 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. Variable selling and administrative expenses are $1.75 per pie sold. Fixed selling and administrative expense is $19,400 per month including $ 9,400 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Annie's borrowed $180,000 on Oct. 1, 2019, to prepare for a kitchen update. The full $180,000 was repaid on Nov. 30, 2019, but Annie's paid two interest payments of $1,050 for the borrowings. The kitchen update cost $229,500 which was paid on Oct. 31. 2019. This addition to depreciable PP&E caused the fixed manufacturing overhead to raise to $21,775 per month in November with $9,775 being non-cash. The Company uses variable costing in its budgeted income statement and its balance sheet. Additionally, Apple Annie's has the following balance sheet as of September 30, 2019: Apple Annie's Balance Sheet As of September 30, 2019 LIABILITIES & EQUITIES $130,000 Accounts Payable $183,000 Notes Payable $3,237 Interest Payable $7,896 TOTAL LIABILITIES $526,000 Retained Earnings $850,133 TOTAL LIABILITIES & EQUITIES ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS $12,700 $0 $12,700 $837,433 $850,133 Requirements 1. Enter your name at the top of the INPUTS tab. 2. Prepare a master budget for the quarter ended December 31, 2019 that includes: Finished Goods Inventory Cost per Unit, Sales Budget, Schedule of Expected Cash Collections, Production Budget, Raw Materials Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Expense Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 11 schedules). Each schedule should be on a separate worksheet as appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an input page you must use. Complete the shaded areas of the template only. All of your spreadsheets must be formula driven from the input tab! 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 goal seek value on the last tab. See item #3.) 3. Apple Annie's appreciates their hardworking and loyal kitchen staff, and ideally, the wage rate could be raised. Use Goal Seek to find the highest wage rate that could be paid to all kitchen staff while still meeting a net income target of $300,000 for the quarter. HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-If Analysis. If you are using an old version of Excel, you can find goal seek by going to the Tools menu. Manually enter your answer on the Goal Seek tab and then change the wage rate back to $15.00 on the INPUTS tab. 4. When you are done, compare your file against the project rubric below! 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 assess your project Have correct answers that match the following check figures: Possible Your Score Total Sales for the Quarter = $2,292,000 Cash Collections in October = $754,200 Desired Units of Finished Goods Ending Inventory for the Quarter = 640 pies Raw Material Purchases in December = $71,065 Total Direct Labor Cost in October = $253,350 Total Overhead Disbursements for the Quarter = $302,728 November Ending Cash Balance = $310,922 Net Income for the Quarter = $486,850 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $1,331,390 Goal seek highest labor wage rate is $18.26 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 selling price to $15.00, and net income should become $1,059,850. 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 $539,009. Change units sold in December to 100,000 and the retained earnings on the balance sheet becomes $1,439,483. Total Score Comments Fall 2019 Excel Spreadsheet Project Name: Apple Annie's PROJECTED BUDGETING DATA 2019 October November December 2020 January February Sales: Sales in Units (pies) Selling Price per Pie 71,000 64,000 32,000 36,000 56,000 12.00 $ Collections: Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the following Month 40% 45% 15% Finished Goods Inventory: Pies Ending FG Inventory Requirement Beginning FG Inventory, October 1, 2019 2% of next month's unit pie sales 1,120 pies Raw Materials Inventory: Crust & Other Ingredients Beginning RM Inventory, October 1, 2019 Raw Materials Cost per Crust Ending RM Inventory Requirement Ingredients Paid for in the Month of Purchase Ingredients Paid for in the following Month 2,815 crusts $1.15 per crust 5% of next month's pie production needs 90% 10% Direct Labor: Number of Kitchen Staff Required for the Making of Each Pie Labor Hours Required per Kitchen Staff per Unit of FG (pie) Labor Cost per Hour 3 kitchen staff 0.1 hours 15.00 per hour $ Manfacturing Overhead: Variable OH Fixed OH Noncash Fixed OH (included in above) $ 1.40 per pie $ 20,500 per month (Oct.) $ $ 8,500 per month (Oct.) $ 21,775 per month (Nov. & Dec.) 9,775 per month (Nov. & Dec.) Selling & Administrative Expenses: Variable S&A Fixed S&A Noncash Fixed S&A (included in above) $ 1.75 per unit sold $ 19,400 per month $ 9,400 per month Cash Borrowings: Principle Borrowed on October 1, 2019 Principle Repaid on November 30, 2019 Interest Payment on Borrowings in October & November $180,000 $180,000 $ 1,050 per month (paid in following month) Other: Kitchen Update (PP&E) $229,500 paid on October 31, 2019 Apple Annie's Balance Sheet As of September 30, 2019 LIABILITIES & EQUITIES $130,000 Accounts Payable $183,000 Notes Payable $3,237 Interest Payable $7,896 TOTAL LIABILITIES $526,000 Retained Earnings $850,133 TOTAL LIABILITIES & EQUITIES $12,700 ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS So $0 $12,700 $837,433 $850,133 Cash Budget 2019 November December QUARTER October Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior Direct Material Current Direct Labor Overhead S&A Kitchen Update Total Cash Disbursements Borrowings Interest Repayments ENDING CASH BALANCE Accounting 285 Fall 2019 Excel Spreadsheet Project DUE THURS., NOV 7th @ 11:59 PM via CANVAS APPLE ANNIE'S Complete this assignment individually. Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Apple Annie's is a small business that makes delicious apple pies to sell using apples from their own orchard. The information below pertains to the company's budgeting process during their busiest time of year. Each pie is considered a unit of finished good. Budgeted sales in units (pies) are as follows: October November December January February 56,000 pies 71,000 pies 64,000 pies 1 32,000 pies 36,000 pies Each pie sells for $12.00. The company's collection pattern is as follows: o 40% of sales are cash sales which are collected immediately o 45% of sales are collected in the month of sale o 15% of sales are collected in the month following the sale The company desires to have very little finished goods inventory on hand in order to offer customers the freshest product possible. At the end of each month, ending inventory is 2% of the following month's budgeted sales in units. On September 30, the company had 1,120 units on hand. Each pie requires one crust. The cost per crust and other ingredients used is $1.15. Annie's desires to have ingredients on hand at the end of each month equal to 5% of the following month's production needs. On September 30, the company had 2,815 crusts on hand. The company's payment pattern for raw materials is as follows: o 90% of the month's ingredient purchases are paid for in the month of purchase o 10% is paid for in the month following the purchase The process to make each pie requires 3 kitchen staff members contributing to the process with each member contributing 6 minutes (0.1 hours) of labor time. The kitchen staff are currently being paid $15/hour. Wages are paid in the month incurred. Variable manufacturing overhead is $1.40 per pie. Fixed manufacturing overhead is $20,500 per month including $8,500 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. Variable selling and administrative expenses are $1.75 per pie sold. Fixed selling and administrative expense is $19,400 per month including $ 9,400 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Annie's borrowed $180,000 on Oct. 1, 2019, to prepare for a kitchen update. The full $180,000 was repaid on Nov. 30, 2019, but Annie's paid two interest payments of $1,050 for the borrowings. The kitchen update cost $229,500 which was paid on Oct. 31. 2019. This addition to depreciable PP&E caused the fixed manufacturing overhead to raise to $21,775 per month in November with $9,775 being non-cash. The Company uses variable costing in its budgeted income statement and its balance sheet. Additionally, Apple Annie's has the following balance sheet as of September 30, 2019: Apple Annie's Balance Sheet As of September 30, 2019 LIABILITIES & EQUITIES $130,000 Accounts Payable $183,000 Notes Payable $3,237 Interest Payable $7,896 TOTAL LIABILITIES $526,000 Retained Earnings $850,133 TOTAL LIABILITIES & EQUITIES ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS $12,700 $0 $12,700 $837,433 $850,133 Requirements 1. Enter your name at the top of the INPUTS tab. 2. Prepare a master budget for the quarter ended December 31, 2019 that includes: Finished Goods Inventory Cost per Unit, Sales Budget, Schedule of Expected Cash Collections, Production Budget, Raw Materials Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Expense Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 11 schedules). Each schedule should be on a separate worksheet as appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an input page you must use. Complete the shaded areas of the template only. All of your spreadsheets must be formula driven from the input tab! 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 goal seek value on the last tab. See item #3.) 3. Apple Annie's appreciates their hardworking and loyal kitchen staff, and ideally, the wage rate could be raised. Use Goal Seek to find the highest wage rate that could be paid to all kitchen staff while still meeting a net income target of $300,000 for the quarter. HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-If Analysis. If you are using an old version of Excel, you can find goal seek by going to the Tools menu. Manually enter your answer on the Goal Seek tab and then change the wage rate back to $15.00 on the INPUTS tab. 4. When you are done, compare your file against the project rubric below! 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 assess your project Have correct answers that match the following check figures: Possible Your Score Total Sales for the Quarter = $2,292,000 Cash Collections in October = $754,200 Desired Units of Finished Goods Ending Inventory for the Quarter = 640 pies Raw Material Purchases in December = $71,065 Total Direct Labor Cost in October = $253,350 Total Overhead Disbursements for the Quarter = $302,728 November Ending Cash Balance = $310,922 Net Income for the Quarter = $486,850 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $1,331,390 Goal seek highest labor wage rate is $18.26 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 selling price to $15.00, and net income should become $1,059,850. 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 $539,009. Change units sold in December to 100,000 and the retained earnings on the balance sheet becomes $1,439,483. Total Score Comments Fall 2019 Excel Spreadsheet Project Name: Apple Annie's PROJECTED BUDGETING DATA 2019 October November December 2020 January February Sales: Sales in Units (pies) Selling Price per Pie 71,000 64,000 32,000 36,000 56,000 12.00 $ Collections: Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the following Month 40% 45% 15% Finished Goods Inventory: Pies Ending FG Inventory Requirement Beginning FG Inventory, October 1, 2019 2% of next month's unit pie sales 1,120 pies Raw Materials Inventory: Crust & Other Ingredients Beginning RM Inventory, October 1, 2019 Raw Materials Cost per Crust Ending RM Inventory Requirement Ingredients Paid for in the Month of Purchase Ingredients Paid for in the following Month 2,815 crusts $1.15 per crust 5% of next month's pie production needs 90% 10% Direct Labor: Number of Kitchen Staff Required for the Making of Each Pie Labor Hours Required per Kitchen Staff per Unit of FG (pie) Labor Cost per Hour 3 kitchen staff 0.1 hours 15.00 per hour $ Manfacturing Overhead: Variable OH Fixed OH Noncash Fixed OH (included in above) $ 1.40 per pie $ 20,500 per month (Oct.) $ $ 8,500 per month (Oct.) $ 21,775 per month (Nov. & Dec.) 9,775 per month (Nov. & Dec.) Selling & Administrative Expenses: Variable S&A Fixed S&A Noncash Fixed S&A (included in above) $ 1.75 per unit sold $ 19,400 per month $ 9,400 per month Cash Borrowings: Principle Borrowed on October 1, 2019 Principle Repaid on November 30, 2019 Interest Payment on Borrowings in October & November $180,000 $180,000 $ 1,050 per month (paid in following month) Other: Kitchen Update (PP&E) $229,500 paid on October 31, 2019 Apple Annie's Balance Sheet As of September 30, 2019 LIABILITIES & EQUITIES $130,000 Accounts Payable $183,000 Notes Payable $3,237 Interest Payable $7,896 TOTAL LIABILITIES $526,000 Retained Earnings $850,133 TOTAL LIABILITIES & EQUITIES $12,700 ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS So $0 $12,700 $837,433 $850,133 Cash Budget 2019 November December QUARTER October Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior Direct Material Current Direct Labor Overhead S&A Kitchen Update Total Cash Disbursements Borrowings Interest Repayments ENDING CASH BALANCE
