Accounting 285 Fall 2019 Excel Spreadsheet Project DUE FRIDAY, NOV 8th @ 5:00 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 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 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 talo! 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! $12,700 Apple Annie's Balance Sheet As of September 30, 2019 ASSETS LIABILITIES & EQUITIES Cash $130,000 Accounts Payable Accounts Receivable $183,000 Notes Payable Raw Materials Inventory $3,237 Interest Payable Finished Goods $7,896 TOTAL LIABILITIES Inventory PP&E, net $526,000 Retained Earnings TOTAL LIABILITIES & TOTAL ASSETS $850,133 EQUITIES $12,700 $837,433 $850,133 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 October November December Q UARTER Unit Sales Selling Price per Unit TOTAL SALES Accounting 285 Fall 2019 Excel Spreadsheet Project DUE FRIDAY, NOV 8th @ 5:00 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 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 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 talo! 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! $12,700 Apple Annie's Balance Sheet As of September 30, 2019 ASSETS LIABILITIES & EQUITIES Cash $130,000 Accounts Payable Accounts Receivable $183,000 Notes Payable Raw Materials Inventory $3,237 Interest Payable Finished Goods $7,896 TOTAL LIABILITIES Inventory PP&E, net $526,000 Retained Earnings TOTAL LIABILITIES & TOTAL ASSETS $850,133 EQUITIES $12,700 $837,433 $850,133 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 October November December Q UARTER Unit Sales Selling Price per Unit TOTAL SALES