Fall 2022 Required: Using the excel template provided on MyCanvas, Master Budget Submission Template Fall 2022, complete all the budget schedules and finish the forecast with cashflow, income statement and balance sheet projections. This assignment could require 8 to 12 hours for completion, so plan your time. The template will keep you organized. You shouldnt need to change it much, except to enter numbers and formulas. PLEASE NOTE: Copying or using anyone elses template will result in a grade of ZERO on the assessment (at minimum) and reported as an Academic Offense. Very Important: Use the template provided and complete the spreadsheet and save as your own document including your FULL name in the document namelast name first name. For example if your name is John Smith, save as Smith John master budget. Do NOT use anyone elses spreadsheet or copy anything at all from any other student. You should use formulas and links to other cells wherever possible. The only cells that should have numbers typed in them are the assumptions given in the question (eg. Number of units sold each month). Please note that due to the use of macros, you must save the template as is to your computer and then re-open it from there to Enable Content to enable macros. You can then proceed and save under your own name. The rest should be formulas or links to other cells. The idea is that we can change the selling price or number of units, etc. in one place and your whole budget(other cells) will just automatically recalculate everything else and everything will still balance. Failure to use a formula and/or a link to another cell that already contains that data wherever possible will result in grade deductions EVERY time. Simply inputting values into cells without linking the value to the originating cell will not allow for that automated functionality which is so critical to the budgeting process. The point is to create a spreadsheet which is accurate AND efficient and quick. Upload your excel spreadsheet to MyCanvas ONLY Microsoft Excel Macro-Enabled Spreadsheet formats are acceptable using the EXACT same format that the template is. DO NOT CHANGE THE TYPE OF FILE FORMAT. It should remain a Microsoft Excel Macro-enabled worksheet. (.xlsm) Do not wait until last minute to upload since any late assignments will get zero and technical issues are not an acceptable excuse. It is recommended you upload your assignments days BEFORE the due date and time. 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 doesnt, you wont get 100%, but you can still get a very good grade based on your other 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. You have plenty of days to work on this. Do not leave it to the last day to start. It is not the kind of assignment you can do quickly. Late assignments will not be accepted so get it done early. Grades The assignment is out of 100. Grades are allocated for each section as follows: Total Point Allocation Total Possible Sales Budget 4 Collection Budget 11 Merchandise Purchases Budget 12 Merchandise Payments Budget 9 Labour Budget 12 General and Administration Budget 8 Cash Budget 19 Ending Inventory/Cost of Goods Sold 10 Budgeted Income Statement 6 Budgeted Balance Sheet 9 Total 100 Grades are awarded for accuracy, completeness and the use of formulas and other cell references in excel to calculate those numbers where applicable. You are completing the budgets for the QUARTER including the Total Quarter. Other columns before and after the column may be filled in to assist you in completing the quarter budget. Using formulas will actually help you and your grade so that if you make an error at the start that has a ripple effect, you will not be penalized repeatedly in different areas for that original error if your formulas are correct later. Details: Its March 31st, and Chickadee Inc. (CI) is asking you to complete their monthly budget for the next three months. They buy Rotisserie Cookers from a wholesaler for an average cost of $162 each and plan to sell the following units 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 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 Rotisserie Cookers so they need to be in stock early. CI likes to end the month with enough inventory to satisfy 75% of the next months 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. 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 incurred 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 $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. 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 s 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. 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