Preparing the Master Budget

Background information:

• Noah manages Arcs & Barges, a retail operation that specializes in selling large cruise ships.

• When Noah closed his books at the end of December, retained earnings was $1,080,000.

• Sales are 100% on credit.

• Credit sales are collected as follows:

• 50% in the month of the sale
• 10% in each subsequent month

• Cash on hand and Accounts receivable on December 31^st were $250,000 and $750,000, respectively. The accounts receivable include past-due amounts from previous months, of which one-third is expected to be collected each month of the budgeted period.

• Each ship sells for $50,000. Sales (in ships) projected as follows:

January.......... 10 ships

February . 20 ships

March.. 0 ships

April.. 30 ships

• Arcs & Barges maintains an inventory of at least two ships at the end of each month. The ships cost the company $40,000 each.

• Noah pays for his inventory purchases as follows: 90% during the month purchased and 10% during the next month.

• The Company did not have any accounts payable at the end of December

• Assume that Noahs incurs the following operating expenses each month:

Rent $14,500

Insurance 2,500

Property taxes (February only) 20,000

Licensing (January only) 10,000

Other fixed expenses 5,000

Commissions 10% of sales revenue

• All fixed and variable expenses are paid in the month incurred

• The Company must maintain a minimum cash balance of $200,000. In the event of a rainy day, it is able to draw on a line of credit it has with the bank to cover any shortages. Any borrowings must be repaid in the next month.

• Inventory on hand at December 31^st included 2 ships.

Noah is hiring you as a consultant to help him prepare the master budget he will need in order to effectively manage his company for the next several months. While he is very knowledgeable about his line of business, his background in accounting and finance is limited. Your task is to draft his master budget for January-March and educate him as to what its components are and how they interrelate from a business standpoint.

• Required (with available points in parenthesis)
  • Sales Budget /Schedule of Cash Collections (4)
  • Purchasing Budget /Schedule of Cash Disbursements (4)
  • Selling and administrative expense budget (3)
  • Cash Budget (3)
  • Budgeted income statement (3)
  • Budgeted balance sheet (5)
  • All budgets completed using an Excel spreadsheet (3):
    • Formatted professionally (1)
    • Formulas used where applicable (1)
    • Numbers linked between budgets (1)