Explore BrainMass

Explore BrainMass

    Cash budget, Budgeted inccome statement and balance sheet

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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.

    © BrainMass Inc. brainmass.com June 3, 2020, 10:18 pm ad1c9bdddf
    https://brainmass.com/business/balance-sheet/cash-budget-budgeted-inccome-statement-and-balance-sheet-225188

    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

    ADVERTISEMENT