Buad 195
Chapter 4 Budget Project 2019
Page 1 of 4
You are the sole shareholder and operator of a small incorporated business that imports Bob Ross Painting Kits and sells
them to retailers. The following data have been assembled to assist in the preparation of the master budget for the second
quarter (April, May and June) of 2019.
A. As of March 31, 2019 your company had the following balance sheet:
Cash
Accounts receivable
Inventory
Prepaid insurance
Total current assets
Equipment
Accumulated amortization
Net equipment
Total assets
Bob Ross’ Magic of Painting Inc.
Balance Sheet
March 31, 2019
$
6,000
Accounts payable
25,230
Taxes payable
5,760
Short-term loan payable
5,000
Total current liabilities
41,990
Long-term loan payable
$ 45,000
Total liabilities
$
9,000
36,000
77,990
Common shares
Retained earnings
Total liabilities and equity
$
$
13,680
2,700
16,380
20,000
36,380
10,000
31,610
77,990
B. The kits sell for $29 each. Recent and forecasted sales (in units) are as follows:
January (actual)
February (actual)
March (actual)
April
May
June
July
August
September
900
1,000
1,100
1,200
1,500
1,700
1,800
1,600
1,500
C. The company has a policy of having kits on hand at the end of the month equal to 40% of the next month’s
expected sales. As of the end of March 2019 the company had 600 kits in inventory. Each kit costs the company
$12. Inventory purchases are paid in the month following the month of purchase.
D. 30% of the cash for sales is collected in the month of sale, 60% is collected in the following month, and the
remaining 10% is collected in the month after that. For simplicity, all sales taxes will be ignored.
E. The company is expected to incur fixed operating expenses of $5,000 per month and variable operating expenses
equal to 10% of sales for the month. Operating expenses are paid for in the month incurred. Depreciation
expense is $750 per month.
F. On February 1, 2019, the company paid $6,000 for one year’s insurance coverage.
G. Office equipment costing $13,300 will be purchased and paid for in April.
H. You plan to pay salaries of $5,500 each month. For simplicity, ignore all payroll tax implications.
I.
You will declare and pay yourself a dividend of $5,000 in May.
J. Interest is paid monthly on the long-term loan at a rate of 3% per year. Principal payments are made annually
every November 30th.
K. Income tax expense for this small business is calculated at 15% of the earnings before taxes. The company pays
income tax instalments of $500 per month.
Buad 195
Chapter 4 Budget Project 2019
Page 2 of 4
L. 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 6% per annum. Any cash
above $5,000 available at month end is used to reduce any existing short-term loan.
Buad 195
Chapter 4 Budget Project 2019
Page 3 of 4
Required:
Use a spreadsheet application such as Excel to complete this assignment. Each student is to create his/her own
Excel file, and complete the assignment individually. Use formulas wherever possible. Your spreadsheet should
be formatted to show amounts to the nearest dollar (no cents).
The items in the budget should appear in the following order:
1. The balance sheet for March 31, 2019 (as given).
2. A cash receipts schedule for April, May and June.
Check figure: Cash receipts for April should be $32,480.
3. A purchases schedule in units for April, May and June.
Check figure: April purchases should be 1,320 units.
4. A cash payments schedule for April, May and June.
Check figure: April’s total cash payments should be $41,510.
5. A cash budget for April, May and June, including a calculation of cumulative loan at the bottom.
Check figure: At the end of April the cash balance should be $5,000 and the cumulative loan should be
$8,030.
6. The pro-forma income statements for April, May and June. 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 figure: May earnings after taxes should be $7,913.
7. A pro-forma retained earnings schedule for the quarter ended June 30th.
Check figure: Ending retained earnings should be $49,189.
8. A pro-forma balance sheet at June 30th. Hint: Consider what will cause balances to change from the
March 31 2019 balance sheet.
Check figure: Total assets should be $105,254.
9. Write a short paragraph to answer the following question: How well is this business is doing?
Buad 195
Chapter 4 Budget Project 2019
Page 4 of 4
Other information:
Format:
•
•
•
•
•
•
For numbers, use the Accounting format with zero decimal places.
If your ending balance sheet is out by $1 or $2, do not worry about it, it’s just rounding error.
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 March 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: November 11th 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. juliecruise-buad195budget.xlsx.
Plagiarism: Each person is to create his/her own Excel file and do this assignment individually. You can help
each other with formulas, checking numbers, etc., but no copying.
Assistance from the Professor: On October 31st we will have a class dedicated to working on the budget
assignment. Please bring your laptop computers that day. I will be available to help with general and specific
questions. You can also discuss the budget with your classmates and ask questions of each other. However,
everyone needs to prepare their own excel file. 5 bonus marks will be awarded for coming to class to work on the
assignment with your computer.
Purchase answer to see full
attachment