Austin Brewing Company is beginning its budgeting process for the
coming year. The following is the company's expected balance sheet at
December 31, 2002:
Assets Liabilities and Equities
Cash $ 10,000 Accounts payable $ 3,000
Accounts receivable 20,000 Long-term debt 50,000
Inventory 30,000
Total current asset $ 60,000 Total liabilities $ 53,000
Fixed assets $200,000 Common stock (at par) $ 10,000
Accumulated depreciation <90,000> Additional paid-in capital 20,000
Retained earnings 87,000
Total assets $170,000 Total liabilities and equities $170,000
The company expects to collect the beginning balance of accounts
receivable in January. In general, 30% of the company's sales are on
a cash basis. Of the credit sales, 40% are paid in the following
month, and the remaining 60% are paid in the second month after the
sale.
The accounts payable at the beginning of the year will be paid in
January. All materials are purchased on credit and paid for in the
following month.
The long-term debt has an annual interest rate of 12%. Interest
payments of 1% of the principal are made each month. The long-term
debt is not due for another five years.
Austin Brewing makes two different types of beer, an ale and a porter.
The ale is a lighter beer that requires fewer ingredients than does
the darker and heavier porter.
The input requirements for a case of each type of beer follow:
Ale Porter
Material Quantity per Case Cost Material Quantify per Case Cost
Hops 5.0 lb. $0.30/lb. Hops 10.0 lb. $0.30/lb.
Yeast 1.0 oz. 0.10/oz. Yeast 1.0 oz. 0.10/oz.
Sugar 0.5 lb. 0.40/lb. Sugar 0.8 lb. 0.40/lb.
Bottles 24 0.05/bottle Bottles 24 0.05/bottle
The labor to make a case of beer is the same for each type, 0.20 hours
at $10/hour. Labor is paid in the month it is earned.
Monthly overhead expenses are paid/recorded in the month incurred and
are expected to be as follows: Electricity - $2,000, indirect labor -
$20,000, rent - $5,000, and depreciation - $2,000.
Ale and porter sell for $10 and $12 per case, respectively. Estimated
sale (in cases) for the coming months for ale are: January - 3,000
cases, February - 3,000, March - 4,000, and April - 2,000. The
estimated sales for porter are: January - 4,000 cases, February -
5,000, March - 3,000, and April - 2,000.
The beginning inventory includes 2,000 cases of ale and 3,000 cases of
porter. The company prefers to have inventory at the end of each
month equal to the expected sales in the next month. Austin uses the
FIFO method of costing its inventory.
The company also plans to buy a new bottling machine at the end of
January at a cost of $20,000.
Requirements
1. Using Excel, prepare the following budgets for January, February,
and March:
a. Sales budget
b. Production budget
c. Direct-material budget
d. Labor budget
e. Budgeted cash flow statement
f. Budgeted income statement
g. Budgeted balance sheet
2. Sensitivity Analysis: (1) How would income be effected if sales
are 20% less than predicted? (2) How would income be effected if
selling prices are increased 10%? (3) How would income be effected
if direct material prices increased 5%? (Note: Consider each of
these as independent assumptions.) |
