at the average selling price shown. Month Sales (# of Units) Average Selling Price February Actual 4,000 $ 380 March Actual 4,000 $ 375 April Estimate 4,500 $ 370 May Estimate 5,000 $ 375 June Estimate 5,500 $ 390 July Estimate 6,000 $ 370 Prices fluctuate. Some customers pay right away and others are invoiced on account. Historically, 50% of the sales are collected in the month of sale, with 30% more in the next month and the balance in the second month. CI likes to end the month with enough inventory to satisfy 75% of the next month's sales estimate. In practice, they are not always exact on this. On March 31, they had 5,250 units in inventory at an average cost of $162. Because CI carries so much inventory, it helps that they can delay payment to the wholesaler. Typically, CI pays 30% of the invoice cost in the month of purchase and the balance in the following month. The actual purchases made in March were $648,000. Just enter this number in cell E46. Most of the variable labour is used in moving and assembling inventory so the number of units purchased is the most accurate driver of variable labour costs. Variable labour averages @ $24 per unit purchased in the month of purchase. Fixed labour is estimated to be $45,000 for the quarter to cover management and office staff salaries. Total Labour expense in March was $173,000. Payroll processing and reporting cutoffs mean that 60% of the labour is paid in the month it is earned and the rest is paid the next month. General & Administration expenses are driven by unit sales so that is the driver used to forecast them. Variable G&A averages $75 per unit and fixed G&A is $16,000 each month. The fixed G&A value includes $3,600 of depreciation expense. The balance sheet from March 31st is given and it shows opening bank balance for the quarter. In addition to the cashflows already mentioned, the company plans to pay a $26,000 dividend in May and purchase $80,000 of new storage equipment in June and pay cash for it. The equipment will not be used in June, so it will not affect the monthly depreciation. The company has a $100,000 line of credit available to them and as of March 31st, it is unused. That means that CI can borrow up to $100,000 if they need to - that is their credit limit. They currently owe nothing on it. CI has a terrific relationship with the bank, so the line is provided at a 0% interest rate as long as they only use it periodically and are able to repay within the quarter. You can assume no interest. The company has decided that going forward they want to maintain a balance of $1,000,000 at month end in their account. So, if their balance is forecast to be below that, assume that they borrow from the line of credit to bring the balance to $1,000,000. They will repay any amount owing as soon as they can while maintaining the minimum balance they want. CB203 Master Budget Comprehensive Assignment 15% of Grade Below is the Actual Balance Sheet as of March 31st: (These balances are already input into the template for you in the Balance Sheet section at the bottom of the worksheet). They are the actual and require no calculations on your part. March 31st Cash $ 400,000 Accounts Receivable 1,054,000 Merchandise Inventory 850,500 Fixed Assets 1,000,000 Less: Depreciation -$360,000 Total Assets $ 2,944,500 Accounts payable 453,600 Wages Payable 69,200 Line of credit $0 Total Liabilities 522,800 Common Shares 1,104,000 Retained Earnings 1,317.700 Total Liabilities and Equity $ 2,944,500