Trump Brand wigs
You were recently promoted to assistant controller at Trump Brand Wigs, a subsidiary of Trump Enterprises. Your employer manufactures and sells a single product, wigs for the gentleman who wants to look like a rodent died on his head. The first task you have been assigned in your new role is to prepare the master budget for the quarter ended September 30th, 2018. You have assembled the following information:
The wigs sell for $1,500 each.
Recent and forecasted sales (in units) are as follows:
· April (actual) 240
· May (actual) 260
· June (actual) 210
· July 290
· August 350
· September 300
· October 280
· November 240
· December 650
· Inventories of finished goods on hand at the end of each month are to be equal to 60% of the following months budgeted sales.
· As of June 30th the company had 174 wigs in inventory.
· Each wig requires 25 ounces of guinea pig fur, which the company purchases for $6 per ounce.
· It is company policy to keep enough guinea pig fur to meet 80% of the next month’s production needs.
· As of June 30th Trump Brand Wigs had 6,520 ounces of guinea pig fur on hand.
· Purchases of raw materials are paid for as follows: 25% in the month of purchase and the remaining 75% in the following month.
· All sales are on credit, with no discount, and payable within 15 days.
· The company has found, however, that only 30% of a month’s sales are collected by month-end. An additional 50% is collected in the month following, and the remaining 20% is collected in the second month following.
· Bad debts have been negligible.
· Each wig requires 20 hours to hand stitch.
· Employees who make the wigs are paid $25 per hour and never work overtime (i.e. the company has enough casual workers that they can call in if additional work is required).
· Manufacturing overhead includes all the costs of production other than direct materials and direct labour.
· The variable component is $45 per wig in production and the fixed component is $45,000 per month (this amount includes depreciation of $2,000 per month on the sewing machines and drying racks).
· Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced.
Trump Brand Wig’s monthly operating expenses are given below:
Royalties paid to The President $100 per wig sold
· Wages & salaries $30,000
· Utilities 5,000
· Insurance expired 1,500
· Depreciation 750
· Miscellaneous 8,000
All operating expenses are paid during the month in cash, with the exception of the depreciation and insurance expenses.
Due to the planned introduction of a new model of wig “The Stormy Special” the company is expecting a big increase in sales for Christmas.
To accommodate the expected increase in demand the company will be purchasing a new industrial die machine in July for $60,000 and some new sewing machines in August for $70,000. The company declares a dividend of $10,000 on the last day of each quarter which is then paid in the first month of the next quarter.
The balance sheet at June 30th is given below:
Cash $ 14,000
Accounts receivable 298,500
Inventory, raw materials 39,120
Inventory, finished goods 146,080
Unexpired insurance 13,500
Fixed assets 297,000
Accumulated depreciation (104,000) 193,000
Total Assets $ 704,200
Liabilities and Shareholders’ Equity
Accounts payable, purchases $ 35,145
Dividends payable 10,000
Capital stock, no par 50,000
Retained earnings 609,055
Total liabilities and shareholders’ equity $ 704,200
· Management of Trump Brand Wigs requires a minimum ending cash balance each month of $14,000.
· The company can borrow money from its bank at 12% annual interest.
· All borrowing must be done at the beginning of a month, and repayments must be made at the end of a month.
· Repayments of principal must be in round $1,000 amounts.
· Borrowing is also in round $1,000 amounts.
· Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter.
· Round all interest payments to the nearest whole dollar.
· Compute interest using whole months.
· The company wishes to use any excess cash to pay loans off as rapidly as possible.
1. Prepare a Sales Budget; Production Budget, Direct Materials Budget; Direct Labour Budget; Manufacturing Overhead Budget; and Sales and Administration Budget.
2. Prepare Schedules of Expected Cash Collections and Expected Cash Disbursements for Material as well as a Cash Budget.
3. Prepare a Budgeted Income Statement for the quarter ending September 30th
4. Prepare a Budgeted Balance Sheet at September 30th.
· All schedule and statements should be formatted professionally.
· Use excel to do the work. Use cell references and formulas in your schedules.
· If you’re having difficulty you can always email me a copy of your excel file and I’ll point you in the right direction.
· Upload your excel file to the dropbox in the Project folder on Moodle prior to midnight on Friday June 8th.
Total cash collections for the quarter
(Expected cash collections)
Total required production for the quarter (units)
Raw materials to be purchased for the quarter (ounces)
Ending cash balance
Total cost of goods manufactured for the quarter