Calculation cash budget

\fFizzy Pop bottles a lemon flavoured soft drink. All inventory is in direct materials and nished goods at the end of each quarter. There is no workinprocess inventory. Fizzy Pop uses cases as the unit of analysis in its budgeting. Each case contains 24 bottles. The business is preparing to build its master budget for the next financial year ending 31 December 2021. The master budget will be based on the following information: i. The target sales for 2021 for each quarter (Q) are: a (11 260,000 cases . C12 240,000 cases a C13 280,000 cases 0 0.4 300,000 cases o 01 (2022) 280,000 cases ii. The selling price for each case is $9 and will be kept constant throughout the year. iii. The beginning nished goods inventory at the start of the first quarter is expected to be 100,000 cases at a cost of $5.30 per case. The inventory manager wants to increase inventory to 135,000 cases at the end of each quarter. iv. Direct materials for the soft drink include: - Syrup used as flavour for the soft drink. 0 Two (2) litres are required for each case at a cost of $1.20 per litre. 0 At 1 Jan 2021, it is expected that there will be 600,000 litres on hand. 0 Management wants to ensure that at the end of each quarter, inventory on hand is 75% of the following quarter's production needs. 0 Containers ~ used to bottle the soft drink. 0 Each bottle costs $0.04. 0 Management currently ensures that at the end of each quarter, inventory on hand is 50% of the following quarter's production needs. This will be continued for 2021. Assignment continued Packaging - each case is packed in a cardboard carton (one carton per case). Each carton costs $0.80. . . At 1 Jan 2021, it is expected that there will be 295,000 cartons on hand. Management wants to ensure that at the start of each quarter, 100% of that quarter's production needs are on hand. No direct material costs are expected to change in 2021. V. Labour hours for each case are expected to be 0.02 hours, while wages are $25 per hour and are not expected to increase in 2021. vi. Based on 2020 information and reasonable forecasts, Fizzy Pop has determined the following manufacturing overhead costs: Indirect materials per DLHr $0.75 Electricity per DLHr $2.00 Maintenance on machinery per DLHr $0.50 . Supervisor's salary per quarter $72,000 Factory rent per quarter $85,000 Insurance per quarter $14,000 Machinery depreciation per quarter $16,000 vii. Fizzy Pop expects to incur the following selling and administrative costs: Freight and delivery per case sold $4.00 . Advertising costs per quarter $20,000 Office salaries per quarter $40,000 $25,000 . Rent on administrative building per quarter Insurance per quarter $5,000 Depreciation on office equipment per quarter $5,000 vili. The cash balance at the beginning of January 2021 is expected to be $100,000. ix. Sales are all made on credit terms. Eighty (80) per cent of sales are collected in the quarter of the sale and the remaining twenty (20) per cent are collected in the following quarter. X. Fizzy Pop purchases all of its direct materials on credit terms. It pays its suppliers sixty (60) per cent of the purchase price in the same quarter as the purchase, and the remaining forty (40) per cent is paid in the following quarter. xi. Labour costs are paid in the quarter in which they are incurred. xii. Manufacturing overhead expenses, and selling and administrative overhead expenses, are paid in the quarter in which they arise. xili. Fizzy Pop intends to use cash to invest in a new machine in the second quarter at a cost of $900,000. The new machine will replace an existing one, which an interested party has offered to purchase for $650,000, in the fourth quarter. xiv. At 1 January 2021 the balance sheet is expected to show: Accounts receivable $550,000 Accounts payable $300,000 Assignment continues on next pageXV. Senior management wants to maintain a minimum of $25,000 in cash on hand at the end of each quarter as a buffer for any unexpected cash needs. xvi. Fizzy Pop is able to borrow short-term financing quarterly to cover any cash deficits, and does so at the end of each quarter where applicable. Repayments are also made at the end of each quarter where applicable. The interest rate on any short-term borrowing is 6.0 per cent per annum