Please help with this accounting projecting.

Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills.

Case: Apple Annies is a small business that makes delicious apple pies to sell using apples from their own orchard. The information below pertains to the companys budgeting process during their busiest time of year.

Thank You so much.

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 Accounting 285 Fall 2019 DUE THURS., NOV 7th @11:59 PM via CANVAS 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. Excel Spreadsheet Project 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. APPLE ANNIE'S Additionally, Apple Annie's has the following balance sheet as of September 30, 2019: Apple Annie's Balance Sheet Complete this assignment individually As of September 30, 2019 ASSETS LIABILITIES & EQUITIES Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop Cash $130,000 $183,000 $3,237 $7,896 Accounts Payable $12,700 $0 your spreadsheet skills. Notes Payable Interest Payable Accounts Receivable Raw Materials Inventory $0 $12,700 $837,433 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 Finished Goods Inventory TOTAL LIABILITIES $526,000 $850,133 Retained Earnings PP&E, net $850,133 TOTAL ASSETS TOTAL LIABILITIES & EQUITIES Each pie is considered a unit of finished good Budgeted sales in units (pies) are as follows Requirements December February October November January 1. Enter your name at the top of the INPUTS tab. 56,000 pies 71,000 pies 32,000 pies 36,000 pies 64,000 pies 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 2. 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 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. 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 eadch 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. 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.) 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. 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. The company's payment pattern for raw materials is as follows: 90% of the month's ingredient purchases are paid for in the month of purchase 10% is paid for in the month following the purchase o HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-lf 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. 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. 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. 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 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 1 Cash Collections in October = $754,200 Desired Units of Finished Goods Ending Inventory for the Quarter 640 pies 1 Raw Material Purchases in December $71,065 1 Total Direct Labor Cost in October $253,35 1 Total Overhead Disbursements for the Quarter = $302,728 1 November Ending Cash Balance $310,922 1 $486,850 Net Income for the Quarter = 1 Total Assets and Total Liabilities & Equity at the End of the Quarter both $1,331,390 1 Goal seek highest labor wage rate is $18.26 1 15 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 telb we will Change the selling price to $15.00, and net income should become $1,059,850 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 $539,009 5 5 Change units sold in December to 100,000 and the retained earnings on the balance sheet becomes $1439,483 Total Score 40 Comments PROJECTED BUDGETING DATA 2019 2020 November December January February October Sales: Sales in Units (pies) Selling Price per Pie 56,000 71,000 64,000 32,000 36,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 Labor Hours Required per Kitchen Staff per Unit of FG Labor Cost per Hour 3 kitchen staff 0.1 hours $ 15.00 per hour Manfacturing Overhead: Variable OH 1.40 per pie 21,775 per month (Nov. & 9,775 per month (Nov. & $ 20,500 per month $ 8,500 per month Fixed OH $ $ Noncash Fixed OH (included in above) 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 monthr Cash Borrowings: Principle Borrowed on October 1, 2019 Principle Repaid on November 30, 2019 Interest Payment on Borrowings in October & November $ 1,050 per month (paid in following month) Other: Kitchen Update (PP&E) paid on October 31, 2019 Finished Goods Inventory Cost per Unit Per Unit (pie) Direct Material (crust & other ingredients) Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Note: Computing the cost of a unit of inventory will be useful later on when you have to do a budgeted income statement & budgeted balance sheet. Sales Budget 2019 December October November QUARTER Unit Sales Selling Price per Unit TOTAL SALES Schedule of Expected Cash Collections 2019 October November December QUARTER Cash from Current Month Cash Sales Cash from Current Month Credit Sales Cash from Prior Month Credit Sales TOTAL CASH COLLECTIONS Production Budget 2019 2020 February October November December QUARTER January Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Note: The amounts from January & February are necessary in order to complete December on this budget and for the Raw Materials Budget. Raw Materials Purchases Budget 2020 2019 October November December QUARTER January Units (pies) Produced Total Crusts Used in Production Plus: Desired Crusts in Ending Inventory Total Crusts Required Less: Crusts in Beginning Inventory RAW MATERIALS PURCHASES (UNITS) Cost per Crust RAW MATERIALS PURCHASES (COST) Direct Labor Budget 2019 October November December QUARTER Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Manufacturing Overhead Budget 2019 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 2019 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 2019 October November December QUARTER 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 Apple Annie's Budgeted Income Statement For the Quarter Ending December 31, 2019 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 Apple Annie's Budgeted Balance Sheet As of December 31, 2019 LIABILITIES & EQUITIES ASSETS Cash Accounts Receivable Accounts Payable Notes Payable Raw Materials Inventory Interest Payable Finished Goods Inventory TOTAL LIABILITIES PP&E, net TOTAL ASSETS Retained Earnings TOTAL LIABILITIES & EQUITIES GOAL SEEK: Highest Labor Wage Rate to Meet $300,000 Net Income Goal 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 Accounting 285 Fall 2019 DUE THURS., NOV 7th @11:59 PM via CANVAS 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. Excel Spreadsheet Project 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. APPLE ANNIE'S Additionally, Apple Annie's has the following balance sheet as of September 30, 2019: Apple Annie's Balance Sheet Complete this assignment individually As of September 30, 2019 ASSETS LIABILITIES & EQUITIES Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop Cash $130,000 $183,000 $3,237 $7,896 Accounts Payable $12,700 $0 your spreadsheet skills. Notes Payable Interest Payable Accounts Receivable Raw Materials Inventory $0 $12,700 $837,433 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 Finished Goods Inventory TOTAL LIABILITIES $526,000 $850,133 Retained Earnings PP&E, net $850,133 TOTAL ASSETS TOTAL LIABILITIES & EQUITIES Each pie is considered a unit of finished good Budgeted sales in units (pies) are as follows Requirements December February October November January 1. Enter your name at the top of the INPUTS tab. 56,000 pies 71,000 pies 32,000 pies 36,000 pies 64,000 pies 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 2. 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 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. 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 eadch 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. 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.) 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. 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. The company's payment pattern for raw materials is as follows: 90% of the month's ingredient purchases are paid for in the month of purchase 10% is paid for in the month following the purchase o HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-lf 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. 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. 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. 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 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 1 Cash Collections in October = $754,200 Desired Units of Finished Goods Ending Inventory for the Quarter 640 pies 1 Raw Material Purchases in December $71,065 1 Total Direct Labor Cost in October $253,35 1 Total Overhead Disbursements for the Quarter = $302,728 1 November Ending Cash Balance $310,922 1 $486,850 Net Income for the Quarter = 1 Total Assets and Total Liabilities & Equity at the End of the Quarter both $1,331,390 1 Goal seek highest labor wage rate is $18.26 1 15 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 telb we will Change the selling price to $15.00, and net income should become $1,059,850 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 $539,009 5 5 Change units sold in December to 100,000 and the retained earnings on the balance sheet becomes $1439,483 Total Score 40 Comments PROJECTED BUDGETING DATA 2019 2020 November December January February October Sales: Sales in Units (pies) Selling Price per Pie 56,000 71,000 64,000 32,000 36,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 Labor Hours Required per Kitchen Staff per Unit of FG Labor Cost per Hour 3 kitchen staff 0.1 hours $ 15.00 per hour Manfacturing Overhead: Variable OH 1.40 per pie 21,775 per month (Nov. & 9,775 per month (Nov. & $ 20,500 per month $ 8,500 per month Fixed OH $ $ Noncash Fixed OH (included in above) 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 monthr Cash Borrowings: Principle Borrowed on October 1, 2019 Principle Repaid on November 30, 2019 Interest Payment on Borrowings in October & November $ 1,050 per month (paid in following month) Other: Kitchen Update (PP&E) paid on October 31, 2019 Finished Goods Inventory Cost per Unit Per Unit (pie) Direct Material (crust & other ingredients) Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Note: Computing the cost of a unit of inventory will be useful later on when you have to do a budgeted income statement & budgeted balance sheet. Sales Budget 2019 December October November QUARTER Unit Sales Selling Price per Unit TOTAL SALES Schedule of Expected Cash Collections 2019 October November December QUARTER Cash from Current Month Cash Sales Cash from Current Month Credit Sales Cash from Prior Month Credit Sales TOTAL CASH COLLECTIONS Production Budget 2019 2020 February October November December QUARTER January Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Note: The amounts from January & February are necessary in order to complete December on this budget and for the Raw Materials Budget. Raw Materials Purchases Budget 2020 2019 October November December QUARTER January Units (pies) Produced Total Crusts Used in Production Plus: Desired Crusts in Ending Inventory Total Crusts Required Less: Crusts in Beginning Inventory RAW MATERIALS PURCHASES (UNITS) Cost per Crust RAW MATERIALS PURCHASES (COST) Direct Labor Budget 2019 October November December QUARTER Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Manufacturing Overhead Budget 2019 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 2019 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 2019 October November December QUARTER 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 Apple Annie's Budgeted Income Statement For the Quarter Ending December 31, 2019 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 Apple Annie's Budgeted Balance Sheet As of December 31, 2019 LIABILITIES & EQUITIES ASSETS Cash Accounts Receivable Accounts Payable Notes Payable Raw Materials Inventory Interest Payable Finished Goods Inventory TOTAL LIABILITIES PP&E, net TOTAL ASSETS Retained Earnings TOTAL LIABILITIES & EQUITIES GOAL SEEK: Highest Labor Wage Rate to Meet $300,000 Net Income Goal