Buad 195 chapter 4a master budget assignment winter 2018

Master Budget Problem:

You are the sole shareholder and operator of a small incorporated business that purchases virtual reality (VR) headsets and re-sells them. You started your business five years ago. The following data have been assembled to assist in the preparation of the master budget for the first quarter (January, February and March) of 2018.

As of December 31, 2017 your company had the following balance sheet: 

      Balance Sheet   December/31/2017   

Cash $5,000                                                            Accounts   payable  $52,452    

Accounts receivable  102,304                     Taxes   payable   1,100    

Inventory  35,532                                                ST loan   interest payable   30    

Prepaid insurance  3,500                                 ST loan   payable   4,000    

Total current assets  146,336                        Total   current liabilities   57,582    

Equipment  15,000                                              LT Loan   payable   15,000    

Accumulated amortization  3,000              Total   liabilities   72,582    

Net equipment  12,000                                     Common   shares   10,000        

                                                                                         Retained   earnings   75,754    

Total assets  158,336                                         Total   liabilities and equity   158,336 

*10,000 common shares issued for $1 each

1. The company sells each headset for $230. Actual sales for November were 360 units and for December were 376 units. In January it is expected that sales will decrease by 10%, but then will increase by 5% in February and will continue to increase by 5% each month after.

2. 20% of the cash for sales is collected in the month of sale, 40% is collected in the following month, and the remaining 40% is collected in the month after that. For simplicity, all sales taxes will be ignored.

3. The company purchases enough units each month to cover the current month’s sales and maintain an ending inventory equal to 70% of the following month’s projected sales. Each unit costs the company $150. Inventory purchases are paid for in the month following purchase. 

4. The company is expected to incur fixed operating expenses of $2,750 per month.

5. On August 1, 2017, the company paid $6,000 for one year’s insurance coverage.

6. Variable operating expenses have usually been about 15% of sales for the month. This will continue in January, but they expect to see an increase to 17% by February that will continue. Operating expenses are paid for in the month incurred. 

7. Interest is paid monthly on the long-term loan at a rate of 6% per year. They are also required to make quarterly principal payments, the next is due at the end of March for $1,500. 

8. Equipment costing $13,000 will be purchased for cash at the beginning of January. All equipment is depreciated on a straight-line basis over 10 years with no residual value.

9. You pay salaries totalling $4,200 each month. For simplicity, ignore all payroll tax implications.

10. You issue 2,000 additional common shares and sell them to your uncle for $1.00 per share at the end of February.

11. You will declare and pay a dividend of $3,000 at the beginning of February.

12. Income tax expense for this small business is calculated at 15% of the earnings before taxes. The company pays income tax instalments of $400 per month. 

13. The company must maintain a minimum cash balance of $5,000. A short-term loan is available to cover any shortfall. Interest is paid monthly on the previous month’s loan balance at a rate of 9% per year. Any cash above $5,000 available at month end is used to reduce any existing short-term loan. The interest for the short term debt should be calculated and shown separately from the long term debt.

Required and Check Figures:

Use Microsoft Excel to complete this assignment. Each student is to create his/her own Excel file, and complete the assignment individually. Wherever possible, the spreadsheet should use formulas and cell referencing. The spreadsheet should be formatted to show amounts to the nearest dollar (no cents). 

The items in the budget should appear in the following order. Note that when you are showing your schedules, they should flow down a worksheet, so balance sheet first, then cash receipts schedule underneath it and so on. Please do not flow the different schedules across the page or in different tabs.

1. The balance sheet for December 31, 2017 (as given above).

2. A sales forecast in units and dollars.

Check figure: March sales in dollars should be $85,810

3. A cash receipts schedule for January, February and March.  Hint: use the given percentages. 

Check figure: Cash receipts for January should be $83,278

4. A purchases schedule in units for January, February and March.

Check figure: March purchases should be 386 units.

5. A cash payments schedule for January, February and March.

Check figure: February total cash payments should be $76,894

6. A cash budget for January, February and March, including a calculation of cumulative loan at the bottom.

Check figures: February ending cash should be $6,872 and cumulative loan should be $0.

7. The pro-forma income statements for January, February and March. You should also have a total column which totals all three months. 

i. Subtotals for EBIT and EBT should be included. 

ii. List all expenses separately (do not combine).

iii. Show long-term and short-term interest separately.

iv. Hint: Cost of goods sold is not the same thing as purchases.

Check figures: February’s earnings after taxes should be $5,758 and March COGS should be $55,963 and Amortization Expense is $233 per month.

8. A pro-forma retained earnings schedule for the quarter ended March 31 (not for each month). 

Check figure: Ending retained earnings should be $91,347.

9. A pro-forma balance sheet at March 31. You do not have to complete balance sheets for January or February. 

i. Hints: Consider what will cause balances to change from the December 31, 2017 balance sheet. 

1. Prepaid insurance will be the opening amount less the amount expensed on the income statement. 

2. Tax payable will be the opening balance plus total tax expense less total tax instalments.

Check figure: Total assets should be $177,950.

Other information:

Format: 

· For numbers, use the Accounting format with zero decimal places.

· If your ending balance sheet is out by $1, do not worry about it.

· Marks will be awarded for a neat, well laid out and formatted spreadsheet. It should be easy to follow and have a good flow to it. 

· Schedules should flow down a worksheet, not side by side. So, the opening balance sheet would be at the top, the cash receipts schedule below it. 

· Lines should be included to show where calculations occur, with double lines at the bottom of each schedule. See the balance sheet on the previous page. 

· Dollar signs should be used only at the top and bottom of columns in a schedule, not on every number.

Formulas: Many of the numbers in the schedules, most of the numbers in the income statement, and all of the numbers in the retained earnings statement and the closing balance sheet should be the result of formulas or called cells. For the pro-forma balance sheet, some of the formulas will start with a reference to the opening balance sheet. For example, taxes payable will be the opening amount from December 31, plus total tax expense, minus total tax instalments. 

Cell referencing: Whenever possible, cell referencing must be used – you will not get the check figures unless you use cell referencing whenever possible. An efficient schedule design allows you to make changes in one area and have them update automatically in others.

Due Date: March 11 at 11:55 pm, to be uploaded through a Moodle drop-box. Late submissions will not be accepted. The file should be named as FirstNameLastName, Buad 195, and budget e.g. erincreagh-buad195-budget.xlsx.

Plagiarism: Each person is to create his/her own Excel file and do this assignment individually. NO EXCEPTIONS