ACCT 3224 Atma Ram Sanatan Dharma College Budgeting Excel Worksheet I have a business project that needs to be done. Several questions and I need the as so

ACCT 3224 Atma Ram Sanatan Dharma College Budgeting Excel Worksheet I have a business project that needs to be done. Several questions and I need the as soon as possible. Instructions attached. Thanks ACCT 3224 | Spring 2020
Budgeting Assignment
Instructions:
• Due by June 6, 2020
Empire Ltd. is a company that manufactures and sells a single product called WarStars. For planning and control
purposes they utilize a monthly master budget, which is developed in advance of the budget year. Their fiscal year end
is March 31.
The sales forecast consisted of these few lines:
• For the year ended March 31, 2020: 620,000 units at $15.00 each*
• For the year ended March 31, 2021: 640,000 units at $16.50 each
• For the year ended March 31, 2022: 660,000 units at $16.50 each
*Sales for the year ended March 31, 2020 are based on actual sales to date and budgeted sales for the duration of the
year.
Your investigations of the company’s records have revealed the following information:
1. Peak months for sales correspond with gift-giving holidays. History shows that January, March, May and
June are the slowest months with only 4% of sales for each month. Sales pick up over the summer with July,
August and September each contributing 6% to the total. Valentine’s Day in February boosts sales to 8%, and
Easter in April accounts for 10%. As Christmas shopping picks up momentum, winter sales start at 12% in
October, move to 16% in November and then peak at 20% in December. This pattern of sales is not expected
to change in the next two years.
2. From previous experience, management has determined that an ending inventory equal to 30%
of the next month’s sales is required to fit the buyer’s demands.
3. There is only one type of raw material used in the production of WarStars. MilkyWay acrylic (MWA) is a very
compact material that is purchased in powder form. Each WarStars requires 6 kilograms of MWA, at a cost of
$1.00 per kilogram.
The supplier of MWA tends to be somewhat erratic so Empire finds it necessary to maintain an inventory balance
equal to 50% of the following month’s production needs as a precaution against stock-outs.
Empire pays for 25% of a month’s purchases in the month of purchase, 30% in the following month and the
remaining 45% two months after the month of purchase. There is no early payment discount.
4. Beginning accounts payable will consist of $306,612 arising from the following estimated direct material
purchases for February and March of 2020:
MWA purchases in February, 2020:
$236,160
MWA purchases in March, 2020
$267,120
5. Space Age’s manufacturing process is highly automated, so their direct labour cost is low.
Employees are paid on an hourly basis. Their total pay each month is, therefore, dependent on production volumes
and averages $20.00 per hour. This rate already includes the employer’s portion of employee benefits. All payroll
costs are paid in the period in which they are incurred. Each unit spends a total of 6 minutes in production.
6. Variable manufacturing overhead is allocated based on units produced. The unit variable overhead
manufacturing rate is $2.50
7. The fixed manufacturing overhead costs for the entire year are as follows:
Training and development
Property and business taxes
Supervisors’ salaries
Depreciation of manufacturing equipment
Insurance
Other
•
•
$144,000
84,000
194,400
132,000
69,600
123,600
$ 747,600
Fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred.
Empire uses the straight-line method of depreciation.
8. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about
the portion that is fixed. Previous years’ experience has provided the following information:
Lowest level of sales: 560,000 units
Highest level of sales:680,000 units
Total S&A Expenses: $888,000
Total S&A Expenses: $1,032,000
It is estimated that the total selling and administrative expenses for the budget year will be about 2.5% greater
than the previous average. These costs are paid in the month in which they occur, with the exception of the only
non-cash item: a monthly depreciation of office equipment in the amount of $5,000.
Bad debt expense (see point 9) or warehouse rental (see point 10) are not included in the above expenses.
9. Sales are on a cash and credit basis, with 55% collected during the month of the sale, 30% the following month,
and 13% the month thereafter. 2% of sales are considered uncollectible (bad debt expense).
10.Because sales are seasonal, Empire must rent an additional warehouse from September to December to house
the additional inventory on hand at $25,000 per month, payable at the beginning of the month.
11.Sales in February and March 2020 are expected to be $744,000 and $372,000 respectively.
Based on the above collection pattern this will result in Accounts Receivable of $256,680 at
March 31, 2020, which will be collected in April and May 2020.
12.During the fiscal year ended March 31, 2021 Empire will be required to make monthly income tax installment
payments of $12,500. Outstanding income taxes from the year ended March
31, 2020 must be paid in June 2020.
13.Prior to the busy season, Empire is planning to upgrade its manufacturing equipment for which they will need
to pay cash. The bid that was accepted totaled $450,000 which will be paid in 9 equal instalments beginning
in June 2020. Manufacturing overhead costs shown above already include the depreciation on this
equipment.
14.An arrangement has been made with the local bank that if Empire maintains a minimum balance of $50,000
in their bank account, they will be given a line of credit at a preferred rate of 6% per annum.
All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month.
All borrowings and repayments from the bank should be in multiples of
$1,000 and interest must be paid at the end of each month.
Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that
month (i.e. any borrowing for a month must occur at the beginning of that
month).
There is an outstanding line of credit borrowing of $152,000 on March 31, 2020.
15.Empire has a policy of paying dividends at the end of each month. The President tells you that the board of
directors plans to continue their policy of declaring dividends of $10,000 per month.
16.A listing of the estimated balances in the company’s ledger accounts as of March 31, 2020 is given below:
Cash
Accounts receivable
Inventory-raw materials
Inventory-finished goods
Capital assets (net)
$
50,920
256,680
157,440
240,960
1,294,000
$
2,000,000
Accounts payable
Income tax payable
Bank Loan
Capital stock
$
306,612
50,000
152,000
500,000
Retained earnings
991,388
$
Required:
Prepare a monthly master budget for Empire for the year ended March 31, 2021, including the
following schedules:
1.
2.
3.
4.
5.
6.
7.
8.
Sales Budget & Schedule of Cash Receipts
Production Budget
Direct Materials Budget & Schedule of Cash Disbursements
Direct Labour Budget
Manufacturing Overhead Budget
Ending Finished Goods Inventory Budget
Selling and Administrative Expense Budget
Cash Budget
2,000,000

Purchase answer to see full
attachment