Albert Enterprises is a manufacturing company that builds
heavy equipment repair parts for the construction industry. Albert is providing
the following information for preparing their budgets for the first quarter of
2023.
Sales:
November, 2022 Actual Sales were $1,500,000 and December, 2022 Actual
Sales were $2,100,000
Budgeted
Sales for the first 5 months of 2023 are:
January
30,000 units
February
40,000 units
March
50,000 units
April
45,000 units
May
35,000 units
Budgeted
selling price is $60/unit
All
sales are made on the account. 30% is collected in the month of the sale,
50% is collected in the first month following the sale, and the remaining
20% is collected in the second month after the sale.
The
company wants to maintain a finished goods inventory equal to 40% of next
month’s budgeted units of sales.
Each
unit of production requires 3kg of materials. The company maintains raw
materials inventory equal to 20% of next month’s production needs. The
budgeted purchase price for raw materials is $8/kg.
All
purchases of materials are made on account. 30% of the month’s purchases
are paid in the month of purchase and the remaining 70% are paid the
following month.
Each
unit of production requires 2 direct labor hours. The direct labor rate
per hour is $6/hour and all labor costs are paid by the end of the
month.
Variable
manufacturing overhead per direct labor hour is $2. The budgeted monthly
fixed manufacturing overhead is $200,000 which includes monthly
depreciation of $25,000.
Variable
selling expenses per unit sold are $2/unit. The budgeted monthly fixed
selling and administrative expenses are $120,000 which includes monthly
depreciation of $20,000.
The
company will be acquiring some additional equipment in the first quarter
of the year. They will purchase equipment for $300,000 in cash in
January.
Dividends
of $150,000 will be paid out in March.
The
company has a policy that it needs to maintain a cash balance of $50,000
at all times. They do have a line of credit with their bank. It has an
annual interest rate of 12%. Borrowing, if needed, will occur at the
beginning of the month and repayment of the principal and interest will
occur at the end of the month. Borrowings and repayments are done in
increments of $5,000.
REQUIRED:
Prepare
the sales budget for each month of the first quarter. (25
points)
Prepare
the schedule of expected cash collections for each month of the first
quarter. (25 points)
Prepare
the production budget for each month of the first quarter. (25
points)
Prepare
the direct materials budget for each month of the first quarter. (25
points)
Prepare
the schedule of cash payments to suppliers for each month of the first
quarter. (25 points)
Prepare
the direct labor budget for each month of the first quarter. (25
points)
Prepare
the manufacturing overhead budget for each month of the first
quarter. (25 points)
Prepare
the selling and administrative expense budget for each month of the first
quarter. (25 points)
Prepare
the cash budget for each month of the first quarter. (50
points)
Prepare
the budgeted income statement for the full quarter. (75 points)
Prepare
the budgeted balance sheet as of March 31, 2023. (75 points)
Total: 400 points
Please read the instructions carefully before you
begin to attempt the project:
Please complete each of the 10 budget templates for
Albert Enterprises by month for the first quarter of the year (January,
February, March 2023). The balance sheet will be as of March 31, 2023, and
the Income Statement will be for the full quarter ended March 31, 2023.
You will be utilizing the format and templates I had you work on
during my video lecture. Please refer to the Module 2 PowerPoint
presentation for guidance and format of each of the 10 budget schedules.
You should prepare each schedule on a separate tab within Excel. If
you create formulas as you build each template and link the schedules; it
will allow you to make changes that will automatically update all
schedules.
