Accounting master budget
xoon
comprehensive_project_3.docxSales: Coffee Company sells its coffee beans in 5-pound packages in one primary store location in the area surrounding Pace University for $25 each. Sales of coffee bean packages are as f ollows:
Cash Receipt policy: Sales of coffee bean bags to the coffee shop are collected from customers using the following policy: 40% in the month of sale and 60% in the following month. Assume that bad debts are negligible due to the thorough vetting of the creditworthiness of their customers.
Inventory: Coffee Company has two normal seasonal buildups in inventory: (1) before the fall quarter begins in September, (2) prior to the December break. Ending inventory purchases should equal 80% of the following month’s sales in units. Coffee beans are purchased in 25-pound coffee bean sacs from growers in Dominica for $100 each.
Cash Payment policy: Purchases of coffee bean sacs from Dominica are paid using the following policy: 50% paid in the month of purchase and the remaining 50% in the following month.
Cash borrowing: Coffee Company requires a minimum cash balance of $4,000 at the end of each month to ensure that they are able to pay any unforeseen bills. In order to maintain this minimum cash balance, they have an agreement with Bank of America to borrow money in denominations of $1,000 as needed.
Any borrowed funds are subject to a 1% simple monthly interest rate, paid in cash monthly, for each full month that money is borrowed (assume that any borrowing occurs on the last day of a month). For example, if the minimum amount of $1,000 is borrowed in October from the bank, 1% interest (or $10) is paid to the bank in November, even if the $1,000 is repaid in November.
Operating Expenses: All operating expenses are paid during the month in cash except for depreciation expense and prepaid insurance.
· Prepaid insurance was purchased on January 1, 2013 and runs through December 31, 2013.
· A new piece coffee packaging equipment was purchased for cash on October 1st for $12,000 in anticipation of the December busy period. Depreciation on the new piece of equipment is incurred over 24 months.
· Monthly depreciation expense for the company’s building is $2,000 and the old piece of equipment is $500.
· Wages/salaries total $2,000 per month and utilities total $300 per month.
Financial statements: Coffee Company has the following balance sheet as of September 30, 2013:
Requirements: Utilize formulas within your Excel file and use the appropriate format (see examples following) for the schedules. Points will be deducted for not using formulas in your budget schedules since you should understand the interrelationship between the budgets (e.g. sales in your budgeted income statement should be linked back from your master budget).
1. Prepare a master budget for the fourth quarter.
2. Prepare a cash budget for the fourth quarter.
3. Prepare a budgeted income statement.
4. Answer the following question: what are two ways that the company can improve their net income and/or cash flow for the upcoming quarter?
5. Prepare a budgeted balance sheet.
Sheet1
| Month | # packages | Month | # packages | |
| July (actual) | 400 | October (projected) | 400 | |
| August (actual) | 600 | November (projected) | 700 | |
| September (actual) | 300 | December (projected) | 600 | |
| January (projected) | 500 |
Cash4,400$ Liabilities:
Accounts receivable4,500 Accounts Payable950$
Inventory (64 sacs)6,400 Notes Payable (bank)-
Prepaid insurance15,000 Total Liabilities950$
Land50,000
Building50,000 Stockholders Equity:
Less: Accumulated
Depreciation(30,000) 20,000 Capital stock70,000$
Equipment30,000 Retained earnings32,350$
Less: Accumulated
Depreciation(27,000) 3,000 Total Stockholders' Equity102,350$
Total Assets103,300$
Total Liabilities and
Stockholders' Equity
103,300$
Lubin Coffee Company
Balance Sheet
September 30, 2013
Assets:Liabilities and Owners' Equity:
Sheet1
| Lubin Coffee Company | |||||
| Balance Sheet | |||||
| September 30, 2013 | |||||
| Assets: | Liabilities and Owners' Equity: | ||||
| Cash | $ 4,400 | Liabilities: | |||
| Accounts receivable | 4,500 | Accounts Payable | $ 950 | ||
| Inventory (64 sacs) | 6,400 | Notes Payable (bank) | - | ||
| Prepaid insurance | 15,000 | Total Liabilities | $ 950 | ||
| Land | 50,000 | ||||
| Building | 50,000 | Stockholders Equity: | |||
| Less: Accumulated Depreciation | (30,000) | 20,000 | Capital stock | $ 70,000 | |
| Equipment | 30,000 | Retained earnings | $ 32,350 | ||
| Less: Accumulated Depreciation | (27,000) | 3,000 | Total Stockholders' Equity | $ 102,350 | |
| Total Assets | $ 103,300 | Total Liabilities and Stockholders' Equity | $ 103,300 | ||
October
November
December
Total
Quarter
Sales Budget:
Budgeted sales in units
Selling price per unit
Total sales
Schedule of expressed cash collections:
September sales
October sales
November sales
December sales
Total cash collections
Merchandise purchases budget:
Budgeted sales (pounds of coffee beans)
Add: budgeted ending inventory
(pounds of coffee beans)
Total needs (pounds of coffee beans)
Less: beginning inventory
(pounds of coffee beans)
Required purchases
(pounds of coffee beans)
Required purchases (sacs of coffee beans)
Unit cost
Required dollar purchases
Budgeted cash disbursements for merchandise
purchases:
September purchases
October purchases
November purchases
December purchases
Total cash disbursements
Coffee Company
Master Budget
For the Three Months Ending December 31, 2013
Sheet1
| Coffee Company | ||||
| Master Budget | ||||
| For the Three Months Ending December 31, 2013 | ||||
| October | November | December | Total Quarter | |
| Sales Budget: | ||||
| Budgeted sales in units | ||||
| Selling price per unit | ||||
| Total sales | ||||
| Schedule of expressed cash collections: | ||||
| September sales | ||||
| October sales | ||||
| November sales | ||||
| December sales | ||||
| Total cash collections | ||||
| Merchandise purchases budget: | ||||
| Budgeted sales (pounds of coffee beans) | ||||
| Add: budgeted ending inventory (pounds of coffee beans) | ||||
| Total needs (pounds of coffee beans) | ||||
| Less: beginning inventory (pounds of coffee beans) | ||||
| Required purchases (pounds of coffee beans) | ||||
| Required purchases (sacs of coffee beans) | ||||
| Unit cost | ||||
| Required dollar purchases | ||||
| Budgeted cash disbursements for merchandise purchases: | ||||
| September purchases | ||||
| October purchases | ||||
| November purchases | ||||
| December purchases | ||||
| Total cash disbursements | ||||
OctoberNovemberDecemberTotal Quarter
Cash balance, beginning
Add: receipts from customers
Total cash available
Less: disbursements
Purchase of inventory
Salaries and wages
Utilities
Equipment purchases
Total disbursements
Excess (deficiency) of receipts
over disbursements
Financing:
Borrowing
Repayments
Interest
Total financing
Cash balance, ending-$ -$ -$ -$
Cash Budget
For the Three Months Ending December 31, 2013
Coffee Company
Sheet1
| Coffee Company | ||||
| Cash Budget | ||||
| For the Three Months Ending December 31, 2013 | ||||
| October | November | December | Total Quarter | |
| Cash balance, beginning | ||||
| Add: receipts from customers | ||||
| Total cash available | ||||
| Less: disbursements | ||||
| Purchase of inventory | ||||
| Salaries and wages | ||||
| Utilities | ||||
| Equipment purchases | ||||
| Total disbursements | ||||
| Excess (deficiency) of receipts | ||||
| over disbursements | ||||
| Financing: | ||||
| Borrowing | ||||
| Repayments | ||||
| Interest | ||||
| Total financing | ||||
| Cash balance, ending | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
Sales in units
Sales revenue
Variable expenses:
Cost of goods sold
Contribution margin
Fixed expenses:
Wages and salaries
Utilities
Prepaid Insurance expired
Depreciation: building
Depreciation: old equipment
Depreciation: new equipment
Total Fixed Expenses
Net operating income/loss
Less: interest expense
Net income/loss
Coffee Company
Budgeted Income Statement
For the Three Months Ending December 31, 2013
Sheet1
| Coffee Company |
| Budgeted Income Statement |
| For the Three Months Ending December 31, 2013 |
| Sales in units |
| Sales revenue |
| Variable expenses: |
| Cost of goods sold |
| Contribution margin |
| Fixed expenses: |
| Wages and salaries |
| Utilities |
| Prepaid Insurance expired |
| Depreciation: building |
| Depreciation: old equipment |
| Depreciation: new equipment |
| Total Fixed Expenses |
| Net operating income/loss |
| Less: interest expense |
| Net income/loss |
CashLiabilities:
Accounts receivableAccounts Payable
InventoryNotes Payable (bank)
Prepaid insuranceTotal Liabilities
Land
BuildingStockholders Equity:
Less: Accumulated
DepreciationCapital stock
Equipment: oldRetained earnings
Less: Accumulated
DepreciationTotal Stockholders' Equity
Equipment: new
Total Liabilities and
Stockholders' Equity
Less: Accumulated
Depreciation
Total Assets
Coffee Company
Budgeted Balance Sheet
December 31, 2013
Assets:Liabilities and Owners' Equity:
Sheet1
| Coffee Company | ||||
| Budgeted Balance Sheet | ||||
| December 31, 2013 | ||||
| Assets: | Liabilities and Owners' Equity: | |||
| Cash | Liabilities: | |||
| Accounts receivable | Accounts Payable | |||
| Inventory | Notes Payable (bank) | |||
| Prepaid insurance | Total Liabilities | |||
| Land | ||||
| Building | Stockholders Equity: | |||
| Less: Accumulated Depreciation | Capital stock | |||
| Equipment: old | Retained earnings | |||
| Less: Accumulated Depreciation | Total Stockholders' Equity | |||
| Equipment: new | Total Liabilities and Stockholders' Equity | |||
| Less: Accumulated Depreciation | ||||
| Total Assets | ||||
Month# packagesMonth# packages
July (actual)400October (projected)400
August (actual)600November (projected)700
September (actual)300December (projected)600
January (projected)500
