BUAD195 Okanagan College Chapter 4 Financial Management Questions

User Generated

LnatJnat

Business Finance

BUAD195

Description

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:

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.

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

1

Bob Ross'Magic of painting Inc
Balance sheet
March 31,2019
$
Cash
Accounts receivables
Inventory
Prepaid insurance
Total current assets
Equipment
Accumulated amortization
Net equipment
Total assets

6,000.00
25,230.00
5,760.00
5,000.00
41,990.00
45,000.00
9,000.00
36,000.00
77,990.00

2 Sales budget
Actual
Jan
Sales in units
Selling price
Total sales

Actual
Feb

900
29.00
26,100.00

$
$

1000
29.00
29,000.00

3 Cash receipts Schedule

Total sales in $

Actual
Actual
Jan
Feb
26,100.00
29,000.00

Collections from:
30% month of sale
60% following month
10% month after 2nd month

4 Purchases budget schedule
April
Budgeted sales
Desired EI
Total units desired
Less:BI
Production

May
1200
600
1800
-480
1320

1500
680
2180
-600
1580

5 Cash payment schedule
March

April
$
15,840.00
13,680.00

Inventory purchased and paid
Accounts payable 3/31
100% paid in month after purchase
Total inventory expenses
Selling and Administratio...


Anonymous
Excellent! Definitely coming back for more study materials.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags