Backyard Bliss Inc. is a company that manufactures and sells outdoor benches. For planning and control purposes they utilize a quarterly master budget, which is usually developed at least nine months in advance of the budget year. Their fiscal year end is December 31. During January of 2020, Lisa Yang, the Backyard Bliss controller, spent some time putting together a sales forecast for the next budget year (January to December 2021). In February, Lisa's numbers really came in as she won the jackpot in the lottery. Shortly thereafter, the President of Backyard Bliss received Lisa's letter of resignation, which she sent from Bermuda. The President, desperately needing the budget completed, has approached you, a management accounting student, for help in preparing the budget for the coming fiscal year. Your conversations with the president and your investigations of the company's records have revealed the following information: 1. Lisa's sales forecast consisted of these few lines: For the year ended December 31, 2020: 26,000 units at $310.00 each For the year ended December 31, 2021: 30,000 units at $310.00 each For the year ended December 31, 2022: 33,000 units at $310.00 each 2. History has shown that sales are seasonal, with 55% of sales occurring in the second quarter, 25% in the third quarter, 10% in the first quarter, and the remaining 10% in the last quarter. 3. Because sales are seasonal, Backyard Bliss must rent an additional storage facility from March to June to house the additional finished benches on hand. The only related cost is a flat $2,000 per month, payable at the beginning of the month. 4. Sales are on a cash and credit basis, with 30% collected in cash. 60% of Credit sales are collected during the quarter of the sale, and 36% of credit sales are collected in the following quarter. The remainder are uncollectible. 5. Backyard Bliss has negotiated a line of credit with the bank such that they can borrow up to $400,000, in increments of $5,000, at an interest rate of 5%. Assume all borrowing takes place at the beginning of the quarter (interest is paid for the full quarter when borrowing takes place) and repayment takes place at the end of the quarter interest is paid for the full quarter that repayment is made.) Repayment must be in increments of $1,000. Creditors require a minimum bank balance of $5,000. 6. From previous experience, management has determined that an ending finished goods inventory equal to 10% of the next quarter's sales is required to meet customer demand. Opening finished goods inventory consists of 300 benches. 7. The primary raw material used is 4x6 cedar boards. Each bench averages 60 linear feet of wood (including a wastage allowance), which the company purchases for $1.50 per linear foot. Backyard Bliss finds it necessary to maintain an inventory balance equal to 15% of the following quarter's production needs of cedar as a precaution against stock-outs. Opening raw materials inventory consists of 39,150 linear feet of cedar. Backyard Bliss pays for 80% of a quarter's purchases in the quarter of purchase and 20% in the following quarter. Each bench also requires a cast iron frame, which the company purchases pre-made from Cheatle Forge. The frames are readily available at a cost of $85, are purchased as needed and paid for at the time of purchase. 8. Beginning accounts payable will consist of $52,137 arising from the estimated direct material purchases for Q4, 2020 of $260,685. 9. While much of the wood cutting process is automated, the assembly process is labour intensive. Employees are paid an average of $30 per hour, including the employer's portion of employee benefits. All payroll costs are paid in the period in which they are incurred. Each bench spends a total of 75 minutes in production. 10. Due to the company's concentration on a single product, manufacturing overhead is allocated based on volume (i.e. the units produced). The variable overhead manufacturing rate is $13.00 per unit. 11. The fixed manufacturing overhead costs for the entire year are as follows: Training and development Property and business taxes Supervisor's salary Depreciation on equipment Insurance Other $ 12,000 32,000 120,200 140,000 60,000 22.500 $ 386.700 The property and business taxes are paid on June 30 of each year. The annual insurance premium is paid at the beginning of October each year. All other "cash-related" fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Backyard Bliss uses the straight line method of depreciation. 12. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the portion that is fixed. Selling and Admin costs for 2019, when sales were 25,000 units for the year, were as follows: Quarter Total Selling and Admin Cost 114,000 406,500 211,500 114,000 These costs are paid in the quarter in which they eccur and are expected to follow the same pattern for 2021. 13. During the fiscal year ended December 31, 2021, Backyard Bliss will be required to make quarterly income tax installment payments of $30,000. Outstanding income taxes from the year ended December 31, 2020 must be paid in April 2021. Income tax expense is estimated to be 20% of net income. Income taxes for the year ended December 31, 2021, in excess of installment payments, will be paid in April 2022. 14. Backyard Bliss is planning to acquire additional manufacturing equipment for $162,400 cash. 40% of this amount is to be paid in September 2021, the rest, in October 2021. The manufacturing overhead costs shown above already include the depreciation on this equipment. 15. Backyard Bliss Inc. has a policy of paying dividends at the end of each quarter. The president tells you that the board of directors is planning on continuing their policy of declaring dividends of $18,000 per quarter. 16. A listing of the estimated balances in the company's ledger accounts as of December 31, 2020 is given below: Cash $ 78,465 Accounts receivable 203,112 Inventory-raw materials 58,725 Inventory-finished goods (300 units) 71,513 Prepaid insurance 45,000 Prepaid prop. tax 16,000 Capital assets $1,695,000 Less: Acc. Dep. 820.000 875.000 $ 1.347.815 Accounts payable Income tax payable Capital stock Retained earnings $ 52,137 32,500 800,000 463.178 $ 1.347.815 Required: 1. Prepare a quarterly master budget for Backyard Bliss for the year ended December 31, 2021, including the following schedules: Sales Budget & Schedule of Cash Receipts Production Budget Direct Materials Budget & Schedule of Cash Disbursements Direct Labour Budget Manufacturing Overhead Budget Ending Finished Goods Inventory Budget Selling and Administrative Expense Budget Cash Budget 2. Prepare a budgeted income statement and a budgeted statement of retained earnings for the year ended December 31, 2021, using absorption costing. 3. Prepare a budgeted balance sheet at December 31, 2021. Additional Information: Students are required to prepare a master budget for the year ended December 31, 2020 on a quarterly basis based on the information posted. The budget must be prepared in Microsoft Office Excel with the following criteria: For ease of formatting, use one worksheet for each schedule Prepare the schedules with the quarters going across the worksheet, not down. You must also have a Total column at the end to show the year's budget results. Prepare a data page detailing the information, to be used as reference other worksheets. Link as many of the numbers as possible using formulas. Every number on the cash budget should be referenced from a supporting page, or a sum on the page. Use Excel calculation tools as much as possible. If the headings (e.g. titles or the quarters) are set up on the first worksheet, they can be copied to all the other worksheets to save time. Proper formatting should be used so the budgets will look professional. Look under the word "format" in the toolbar to find the correct way to underline accounting numbers (single and double). Set Excel to record negative numbers within parenthesis e.g. (1,000), not - 1,000 Round all numbers to the nearest dollar, with the exception of per unit amounts. 7. 8. 9. 10. A sample budget spreadsheet has been posted to use as an example, but due to the numerous differences, it will not be efficient to use it as a template. You must create an original excel Workbook for your group submission. Backyard Bliss Inc. is a company that manufactures and sells outdoor benches. For planning and control purposes they utilize a quarterly master budget, which is usually developed at least nine months in advance of the budget year. Their fiscal year end is December 31. During January of 2020, Lisa Yang, the Backyard Bliss controller, spent some time putting together a sales forecast for the next budget year (January to December 2021). In February, Lisa's numbers really came in as she won the jackpot in the lottery. Shortly thereafter, the President of Backyard Bliss received Lisa's letter of resignation, which she sent from Bermuda. The President, desperately needing the budget completed, has approached you, a management accounting student, for help in preparing the budget for the coming fiscal year. Your conversations with the president and your investigations of the company's records have revealed the following information: 1. Lisa's sales forecast consisted of these few lines: For the year ended December 31, 2020: 26,000 units at $310.00 each For the year ended December 31, 2021: 30,000 units at $310.00 each For the year ended December 31, 2022: 33,000 units at $310.00 each 2. History has shown that sales are seasonal, with 55% of sales occurring in the second quarter, 25% in the third quarter, 10% in the first quarter, and the remaining 10% in the last quarter. 3. Because sales are seasonal, Backyard Bliss must rent an additional storage facility from March to June to house the additional finished benches on hand. The only related cost is a flat $2,000 per month, payable at the beginning of the month. 4. Sales are on a cash and credit basis, with 30% collected in cash. 60% of Credit sales are collected during the quarter of the sale, and 36% of credit sales are collected in the following quarter. The remainder are uncollectible. 5. Backyard Bliss has negotiated a line of credit with the bank such that they can borrow up to $400,000, in increments of $5,000, at an interest rate of 5%. Assume all borrowing takes place at the beginning of the quarter (interest is paid for the full quarter when borrowing takes place) and repayment takes place at the end of the quarter interest is paid for the full quarter that repayment is made.) Repayment must be in increments of $1,000. Creditors require a minimum bank balance of $5,000. 6. From previous experience, management has determined that an ending finished goods inventory equal to 10% of the next quarter's sales is required to meet customer demand. Opening finished goods inventory consists of 300 benches. 7. The primary raw material used is 4x6 cedar boards. Each bench averages 60 linear feet of wood (including a wastage allowance), which the company purchases for $1.50 per linear foot. Backyard Bliss finds it necessary to maintain an inventory balance equal to 15% of the following quarter's production needs of cedar as a precaution against stock-outs. Opening raw materials inventory consists of 39,150 linear feet of cedar. Backyard Bliss pays for 80% of a quarter's purchases in the quarter of purchase and 20% in the following quarter. Each bench also requires a cast iron frame, which the company purchases pre-made from Cheatle Forge. The frames are readily available at a cost of $85, are purchased as needed and paid for at the time of purchase. 8. Beginning accounts payable will consist of $52,137 arising from the estimated direct material purchases for Q4, 2020 of $260,685. 9. While much of the wood cutting process is automated, the assembly process is labour intensive. Employees are paid an average of $30 per hour, including the employer's portion of employee benefits. All payroll costs are paid in the period in which they are incurred. Each bench spends a total of 75 minutes in production. 10. Due to the company's concentration on a single product, manufacturing overhead is allocated based on volume (i.e. the units produced). The variable overhead manufacturing rate is $13.00 per unit. 11. The fixed manufacturing overhead costs for the entire year are as follows: Training and development Property and business taxes Supervisor's salary Depreciation on equipment Insurance Other $ 12,000 32,000 120,200 140,000 60,000 22.500 $ 386.700 The property and business taxes are paid on June 30 of each year. The annual insurance premium is paid at the beginning of October each year. All other "cash-related" fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Backyard Bliss uses the straight line method of depreciation. 12. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the portion that is fixed. Selling and Admin costs for 2019, when sales were 25,000 units for the year, were as follows: Quarter Total Selling and Admin Cost 114,000 406,500 211,500 114,000 These costs are paid in the quarter in which they eccur and are expected to follow the same pattern for 2021. 13. During the fiscal year ended December 31, 2021, Backyard Bliss will be required to make quarterly income tax installment payments of $30,000. Outstanding income taxes from the year ended December 31, 2020 must be paid in April 2021. Income tax expense is estimated to be 20% of net income. Income taxes for the year ended December 31, 2021, in excess of installment payments, will be paid in April 2022. 14. Backyard Bliss is planning to acquire additional manufacturing equipment for $162,400 cash. 40% of this amount is to be paid in September 2021, the rest, in October 2021. The manufacturing overhead costs shown above already include the depreciation on this equipment. 15. Backyard Bliss Inc. has a policy of paying dividends at the end of each quarter. The president tells you that the board of directors is planning on continuing their policy of declaring dividends of $18,000 per quarter. 16. A listing of the estimated balances in the company's ledger accounts as of December 31, 2020 is given below: Cash $ 78,465 Accounts receivable 203,112 Inventory-raw materials 58,725 Inventory-finished goods (300 units) 71,513 Prepaid insurance 45,000 Prepaid prop. tax 16,000 Capital assets $1,695,000 Less: Acc. Dep. 820.000 875.000 $ 1.347.815 Accounts payable Income tax payable Capital stock Retained earnings $ 52,137 32,500 800,000 463.178 $ 1.347.815 Required: 1. Prepare a quarterly master budget for Backyard Bliss for the year ended December 31, 2021, including the following schedules: Sales Budget & Schedule of Cash Receipts Production Budget Direct Materials Budget & Schedule of Cash Disbursements Direct Labour Budget Manufacturing Overhead Budget Ending Finished Goods Inventory Budget Selling and Administrative Expense Budget Cash Budget 2. Prepare a budgeted income statement and a budgeted statement of retained earnings for the year ended December 31, 2021, using absorption costing. 3. Prepare a budgeted balance sheet at December 31, 2021. Additional Information: Students are required to prepare a master budget for the year ended December 31, 2020 on a quarterly basis based on the information posted. The budget must be prepared in Microsoft Office Excel with the following criteria: For ease of formatting, use one worksheet for each schedule Prepare the schedules with the quarters going across the worksheet, not down. You must also have a Total column at the end to show the year's budget results. Prepare a data page detailing the information, to be used as reference other worksheets. Link as many of the numbers as possible using formulas. Every number on the cash budget should be referenced from a supporting page, or a sum on the page. Use Excel calculation tools as much as possible. If the headings (e.g. titles or the quarters) are set up on the first worksheet, they can be copied to all the other worksheets to save time. Proper formatting should be used so the budgets will look professional. Look under the word "format" in the toolbar to find the correct way to underline accounting numbers (single and double). Set Excel to record negative numbers within parenthesis e.g. (1,000), not - 1,000 Round all numbers to the nearest dollar, with the exception of per unit amounts. 7. 8. 9. 10. A sample budget spreadsheet has been posted to use as an example, but due to the numerous differences, it will not be efficient to use it as a template. You must create an original excel Workbook for your group submission