ACC 255 - Managerial Accounting Budget Project Spring 2021-9:30am Class The Chippewa Chocolates (CC) Company began business on January 1, 2020. After its first year of operation, CC's president, Allen Hale, is now trying to prepare the company's master budget for the first two months (January and February) of 2021. Since you are his good friend and an accounting student, Mr. Hale has asked you to prepare the budget. Based on the budgets from 2020, Mr. Hale has gathered the following information: Projected Sales for January 2021 S128,000 Average Monthly Sales Increase 5% Average Monthly Salary Expenses $ 33,400 Average Monthly Rent Expenses $ 6,900 Average Monthly Utility Expenses $ 5,870 Average Monthly Misc. Expense S 2,650 Supplies Expense-percent of sales 29 Commission-percent of sales 6% Begin your project by carefully reading all instructions given here and reviewing the balance sheets and budget worksheets contained in the Excel workbook Fill in the data table in the upper left hand corner of the "Budgets" worksheet (found on the second tab in the Excel workbook) with the values above and other values found in the assumptions given below. Using this information, prepare Chippewa Chocolates' master budget for the first two months of 2021. All amounts should be rounded to whole dollars as necessary. Apply the following assumptions: a) Customers typically pay 35% of total sales in cash and put the remaining 65% on account. The company expects to collect 100% of the credit sales in the month following the sale. b) The cost of goods sold is 40% of budgeted sales and the company desires to maintain a minimum ending inventory equal to 15% of the next month's cost of goods sold. All purchases are made on account c) The company pays 80% of inventory purchases in the month of purchase and the remaining 20% in the following month d) Depreciation expense on existing equipment is 56,300) each month. In addition, CC will spend $160,000 on January 1 for additional equipment. The equipment is expected to have a $4,000 salvage value and a five-yeur (60-month) useful life. e) Salaries, utilities, and sales commissions are paid the month other they are incurred all other expenses are paid in the month in which they are incurred ) The company borrows and repays funds on its credit line in increments of S1,000 on the last day of each month as necessary to maintain its targeted $35,000 cash balance. It pays interest of 3% per month in cash on the last day of the month on cash borrowed in the prior month ACC 255 - Managerial Accounting Budget Project Spring 2021 - 9:30am Class The six master budget documents should refer to the values in the data table the first time an item is used and to other budgets thereafter. Please make sure that all original underlines and borders are kept in place. Do not add/delete rows or columns. In addition, any blanks in the budget description columns must be filled in. For example, in the Inventory Purchases Budger, under "Cost of Goods Sold", it says "Add: "You need to say what you're adding there, Under no circumstances should your budgets or February Balance Sheet contain just numbers, only formulas, in the format view. NOTE: The cells in the December Balance Sheet and the Borrow/Repayment cells in the Cash Budget are protected and therefore, cannot be changed. You are, however, able to reference these cells in your formulas Use the templates in the Excel workbook to prepare all Budgets and the February Balance Sheet as outlined in the spreadsheet B D E G H ACC 255 BUDGET PROJECT. SPRING 2021 - 9:30AM CLASS Budget information: CHIPPEWA CHOCOLATES Projected sales for January, 2021 Selling and Administrative Expenses Budget Average Monthly percantage of sales increase: For the Two Months Ending February 28, 2021 Average monthly salary expense: Average monthly rent expense: January February Average monthly utility expense Variable costs: Average monthly misc expense: Supplies expense as a percentage of sales: Commission expense as a percentage of sales: Total variable costs 1 Cash sales in month of sale Fixed costs 2. Cost of goods sold-percentage of sales 3. Desired ending inventory percent of next months COGS 4. Payments of inventory in month of purchase 5 Monthly depreciation for 2020 16 New equipment cost 17 Salvage value Total fixed costs: 78 Useful Life (months) Total S & A Expense 19. Minimum required cash balance 20 Monthly interest rate 21 CHIPPEWA CHOCOLATES 22 CHIPPEWA CHOCOLATES Schedule of Cash Payments for S&A Expenses 23 Sales Budget For the Two months Ending February 28, 2021 24 For the Three Months Ending March 31, 2021 25 January February 26 January February March Variable costs: 27 Cash Sales 28 Credit Sales 29 Total Sales Total variable costs 30 Fixed costs 31 32 CHIPPEWA CHOCOLATES 33 Schedule of Cash Receipts 34 For the Two Months Ending February 28, 2021 35 Total fixed costs 36 January February Cash paid for S&A Expenses 37 Cash Sales 38 Collection of Credit Sales (A/R) 39 Total Budgeted Cash Receipts CHIPPEWA CHOCOLATES 40 Budgeted Income Statement 41 For the Two Months Ending February 28 2021 Total Budgeted Cash Receipts CHIPPEWA CHOCOLATES Budgeted Income Statement For the Two Months Ending February 28, 2021 CHIPPEWA CHOCOLATES Inventory Purchases Budget For the Two Months Ending February 28, 2021 January February January February Sales Revenue Less: Gross Profit Less: S&A Expenses income before interest Cost of Goods Sold Add: Inventory Needed Interest expense Net Income Less: CHIPPEWA CHOCOLATES Cash Budget For the Two Months Ending February 28, 2021 January February 1 Budgeted Purchases 2 3 CHIPPEWA CHOCOLATES Schedule of Cash Payments 56 For the Two Months Ending February 28, 2021 57 58 January February 59. December Purchases 60 61 62 Cash paid for inventory 63 64 65 Beginning Cash Balance Add: Cash Available Less: Cash Paid for 67 50 69 70 Equipment purchase Interest Total Cash Paid Surplus/(Shortage) Borrowing Repayment Ending Cash Balance 72 23 24 2 3 Chippewa Chocolates Company Balance Sheet December 31, 2020 Assets $ $ 35,850 78,000 7,680 Liabilities Accounts Payable Inventory Salaries Payable Utilities Payable Commissions Payable Notes Payable Total Liabilities 9,696 31,500 6.480 7,200 $ 400,000 75,600 324,400 54,876 445 930 Stockholder's Equity Common Stock Retained Earnings Total abilities & Stock Equity 300.000 91,054 445.930 $ 4 5 6 7 Cash 8 Accounts Receivable 9 Inventory 10 Equipment 11 Less: Accum. Depr. 12 13 Total Assets 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Cash 28 Accounts Receivable 29 Inventory 30. Equipment 31 Less: Accum, Depr. 32 33 Total Assets 34 35 36 37 38 39 40 41 Chippewa Chocolates Company Balance Sheet February 28, 2021 Assets Liabilities Accounts Payable Inventory Salaries payable Utilities Payable Commissions Payable Notes Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Liabilities & Stock. Equity Variance check 14 45 Balancse Sheets Budgets ACC 255 - Managerial Accounting Budget Project Spring 2021-9:30am Class The Chippewa Chocolates (CC) Company began business on January 1, 2020. After its first year of operation, CC's president, Allen Hale, is now trying to prepare the company's master budget for the first two months (January and February) of 2021. Since you are his good friend and an accounting student, Mr. Hale has asked you to prepare the budget. Based on the budgets from 2020, Mr. Hale has gathered the following information: Projected Sales for January 2021 S128,000 Average Monthly Sales Increase 5% Average Monthly Salary Expenses $ 33,400 Average Monthly Rent Expenses $ 6,900 Average Monthly Utility Expenses $ 5,870 Average Monthly Misc. Expense S 2,650 Supplies Expense-percent of sales 29 Commission-percent of sales 6% Begin your project by carefully reading all instructions given here and reviewing the balance sheets and budget worksheets contained in the Excel workbook Fill in the data table in the upper left hand corner of the "Budgets" worksheet (found on the second tab in the Excel workbook) with the values above and other values found in the assumptions given below. Using this information, prepare Chippewa Chocolates' master budget for the first two months of 2021. All amounts should be rounded to whole dollars as necessary. Apply the following assumptions: a) Customers typically pay 35% of total sales in cash and put the remaining 65% on account. The company expects to collect 100% of the credit sales in the month following the sale. b) The cost of goods sold is 40% of budgeted sales and the company desires to maintain a minimum ending inventory equal to 15% of the next month's cost of goods sold. All purchases are made on account c) The company pays 80% of inventory purchases in the month of purchase and the remaining 20% in the following month d) Depreciation expense on existing equipment is 56,300) each month. In addition, CC will spend $160,000 on January 1 for additional equipment. The equipment is expected to have a $4,000 salvage value and a five-yeur (60-month) useful life. e) Salaries, utilities, and sales commissions are paid the month other they are incurred all other expenses are paid in the month in which they are incurred ) The company borrows and repays funds on its credit line in increments of S1,000 on the last day of each month as necessary to maintain its targeted $35,000 cash balance. It pays interest of 3% per month in cash on the last day of the month on cash borrowed in the prior month ACC 255 - Managerial Accounting Budget Project Spring 2021 - 9:30am Class The six master budget documents should refer to the values in the data table the first time an item is used and to other budgets thereafter. Please make sure that all original underlines and borders are kept in place. Do not add/delete rows or columns. In addition, any blanks in the budget description columns must be filled in. For example, in the Inventory Purchases Budger, under "Cost of Goods Sold", it says "Add: "You need to say what you're adding there, Under no circumstances should your budgets or February Balance Sheet contain just numbers, only formulas, in the format view. NOTE: The cells in the December Balance Sheet and the Borrow/Repayment cells in the Cash Budget are protected and therefore, cannot be changed. You are, however, able to reference these cells in your formulas Use the templates in the Excel workbook to prepare all Budgets and the February Balance Sheet as outlined in the spreadsheet B D E G H ACC 255 BUDGET PROJECT. SPRING 2021 - 9:30AM CLASS Budget information: CHIPPEWA CHOCOLATES Projected sales for January, 2021 Selling and Administrative Expenses Budget Average Monthly percantage of sales increase: For the Two Months Ending February 28, 2021 Average monthly salary expense: Average monthly rent expense: January February Average monthly utility expense Variable costs: Average monthly misc expense: Supplies expense as a percentage of sales: Commission expense as a percentage of sales: Total variable costs 1 Cash sales in month of sale Fixed costs 2. Cost of goods sold-percentage of sales 3. Desired ending inventory percent of next months COGS 4. Payments of inventory in month of purchase 5 Monthly depreciation for 2020 16 New equipment cost 17 Salvage value Total fixed costs: 78 Useful Life (months) Total S & A Expense 19. Minimum required cash balance 20 Monthly interest rate 21 CHIPPEWA CHOCOLATES 22 CHIPPEWA CHOCOLATES Schedule of Cash Payments for S&A Expenses 23 Sales Budget For the Two months Ending February 28, 2021 24 For the Three Months Ending March 31, 2021 25 January February 26 January February March Variable costs: 27 Cash Sales 28 Credit Sales 29 Total Sales Total variable costs 30 Fixed costs 31 32 CHIPPEWA CHOCOLATES 33 Schedule of Cash Receipts 34 For the Two Months Ending February 28, 2021 35 Total fixed costs 36 January February Cash paid for S&A Expenses 37 Cash Sales 38 Collection of Credit Sales (A/R) 39 Total Budgeted Cash Receipts CHIPPEWA CHOCOLATES 40 Budgeted Income Statement 41 For the Two Months Ending February 28 2021 Total Budgeted Cash Receipts CHIPPEWA CHOCOLATES Budgeted Income Statement For the Two Months Ending February 28, 2021 CHIPPEWA CHOCOLATES Inventory Purchases Budget For the Two Months Ending February 28, 2021 January February January February Sales Revenue Less: Gross Profit Less: S&A Expenses income before interest Cost of Goods Sold Add: Inventory Needed Interest expense Net Income Less: CHIPPEWA CHOCOLATES Cash Budget For the Two Months Ending February 28, 2021 January February 1 Budgeted Purchases 2 3 CHIPPEWA CHOCOLATES Schedule of Cash Payments 56 For the Two Months Ending February 28, 2021 57 58 January February 59. December Purchases 60 61 62 Cash paid for inventory 63 64 65 Beginning Cash Balance Add: Cash Available Less: Cash Paid for 67 50 69 70 Equipment purchase Interest Total Cash Paid Surplus/(Shortage) Borrowing Repayment Ending Cash Balance 72 23 24 2 3 Chippewa Chocolates Company Balance Sheet December 31, 2020 Assets $ $ 35,850 78,000 7,680 Liabilities Accounts Payable Inventory Salaries Payable Utilities Payable Commissions Payable Notes Payable Total Liabilities 9,696 31,500 6.480 7,200 $ 400,000 75,600 324,400 54,876 445 930 Stockholder's Equity Common Stock Retained Earnings Total abilities & Stock Equity 300.000 91,054 445.930 $ 4 5 6 7 Cash 8 Accounts Receivable 9 Inventory 10 Equipment 11 Less: Accum. Depr. 12 13 Total Assets 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Cash 28 Accounts Receivable 29 Inventory 30. Equipment 31 Less: Accum, Depr. 32 33 Total Assets 34 35 36 37 38 39 40 41 Chippewa Chocolates Company Balance Sheet February 28, 2021 Assets Liabilities Accounts Payable Inventory Salaries payable Utilities Payable Commissions Payable Notes Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Liabilities & Stock. Equity Variance check 14 45 Balancse Sheets Budgets