Accounting 285 excel assignment.

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. 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 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 QUARTER Unit Sales Selling Price per Unit TOTAL SALES c5 x fx D E F G H I Production Budget 2019 October November DecemberQUARTER 2020 February 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. 19 LS 2020 January Q UARTER Raw Materials Purchases Budget 2019 October November December 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) 14 15 C5 Copy formatting from one location and apply it to another Jx co G H QUARTER Direct Labor Budget 2019 October November December Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST C5 Paste x fx C D E F C QUARTER Manufacturing Overhead Budget 2019 October November December Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS C5 Name Box E F G H Selling & Administrative Expense Budget 2019 November December October 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 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 on msns C6 x fx ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Apple Annie's Budgeted Balance Sheet As of December 31, 2019 LIABILITIES & EQUITIES Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES 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. 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 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 QUARTER Unit Sales Selling Price per Unit TOTAL SALES c5 x fx D E F G H I Production Budget 2019 October November DecemberQUARTER 2020 February 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. 19 LS 2020 January Q UARTER Raw Materials Purchases Budget 2019 October November December 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) 14 15 C5 Copy formatting from one location and apply it to another Jx co G H QUARTER Direct Labor Budget 2019 October November December Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST C5 Paste x fx C D E F C QUARTER Manufacturing Overhead Budget 2019 October November December Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS C5 Name Box E F G H Selling & Administrative Expense Budget 2019 November December October 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 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 on msns C6 x fx ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Apple Annie's Budgeted Balance Sheet As of December 31, 2019 LIABILITIES & EQUITIES Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES