Reference no: EM132553668
Case Study
Master Budget, Cash Budget and Budgeted Income Statement
After two years study at UCW, you finally graduate and start a job as Junior accountant at XYZ Inc. Your manager is responsible for the nationwide distribution of men grooming sets. Because of the new fashion style among current generation, the company has grown rapidly, and the prompt growth forces the management team to improve their efficiency and manage their production effectively.
You have just been given responsibility for all planning and budgeting of the entire men grooming set division. Today is your first day, you have just given an assignment to prepare master budget for the manager, who needs to present the budget and discuss the financial objectives with the shareholders tomorrow. During your job interview, you clearly stated that you gained managerial accounting knowledge and hand on experience during your MBA study.
Your first assignment is to prepare a master budget for the next fiscal year, starting April 1, 2019. Your co-work has left a pile of files on your desk including the past sale records, product information, manufacture schedule and supplier pricing list. Now, you realized that you should have pay more attention during the lecture. Now, you don't know where to start. Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your computer from accounting course.
Note: The company desires a minimum ending cash balance each month on $25,000. The XYZ's are sold to retailers for $18 each and they are flying off the shelves. Recent forecasted sales in units are provided below:
|
April 2020
|
20,000
|
October 2020
|
60,000
|
|
May 2020
|
24,000
|
November 2020
|
68,000
|
|
June 2020
|
68,000
|
December 2020
|
80,000
|
|
July 2020
|
35,000
|
January 2021
|
32,000
|
|
August 2020
|
45,000
|
February 2021
|
35,000
|
|
September 2020
|
55,000
|
March 2021
|
36,000
|
The increased sales volume before and during June and December is due to Father's Day and holidays with XYZ being a favorite. Ending finished goods inventories are supposed to be equal to 30% of the next month's sales in units. XYZ currently does its own assembly production in house. Each unit consists of 2 shaves and the cost of each is $1.00. Each unit needs 0.20 labour hour from assemble to finish package. The hourly pay rate to the assembling workers is $15 per hour. The production manager also required desired direct material ending inventory to 40% of the next month production.
Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the month following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The XYZ has determined that only 25% of a month's sales are collected by the end of the month in which the sale occurred. An additional 50% is collected in the month following the sale, and the remaining 25% is collected in the second month following the sale.
Bad debts have been negligible, supporting the credit terms as favorable.
|
Below is a display of the XYZ division monthly manufacture overhead and selling and administrative expenses:
Manufacture Overhead
|
|
Variable:
|
|
|
|
Indirect labour
|
$0.5 per labour hour
|
|
Indirect Materials
|
$0.2 per labour hour
|
|
Fixed:
|
|
|
Wages and Salaries
|
$2,000
|
|
Utilities
|
$1,000
|
|
Insurance
|
$1,000
|
|
Depreciation
|
$1,500
|
|
|
|
|
Selling and Administrative
|
|
Variable:
|
|
|
|
Sales Commissions
|
$1 per unit
|
|
Fixed:
|
|
|
Wages and Salaries
|
$22,000
|
|
Utilities
|
$14,000
|
|
Insurance
|
$1,200
|
|
Depreciation
|
$1,500
|
|
Miscellaneous
|
$3,000
|
|
|
|
|
|
|
Labour, Manufacture Overhead, and Selling and Administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course). XYZ will make a purchase of a parcel of land during the month of July 2020 for $22,500 cash. XYZ contributes to the corporate dividend at a rate of $12,000 each quarter, payable in the first month of the following quarter. XYZ's balance sheet at the end of the first quarter is shown below:
|
Assets
|
|
|
Cash
|
$14,000
|
|
Accounts receivable ($48,000 February sales; $168,000 March sales)
|
216,000
|
|
Liabilities and
|
|
|
Accounts payable
|
$85,700
|
An agreement with Bank of the West allows XYZ to borrow in increments of $1,000 at the beginning of each month, up to a total loan amount of $550,000. The interest rate on these loans is 10% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only.
Required:
Prepare a master budget for twelve months from April 1, 2020 to March 31st, 2020. Include the following budget schedules and financial statements:
1) Master Budget
2) Cash Budget. Show the cash budget by month and in total.
3) Budgeted Income statement
Attachment:- Cash Budget and Budgeted Income Statement.rar