Share
Explore BrainMass

Cash budget, Budgeted inccome statement and balance sheet

Homework Help

Using the scenario in the attached document (marked as scenario), please fill out the information using the tables that are laid out (also in the attached document).

Schedule a: Sales Budget January February March
Total sales (100% on credit)

Schedule b: Cash Collections
60% of current month's sales
30% of previous month's sales
10% of second previous month's sales
Total collections

December January February March

Schedule c: Purchases Budget
Desired ending inventory
Cost of goods sold
Total needed
Beginning inventory
Purchases

Schedule d: Disbursements for Purchases
100% of previous month's purchases
March 31 accounts payable

Exhibit I
VICTORIA KITE
Cash Budget
For the Three Months Ending March 31, 2008

January February March

Cash balance, beginning
Minimum cash balance desired
(a) Available cash balance
Cash receipts and disbursements:
Collections from customers
(Schedule b)
Payments for merchandise
(Schedule d)
Rent
Wages and salaries
Miscellaneous expenses
Dividends
Purchase of fixtures
(b) Net cash receipts & disbursements
Excess (deficiency) of cash before
financing (a + b)
Financing:
Borrowing, at beginning of period
Repayment, at end of period
Interest, 10%, compounded monthly
(c) Total cash increase (decrease)
from financing
(d) Cash balance, end (beginning
balance + c + b)

Exhibit II
VICTORIA KITE
Budgeted Income Statement
For the Three Months Ending March 31, 2008

Sales (Schedule a)
Cost of goods sold (Schedule c)
Gross margin
Operating expenses:
Rent*
Wages and salaries
Depreciation.
Insurance
Miscellaneous
Net income from operations
Interest expense
Net income
*(January-March sales less $10,000) x .10 plus 3 x $250

Exhibit III
VICTORIA KITE
Budgeted Balance Sheet
March 31, 2008
Assets
Current assets:
Cash (Exhibit I)
Accounts receivable*
Merchandise inventory (Schedule c)
Unexpired insurance
Fixed assets, net: $12,500 + $3,000 - $750
Total assets
Liabilities and Stockholders' Equity
Liabilities:
Accounts payable (Schedule d)
Rent payable.
Dividends payable
Stockholders' equity**
Total liabilities and stockholders' equity.
*February sales (.10 x $70,000) plus March sales (.40 x $38,000) = $22,200
**Balance, December 31, 2007
Add: Net income.
Total
Less: Dividends declared.
Balance, March 31, 2008

====================================================================

Scenario & Professor's notes

Scenario

? Victoria Kite Company sells kits on the web wants a master budget for
? The next three months beginning January 1, 2008..

1) It desires an ending minimum cash balance of $5,000 each month.
2) Sales forecasted at an average wholesale selling price of $8 per kite.
In January, Victoria Kite is beginning just-in-time (JIT) deliveries from suppliers, which means that purchases equal expected sales

3) On January 1, purchases will cease until inventory reaches $6,000, after which time purchases will equal sales. Merchandise costs average $4 per kite.
4) Purchases during any given month are paid in full during the following month.
5) All sales are on credit, payable within 30 days, but experience has show that:
60% of current sales are collected in the current month,
30% in the next month, and
10% in the month thereafter. Bad debts are negligible.

6) Monthly operating expenses are as follows:

Wages and salaries $15,000
Insurance expired 125
Depreciation 250
Miscellaneous 2,500
Rent 250/month + 10% of quarterly sales over 10,000

7) Cash dividends of $1,500 are to be paid quarterly, beginning January 15, and are declared on the fifteenth of the previous month.
8) All operating expenses are paid as incurred, except insurance, depreciation, and rent.
9) Rent of $250 is paid at the beginning of each month, and the additional 10% of sales is paid quarterly on the tenth of the month following the end of the quarter.
The next settlement is due January 10.

10) The company plans to buy some new fixtures for $3,000 cash in March.
11) Money can be borrowed and repaid in multiples of $500 at an interest rate of 10% per annum.
Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid.
11A) Assume that borrowing occurs at the beginning, and repayments at the end of the month s in questions.

12) Assets as of December 31,2007

Cash $5,000
Accounts receivable 12,500
Inventory * 39,050
Unexpired insurance 1,500
Fixed assets, net 12,500
Total $70,550

13) Liabilities as of December 31, 2007

Accounts payable (merchandise) $35,550
Dividends payable 1,500
Rent payable 7,800
Total $44,850

14) November 30 inventory balance = $16,000

15) Recent and forecasted sales

October $38,000
November $25,000
December $25,000
January $62,000
February $70,000
March $38,000
April $45,000

Required:

(1) Prepare a master budget including a
? Budgeted income statement for the months January through March 2005
? Budgeted balance sheet for the months January through March 2005
? Budgeted statement of cash receipts and disbursements for the months January through March 2005
? Supporting schedules for the months January through March 2005

(2) Explain why there is a need for a bank loan and what operating sources provide the cash for repayment of the bank loan.

Notes from the professor to assist

First of all, please do schedule A, B, C, and D before you do the exhibits.

Now, I will give you a detailed instructions for each transaction.

12) It desires an ending minimum cash balance of $5,000 each month.

The $5,000 should be reported under budgeted cash report and disbursement. It should be reported under the heading "Minimum cash balance desired. This should under each month.

13) Sales forecasted at an average wholesale selling price of $8 per kite.
In January, Victoria Kite is beginning just-in-time (JIT) deliveries from suppliers, which means that purchases equal expected sales

This item combined with items 3 and 15, will let you calculate the amount of cost of goods sold reported on Schedule C.

Example for the Month of December:

Item 2 states that sales price per kite is $8.00
Item 3 states cost per kite is $4.00

So the percentage of cost to sales is 4/8 or 50%.

Item 15 states that December sales is forecasted at $25000
So the cost of goods sold for December is $25,000 x .50 = 12,500.

Please follow the same procedure for the rest of the periods

14) On January 1, purchases will cease until inventory reaches $6,000, after which time purchases will equal sales. Merchandise costs average $4 per kite.

First part state on January 1, purchases will cease until inventory reaches $6,000. This means that the purchase for the month of January that is reported on Schedule C is Zero

Second part states after which time purchases will equal sales. This means that the desired ending inventory =$6,000 for the month of January through the month of March that is reported on Schedule C.

15) Purchases during any given month are paid in full during the following month.

Whatever purchases was made in December will be paid in January and so on. In the mean time, note that purchases for March has not been paid yet at the end of March (the period under consideration). So the amount is owed and will be paid latter. However, according to the matching principle this amount should be reported on Exhibit III (Budgeted Balance Sheet) as accounts payable.

Let me explain what is the term accounts payable means.

Accounts payable is created as a result of purchasing something on account (not pay for it at the time of purchase). This amount is considered to be liability.

16) All sales are on credit, payable within 30 days, but experience has show that:
60% of current sales are collected in the current month,
30% in the next month, and
10% in the month thereafter. Bad debts are negligible.

Number 5 is use to determine cash collection from sales that is reported on Schedule B.

Example: Cash collection for January is calculated as follow:

60% of the current month sales: .60 x 62,000 = 37,200
30% of the next month sales : .30 x 25,000 = 7,500
10% thereafter : .10 x 25,000 = 2,500
Total 47,200

Here is how it looks on the schedule
January February March
Schedule b: Cash Collections
60% of current month's sales $37,200
30% of previous month's sales 7,500
10% of second previous month's sales 2,500
Total collections $47,200

Please use the same procedure for the other periods.

17) Monthly operating expenses are as follows:

Wages and salaries $15,000
Insurance expired 125
Depreciation 250
Miscellaneous 2,500
Rent 250/month + 10% of quarterly sales over 10,000

These are monthly expenses and should be reported on Exhibit II (Budgeted income statement). However note the following two important factors:
a. The income statement is for three month
b. You have to make a calculation for the rent. Use Item 5 and item 9.

18) Cash dividends of $1,500 are to be paid quarterly, beginning January 15, and are declared on the fifteenth of the previous month.

Item #7 is reported on Exhibit III (Budgeted balance sheet) when you calculate the stockholders 'equity.

19) All operating expenses are paid as incurred, except insurance, depreciation, and rent.

Depreciation expense is a systematic process of allocating the cost of the assets over its estimated useful life. So it does not result in any payment.

However, for the rent it does and it says that the rent was not paid in the period under consideration. So an accounts called rent payable must be reported on the Exhibit III (budged balance sheet) and you have to calculate this amount.

20) Rent of $250 is paid at the beginning of each month, and the additional 10% of sales is paid quarterly on the tenth of the month following the end of the quarter.
The next settlement is due January 10.

Use item #6 and 9 to calculate this amount.

21) The company plans to buy some new fixtures for $3,000 cash in March.

This will be added to fixed assets net on Exhibit III (Budgeted balance sheet)

Item 11,

Money can be borrowed and repaid in multiples of $500 at an interest rate of 10% per annum.
Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid.

Assume that borrowing occurs at the beginning, and repayments at the end of the month s in questions.

Use two months period to calculate interest expense.

Items 12 and 13

12) Assets as of December 31,2004

Cash $5,000
Accounts receivable 12,500
Inventory * 39,050
Unexpired insurance 1,500
Fixed assets, net 12,500
Total $70,550

13) Liabilities as of December 31, 2004

Accounts payable (merchandise) $35,550
Dividends payable 1,500
Rent payable 7,800
Total $44,850

Items 12 and 13 are used to calculate the balance of stockholders' equity at March 31, 2004.

Remember =

Assets = Liabilities + Stockholder's equity

So Stockholder's equity = Assets -Liabilities

14) November 30 inventory balance = $16,000

Reported on the Schedule C as beginning inventory for the month of December.

15) Recent and forecasted sales

October $38,000
November $25,000
December $25,000
January $62,000
February $70,000
March $38,000
April $45,000

These date are used in conjunction with items 2 & 3 to calculate the purchases and to report the sales on Schedule A.

Attachments

Solution Summary

1

Schedule a: Sales budget
January February March
Total sales (100% on credit) $62,000 $70,000 $38,000

Schedule b: Cash Collections
January February March
60% of current month's sales $37,200 $42,000 $22,800
30% of previous month's sales $7,500 $18,600 $21,000
10% of second previous month's sales $2,500 $2,500 $6,200
Total collections $47,200 $63,100 $50,000

Sales
October $38,000
November $25,000
December $25,000

Accounts receivable , March 31, 2008
40% of March sales $15,200
10% of February sales $7,000
Accounts receivable , March 31, 2008 $22,200

Scdhedule c: Purchases Budget
December January February March
Desired ending inventory $39,050 $8,050 $6,000 $6,000
Cost of goods sold $12,500 $31,000 $35,000 $19,000
Total needed $51,550 $39,050 $41,000 $25,000
Beginning inventory $16,000 $39,050 $8,050 $6,000
Purchases $35,550 $- $32,950 $19,000

Note that though the desired inventory at the end of each month is $6,000, for the month of January, the ending inventory
left over from the previous month exceeds the desired inventory (even if no pruchases were made in January). The actual
inventory left over after January COGS is retained as ending inventory balance in January.

Scdhedule d: Disbursements for Purchases
January February March
100% of previous month's purchases $35,550 $- $32,950
March 31 accounts payable $19,000

Exhibit I
VICTORIA KITE
Cash Budget
For the Three months ending March 31,2008
January February March
Cash balance, beginning $5,000 $5,100 $34,692
Minimum cash balance desired $5,000 $5,000 $5,000
(a) Available cash balance $5,000 $5,100 $34,692
Cash receipts and disbursements:
Collection from Customers (schedule b) $47,200 $63,100 $50,000
Payments for merchadise (schedule d) $35,550 $- $32,950
Rent $8,050 $250 $250
Wages and salaries $15,000 $15,000 $15,000
Miscellaneous expenses $2,500 $2,500 $2,500
Dividends $1,500 $- $-
Purchase of fixtures $- $- $3,000
(b) Net cash receipt & disbursements $(15,400) $45,350 $(3,700)
Excess(deficiency) of cash before financing (a+b) $(10,400) $50,450 $30,992
Financing:
Borrowing,at beginning of period $15,500 $- $-
Repayment, at the end of period $- $(15,500) $-
Interest, 10%, compounded monthly $- $(258) $-
© Total cash increase (decrease) from financing $15,500 $(15,758) $-
(d) Cash balance, end (beginning balance+c+b) $5,100 $34,692 $30,992

Exhibit II
VICTORIA KITE
Budgeted Income Statement
For the Three months ending March 31,2008
$
Sales (Schedule a) $170,000
Cost of goods sold (Schedule c)   $85,000
Gross Margin $85,000
Operating expenses:
Rent $16,750
Wages and salaries $45,000
Depreciation $750
Insurance $375
Miscellaneous expenses $7,500
Net Income from operations $14,625
Interest expense $258
Net Income $14,367

Exhibit III
VICTORIA KITE
Budgeted Balance Sheet
March 31, 2008
2008 2007
Assets
Current assets:
Cash (Exhibit I) $30,992 $5,000
Accounts receivable $22,200 $12,500
Merchandise inventory (schedule c) $6,000 $39,050
Unexpired insurance $1,125 $1,500
Fixed assets, net $14,750 $12,500
Total assets $75,067 $70,550

Liabilities and stockholders' Equity
Liabilities:
Accounts payable (schedule d) $19,000 $35,550
Rent payable $16,000 $7,800
Dividends payable $1,500 $1,500
Stockholders' Equity $38,567 $25,700
Total Liabilities and stockholders' Equity $75,067 $70,550

Stockholders' Equity
Balance, December 31, 2007 $25,700
Add: Net Income $14,367
Total $40,067
Less: Dividends declared $1,500
Balance, March 31, 2008 $38,567

2 Explain why there is a need for a bank loan and what operating sources provide the
cash for repayment of the bank loan.

"The major reason for the bank loan is the need to pay off the accounts payable of December 31, 2007 that amount to $35,500 and quarterly variable rent payment of $7,800 as also quartely dividend payments of $1,500. The low level of sales in November and December also have affected the collections from debtors in January.

Decent sales in January and February have resulted in good collections in the month of February. Besides, there were no purchases made in January necessitating payments to creditors. The cash balance was therefore available to repay the loan."

$2.19