Student Nam Student BBQ Galore Master Budget For the Quarter ended June 20XX Actual Actual Forecast February April Sales Budget Forecast May March Forecast June Forrest Guarter Total Sales (5) Collection Budget Assumption Inputs Actual February Actual March Forecast April Forecast May Forecast Rune Quarter Total Sales Collected in month of sale corected in month after sale Collected in second month after sale Total Collections Rccounts Receivable at month and Merchandise Purchases Budget Assumption Inputs Forecast Forecast April Forecast May Forecast June Quarter Total Salesuada Desired ending inventory unit) Total needs Les Beginning inventory Purchases required units) Unit Cost Purchases 15) Merchandise Payments Budget Assumption Inputs Actual March Forecast April Forecast May Forecast June Quarter Total Purchase is Amount pa din month of purchase Amounted towing month Eyle Balance at month-end Labour Budget Assumption Inputs Act March Forecast April Forecast May Forecast Pune Quarter Total Purchases required units) Wanable about per unit Details: Its March 31%, and BBQ Galore (BG") is asking you to complete their monthly budget for the next three months. They buy Barbeques from a wholesaler for an average cost of $950 each and plan to sell the following units at the average selling price shown. Sales (# of Units) 155 Month February Actual March Actual April Estimate May Estimate June Estimate July Estimate 98 300 800 600 600 Average Selling Price $1,125 $1,018 $1,600 $1,600 $1,550 $1,550 The prices fluctuate as old units are sold off in the Winter and as the new units are rolled out in the busy Spring season. 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. It takes time to build all the barbeques so they need to be in stock early. BG likes to end the month with enough inventory to satisfy 80% of the next month's sales estimate. In practice, they are not always exact on this. On March 31, they had 240 units in inventory at an average cost of $950. Because BG carries so much inventory, it helps that they can delay payment to the wholesaler. Typically, BG pays 30% of the invoice cost in the month of purchase and the balance in the following month. The actual purchases made in March was $246,106. 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 @ $18 per unit purchased in the month of purchase. Fixed labour is estimated to be $37,500 for the quarter to cover management and office staff salaries. Total tabour expense in March was $17,144. Payroll processing and reporting cutoffs mean that 50% of the labour is paid in the month it is earned and the rest is paid the next month. General and Administration expenses are driven by unit sales so that is the driver used to forecast them. Variable G&A averages $95 per unit and fixed G&A is $15,000 each month. The fixed G&A value includes $4,500 of depreciation expense The balance sheet from March 315 is given and it shows opening bank balance for the quarter. In addition to the cashflows already mentioned, the company plans to pay a $25,000 dividend in April and purchase $150,000 of new storage equipment in June. 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 31, it is unused. BG has a territic relationship with the bank, so the line is provided at a 0% interest rate as long as they only use it periodically. You can assume no interest. The company has no overdraft protection on the bank account so they need to borrow from the line to keep the minimum balas General and Administration expenses are driven by unit sales so that is the driver used to forecast them. Variable G&A averages $95 per unit and fixed G&A is $15,000 each month. The fred G&A value includes $4,500 of depreciation expense. The balance sheet from March 31 is given and it shows opening bank balance for the quarter. In addition to the cashflows already mentioned, the company plans to pay a $25,000 dividend in April and purchase $150,000 of new storage equipment in June. 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 31it is unused. BG 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. You can assume no interest. The company has no overdraft protection on the bank account, so they need to borrow from the line to keep the minimum balance of so. CB203 Master Budget Comprehensive Assignment 15% of Grade Below is the Balance Sheet as of March 314. 31-Mar Cash Accounts Receivable Merchandise Inventory Fixed Assets Less: Depreciation Total Assets $100,000 $84,757 $228,000 $1,000,000 $350,000 $1.06252 Accounts payable Wages Payable Line of credit Total Liabilities $171,574 $8,572 $0 $180,146 Common Shares Retained Earnings Total Liabilities and Equity S650,000 $232,611 $1.062,257 Required: Using the template provided, complete all the budget schedules and finish the forecast with cashflow, income statement and balance sheet projections. Time required could be 8 to 12 hours so plan your time. The template will keep you organized. You shouldn't need to change it much, but you can if you prefer alternate layouts. Very Important: Your spreadsheet should use formulas and links to other cells wherever possible. The only cells that should have numbers typed in is the assumptions given in the question (eg. Number of units sold each month). The rest should be formulas. The idea is that we can change the selling price or number of units and your whole budget will recalculate and everything will still balance. If you type the value in a cell instead of linked formulas, it will be graded as though you submitted it blank. Time management: If you get everything working perfectly 1. Your cashflow balances should agree with your March and June bank balances on your balance sheet 2. Your net income and dividend values should explain the change in retained earnings between March and June. 3. Your balance sheet should balance however, it is very typical for your balance sheet to not balance. If it doesn't, you won't get 100%, but you can still get 95% based on your work. It is very likely that your first attempt will not balance. It happens to all of us. I suggest you put it away for a day or two and then come back with fresh eyes to see if you can solve it if you haven't figured it out in an hour, I suggest you submit it and move on with other course work. For some of you, not balancing will drive you crazy