ACCOUNTING FOR MANAGERS2
i uploaded the sample file and questions here
Individual Case Study 2 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 All About The Beard Inc.(AATB). Your manager is responsible for the national 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 October 1, 2020. The office manager brought 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 rather than checking your social media page. Now, you dont know where to start. Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your computer from the accounting course during your MBA study.
Note: The company desires a minimum ending cash balance each quarter on $35,000. The beard grooming products are sold to retailers for $20 each and the sales have been stagnant due to the Covid-19. However, the marketing department has been positive toward the end of the year due to season change and upcoming holiday. The marketing department has just sent you their forecasted quarter sales and marketing budget.
Quarter
2020 Q4
2021 Q1
2021 Q2
2021 Q3
2021 Q4
2022 Q1
Sales in Unit
30,000
35,000
40,000
65000
68000
70,000
Marketing Expenses
$35,000
$20,000
$20,000
$45,000
$45,000
$45,000
The increased sales volume before and during June and January is due to Fathers Day and holidays with AATB being a favorite. Ending finished goods inventories are supposed to be equal to 20% of the next quarters sales in units. AATB currently does its own assembly production in house. Each unit consists of 3 shaves and the cost of each is $1.50. Each unit needs 0.10 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 30% of the next quarter production.
Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The AATB has determined that only 50% of sales are collected by the end of the quarter in which the sale occurred. An additional 30% is collected in the quarter following the sale, and the remaining 20% is collected in the second quarter following the sale. Bad debts have been negligible, supporting the credit terms as favorable.
Below is a display of the AATB 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 per month
Utilities $1,500 per month
Insurance $2,000 per month
Depreciation $2,000 per month
Selling and Administrative
Variable:
Sales Commissions $1 per unit
Fixed (Monthly) :
Wages and Salaries $22,000
Utilities $6,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). AATB will make a purchase of a parcel of land during the first quarter of 2021 for $22,500 cash. AATB contributes to the corporate dividend at a rate of $12,000 each quarter, payable in the first month of the following quarter. AATBs balance sheet at the end of the third quarter is shown below:
Assets
Cash $14,000
Accounts receivable ($48,000 sales in Q2 and $152,000 in Q3 this year ) $200,000
Liabilities
Accounts payable $85,700
An agreement with Bank of the West allows AATB 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 12% 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 Oct, 2020 to Oct, 2021. 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
Hi All,
Here are questions you have asked please my answer before.
1. We could not see any assignment-2 drop box in the UCW portal can u please update the assignment-2 drop box.
Submit online and submission link will be set up.
2. Do we need to do analysis quarterly or monthly?
Quarterly
3. The due date for 22nd September 2020 midnight or before the class?
Midnight
4. if some of the calculations will be wrong in assignment 2, will you deduct marks for those or giving whole 0?
Partial
5. The assignment requirement is to prepare budgets on a quarterly basis including cash budget as you mentioned in class
Quarterly
6. AATBs balance sheet at theendof the first quarter is shown below- Should this be theopening balance
be equal to 20% of the next quarters sales in units
7. Ending finished goods inventories are supposed to be equal to 20% of the nextmonthssales in units- Should this be 20% of nextquarter’ssales in units
Quarter
8. AATB will make a purchase of a parcel of landduring the quarterof 2021 for $22,500 cash-Which quarter
The first Quarter
9. 5. The company desires a minimum ending cash balance each month on $35,000. -is it $35,000 minimum balance as at every quarter ending date
Each Quarter
10. 6. AATB shouldonlyborrow money if the minimum cash budget is not maintained
Yes
11. Are we to assumeno income taxsince no figure is given
Not tax for this question and try to keep it simple
12. I need to know how to calculate ending desired material for the last quarter.
This is a question I would give answers as I mention in class.You should know if you pay attention during the lecture.
13. In the question, below sales commissions, it is mentioned monthly. Is it includes all like insurance, depriciation or not ? and we have to convert into quarterly ?
Yes
There are some errors and I have corrected in the updated version.
All the best, Master Budget
Step 1 Sale Budget
Expected sales volume: 3,000 units in the first quarter with 500-unit increases in each succeeding quarter.
Sales price: $60 per unit
Q1 Q2 Q3 Q4
Unit 3000 3500 4000 4500
Selling Price $ 60 $ 60 $ 60 $ 60
Total Revenue $ 180,000 $ 210,000 $ 240,000 $ 270,000
Step 2. Production Budget To meet future sales needs with an ending inventory of 20% of next quarters budgeted sales volume.
Budgeted Sale Units
+ Desired Ending 20% Next period
– Beginning Inventory
= Required Production Units
2019 2020 2021
Q4 Q1 Q2 Q3 Q4 Q1
Budgeted Sale 3000 3500 4000 4500 5000
Desired Ending 700 800 900 1000
Total Required Units 3700 4300 4900 5500
Beginning Inventory 600 700 800 900
Required production Unit 3100 3600 4100 4600
Step 3 Direct Material Budget Maintains an ending inventory of raw materials equal to 10% of the next quarters production requirements.
Direct Material Production Each Rightride requires 2 pounds and each pound costs at $4
+ Desired Ending DM 1,020 pounds for the fourth quarter of 2017
– Beginning DM Inventory
= Required DM purchase
2016 Curret budget 2017 2018
Direct Materials Budget Q1 Q2 Q3 Q4 Q1
Required production Unit 3100 3600 4100 4600
DM per unit 2 2 2 2
Total Pounds for Production 6200 7200 8200 9200
Desired Ending DM 720 820 920 1020 10%
Beginning DM 620 720 820 920
Required DM purchase 6300 7300 8300 9300
Cost per unit $ 4.00 $ 4.00 $ 4.00 $ 4.00
Total Cost of Purchase $ 25,200 $ 29,200 $ 33,200 $ 37,200
Step 4 Direct Labour Two hours of direct labor are required to produce each unit of finished goods
Hourly wage rate is $10
Q1 Q2 Q3 Q4
Required Production Unit 3100 3600 4100 4600
Direct Labour per Unit 2 2 2 2
Total Required DL 6200 7200 8200 9200 30800
DL Cost per Hour $ 10.00 $ 10.00 $ 10.00 $ 10.00
Total Direct Labor Cost $ 62,000 $ 72,000 $ 82,000 $ 92,000
Step 5 MOH Q1 Q2 Q3 Q4
Variable MOH
Indirect Material $ 6,200 $ 7,200 $ 8,200 $ 9,200 $ 1.00 Per Direct Labour
Indirect Labor $ 8,680 $ 10,080 $ 11,480 $ 12,880 $ 1.40 Per Direct Labour
Utilities $ 2,480 $ 2,880 $ 3,280 $ 3,680 $ 0.40 Per Direct Labour
Maintenance $ 1,240 $ 1,440 $ 1,640 $ 1,840 $ 0.20 Per Direct Labour
Total $ 18,600 $ 21,600 $ 24,600 $ 27,600
Fixed MOH
Supervisory Salaries $ 20,000 $ 20,000 $ 20,000 $ 20,000
Depreciation $ 3,800 $ 3,800 $ 3,800 $ 3,800
Property Taxes and Insurance $ 9,000 $ 9,000 $ 9,000 $ 9,000
Maintenance $ 5,700 $ 5,700 $ 5,700 $ 5,700
Total Fixed Costs $ 38,500 $ 38,500 $ 38,500 $ 38,500
Total MOH $ 57,100 $ 60,100 $ 63,100 $ 66,100 $ 246,400
Total Direct Labor Hour 30800
MOH per Direct Labor Hour $ 8 MOH/DL
Step 6 Operating Expense
Q1 Q2 Q3 Q4
Sales in Units 3000 3500 4000 4500
Variable Expenese
Sales Commissions $ 9,000 $ 10,500 $ 12,000 $ 13,500 $ 3.00 Per Unit
Freight $ 3,000 $ 3,500 $ 4,000 $ 4,500 $ 1.00 Per Unit
Total Variable $ 12,000 $ 14,000 $ 16,000 $ 18,000
Fixed Expenses
Adverising $ 5,000 $ 5,000 $ 5,000 $ 5,000
Salary Sales $ 15,000 $ 15,000 $ 15,000 $ 15,000
Salary Office $ 7,500 $ 7,500 $ 7,500 $ 7,500
Depreciation $ 1,000 $ 1,000 $ 1,000 $ 1,000
Property Taxes and Insurance $ 1,500 $ 1,500 $ 1,500 $ 1,500
Total Fixed $ 30,000 $ 30,000 $ 30,000 $ 30,000
Total S&GA $ 42,000 $ 44,000 $ 46,000 $ 48,000
Budgeted IS
Cost of Goods Sold
Cost per unit
DM 2 pounds $ 4.00 $ 8.00
DL 2 hours $ 10.00 $ 20.00
MOH 2 hours $ 8.00 $ 16.00 MOH allocated by DL
Total Cost per Unit $ 44.00
Q1 Q2 Q3 Q4
Revenue 180000 210000 240000 270000 $ 900,000.00
COGS 132000 154000 176000 198000 $ 660,000.00
Gross Profit 48000 56000 64000 72000 $ 240,000.00
S&GA 42000 44000 46000 48000 $ 180,000.00
Operating Profit 6000 12000 18000 24000 $ 60,000.00
Interest expense 100
Income before Income taxes 6000 12000 17900 24000
Income tax expense 3000 3000 3000 3000
Net Income 3000 9000 14900 21000
Cash Budget
Beginning Cash Balance Contains three sections:
Add: Cash Receipts (from Sales, Other Incomes, Assets disposals) Cash Receipts
Total Avaiable Cash Cash Disbursements
Less: Cash Disbursement (COGs, Opex) Financing
Excess (Deficiency) of Available Cash over Cash Disbursement
Financing (borrowing or repayment)
Ending Cash Balance
$38,000 Given from last year $38,000
Cash Receipt
60% are collected in the quarter
40% are collected in the following quarter
60,000 at December 31, 2016 be collected in full in the first quarter of 2017
Short-term investments are expected to be sold for $2,000 cash in the first quarter
15,000 Given by Management Cash Disbursment
Sales 50% are paid in the quarter purchased and 50% are paid in the following quarter
Schedule of Collection 2019 Q1 Q2 Q3 Q4 Q1 Accounts payable of $10,600 at December 31, 2016, are expected to be paid in full in the first quarter of 2017
Account Receivable 60000 60000 100% is paid in the quarter incurred. Direct Labour
First Quarter 180000 108000 72000 MOH and SGA are paid in the quarter except depreciation
Second Quarter 210000 126000 84000 Capital Expenditure
Third Quarter 240000 144000 96000 purchase a truck in the second quarter for $10,000 cash
Fourth Quarter 270000 162000 108000 AR Hayes makes equal quarterly payments of its estimated $12,000 annual income taxes
Total Collections 900000 168000 198000 228000 258000 Loans are repaid in the earliest quarter in which there is sufficient cash (that is, when the cash on hand exceeds the $15,000 minimum required balance
Purchase
Schedule of Payments Q1 Q2 Q3 Q4 Q1
Account Payable 10600 10600
First Quarter 25200 12600 12600
Second Quarter 29200 14600 14600
Third Quarter 33200 16600 16600
Fourth Quarter 37200 18600 18600 AP
Total Payment 124800 23200 27200 31200 35200
Q1 Q2 Q3 Q4
Beginning Cash Balace 38000 25500 15000 19400
Add: Cash Receipts
Collections from Customers 168000 198000 228000 258000
Sale of Investment Given 2000
Total Receipts 170000 198000 228000 258000
Total Avaiable Cash 208000 223500 243000 277400
Less: Cash Disbursement
Direct Materials 23200 27200 31200 35200
Direct Labour 62000 72000 82000 92000
MOH 53300 56300 59300 62300 not including depreication Total MOH – Depreciation “= Total MOH + Depreciation”
S&GA 41000 43000 45000 47000 not including depreication Total S&GA – Depreciation
Purchase of Truck Given 10000
Income Tax Expense Given 3000 3000 3000 3000 12,000/4
Total Disbursements 182500 211500 220500 239500
Excess (Deficiency) of Available Cash over Cash Disbursement 25500 12000 22500 37900 15,000 minium requirement
Financing (borrowing or repayment)
Borrowing 3000 15000 – 12000
Repayment 3100 paid back the loan plus interest expense
Ending Cash Balance 25500 15000 19400 37900 Cash BS
Interest Expense
(P*r%/12)*3
3000 borrowing as loan
Interest 10%
101.25 Interest expense for 3 months
