Master Budget Case Backyard Retreats Inc. Due date: April 3, 2022 at 11:59 pm Backyard Retreats 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 2022, Kyle Matthews, the Backyard Retreats controller, spent some time putting together a sales forecast for the next budget year (January to December, 2023). In February, Kyle's numbers came in, and he won a lottery jackpot. The president received Kyle's letter if resignation soon after, which was sent from the Bahamas. 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. Kyle's sales forecast consisted of these few lines: For the year ended December 31, 2022: 15,000 units at $308.00 each For the year ended December 31, 2023: 16,000 units at $308.00 each For the year ended December 31, 2024: 17,500 units at $308.00 each 2. History has shown that sales are seasonal, with 65% of sales occurring in the second quarter, 20% in the third quarter, 5% in the first quarter, and the remaining 10% in the last quarter. 3. Because sales are seasonal, Backyard Retreats 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 $1,200 per month, payable at the beginning of the month. . Sales are on a cash and credit basis, with 25% collected in cash. 70% of credit sales are collected during the quarter of the sale, and 28% of credit sales are collected in the following quarter. The remainder are uncollectible.5. Backyard Retreats has negotiated a line of credit with the bank such that they can borrow up to $450,000, in increments of $1,000, at an interest rate of 6%. 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 also be in increments of $1,000 plus interest. Creditors require a minimum bank balance of $3,000. 6. From previous experience, management has determined that an ending finished goods inventory equal to 15% of the next quarter's sales is required to meet customer demand. Opening finished goods inventory consists of 120 benches 7. The primary raw material used is 4x6 cedar boards. Each bench averages 50 linear feet of wood (including a wastage allowance), which the company purchases for $2.25 per linear foot. Backyard Retreats finds it necessary to maintain an inventory balance equal to 10% of the following quarter's production needs of cedar as a precaution against stock-outs. Opening raw materials inventory consists of 11,200 linear feet of cedar. Backyard Retreats pays for 85% of a quarter's purchases in the quarter of purchase and 15% 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 $80, are purchased as needed and paid for at the time of purchase. 8. Beginning accounts payable will consist of $24,967 arising from the estimated direct material purchases for Q4, 2022 of $166,444. 9. While much of the wood cutting process is automated, the assembly process is labour intensive. Employees are paid an average of $28 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 $15.00 per unit. 11. The fixed manufacturing overhead costs for the entire year are as follows: Training and development $ 12,000 Property and business taxes 28,000 Supervisor's salary 120,200 Depreciation on equipment 140,000 Insurance 60,000 Other 22,500 $ 382 700The property and business taxes for the factory are paid on June 30 of each year. The annual insurance premium for the factory 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 Retreats 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 2021, when sales were 12,000 units for the year, were as follows: Quarter Total Selling and Admin Cost 45,500 N - 207,500 86,000 59,000 These costs are paid in the quarter in which they occur, and are expected to follow the same pattern for 2023. (Determine the costs using the method that provides the most reliable cost equation.) 13. During the fiscal year ended December 31, 2023, Backyard Retreats will be required to make quarterly income tax installment payments of $5,000. Outstanding income taxes from the year ended December 31, 2022 must be paid in April 2023. Income tax expense is estimated to be 20% of net income. Income taxes for the year ended December 31, 2023, in excess of installment payments, will be paid in April, 2024. 14. Backyard Retreats is planning to acquire additional manufacturing equipment for $98,000. 40% of this amount is to be paid in September, 2023 and the remainder in October 2023. The manufacturing overhead costs shown above already include the depreciation on this equipment. 15. Backyard Retreats 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 $4,000 per quarter.16. A listing of the estimated balances in the company's ledger accounts as of December 31, 2022 is given below. Cash S 82,945 Accounts receivable 97,020 Inventory-raw materials 25,200 Inventory-finished goods (120 units) 31,968 Prepaid insurance 45,000 Prepaid prop. tax 14,000 Capital assets $1,440,000 Less: Acc. Dep. 576,000 864.000 $ 1.160,133 Accounts payable $ 24,967 Income tax payable 21,500 Capital stock 400,000 Retained earnings 713.666 $ 1.160.133 Required: 1. Prepare a quarterly master budget for Backyard Retreats for the year ended December 31, 2023, 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, 2023, using absorption costing.Additional Information: Students are required to prepare a master budget for the year ended December 31, 2023 on a quarterly basis based on the information posted. The budget must be prepared in Microsoft Office Excel with the following criteria: 1. For ease of formatting, use one worksheet for each schedule 2. 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. 3. Prepare a data page detailing the information, to be used as reference other worksheets. 4 . 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. 5. Use Excel calculation tools as much as possible. 6. 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. 7. Proper formatting should be used so the budgets will look professional. Your budgets should not contain pennies, your amounts should contain commas, etc. 8. Look under the word "format" in the toolbar to find the correct way to underline accounting numbers (single and double). Underlines make the schedule more readable for the user. 9. Round all numbers to the nearest dollar, with the exception of per unit amounts. 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