Cat’s Cradle Inc.
Your Task
You were recently hired as an assistant controller at Cat’s Cradle Inc.(“Cat’s Cradle”). Your employer manufactures and sells a single product, unique and entertaining cat sleeping hammocks. The sleeping hammocks use a special antimicrobial fa
ic and are well known for being extremely comfortable and durable for cats. As cat sleeping hammocks have become the new popular item during the COVID era, demand has been booming. With the popularity of pets during the pandemic, management expects demand to remain strong for the foreseeable future.
The first task you have been assigned in your new role is to prepare the master budget for the quarter ended July 31st, 2021.
You have assembled the following information.
Information on sales
The sleeping hammocks sell for $25 each. Recent and budgeted sales (in units) are as follows:
March (actual) 12,500
April (actual) 13,400
May 10,250
June 8,100
July 5,500
August 3,100
September 2,500
All sales are on credit, with no discount. The company has found that only 50% of a month’s sales are collected by month-end. An additional 30% is collected in the month following the sale, and the remaining 20% is collected in the second month following the sale. Bad debts have been negligible, so they can be ignored for the purposes of the budget.
Information on inventory and production
Inventories of finished goods on hand at the end of each month are to be equal to 50% of the following months budgeted sales. As of April 30th, 2020, the company had 5,125 sleeping hammocks in finished goods inventory. The company has no work-in-process (WIP) inventory.
Each hammock requires 1/10th of a yard of antimicrobial silver ion fa
ic, which the company purchases for $55.00 per yard. Note – both the hammock and the straps are made from this same fa
ic so there is only one raw material used to make the product. It is company policy to keep enough fa
ic in raw materials to meet 70% of the next month’s production needs. As of April 30th, Cat’s Cradle had 642 yards of fa
ic in raw materials inventory. Purchases of raw materials are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month.
Each hammock requires 0.5 labour hours (30 minutes) to assemble by hand. Employees who make the sleeping hammocks are paid $16.00 per hour, and never work overtime. The company has enough casual/on-call 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 of manufacturing overhead is $2.25 per hammock in production, and the fixed component is $25,000 per month. The $25,000 of fixed manufacturing overhead includes depreciation of $5,000 per month on the sewing machines and graphic print machines used to manufacture the sleeping hammocks. Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced.
Information on selling and administrative expenses and cash outlays
Cat’s Cradle’ other monthly operating expenses are given below:
Variable:
License
$1.00 per hammock sold **
Fixed:
Wages and salaries
$10,000
Utilities
$2,500
Insurance
$800
Depreciation (S&A)
$1,200
Miscellaneous
$1,000
**All sleeping hammocks have a print of a character popular with cats. Mortimer Mouse and David Duck are the most popular prints for cat hammocks. Doofy and Massive Mouse are less popular but still sell well. The license amount is paid directly to the companies with the exclusive rights to the characters.
All operating expenses are paid during the month in cash, with the exception of the depreciation, as it is not a cash expense. Due to the planned introduction of a new hammock line for dogs in 2022, the company will be purchasing a new set of industrial sewing machines in May for $33,000 and a new print machine in June for $12,000. The equipment will not be used until XXXXXXXXXXThe machines were purchased ahead of schedule, as Cat’s Cradle did not want to experience any delays in case a future shutdown. The company has a policy to declare a dividend of $78,000 on the last day of each quarter, which will be paid in the first month of the next quarter.
Balance Sheet of Cat’s Cradle Inc.at April 30th, 2020:
Assets
Cash
XXXXXXXXXX,500
Accounts receivable
XXXXXXXXXX,500
Inventory - Raw Materials
XXXXXXXXXX,704
Inventory - Finished Goods
XXXXXXXXXX,515
PP&E
XXXXXXXXXX,000
Accumulated depreciation
XXXXXXXXXX,000)
183,000
TOTAL ASSETS
$415,219
Liabilities and Equity
Bank loan
-
Accounts payable, purchases
XXXXXXXXXX,973
Dividends payable
XXXXXXXXXX,000
Capital stock, no pa
XXXXXXXXXX,000
Retained earnings
XXXXXXXXXX,246
TOTAL LIABILITIES AND EQUITY
$415,219
Additional information on cash requirements and financing
Management of Cat’s Cradle requires a minimum ending cash balance each month of $22,000. The company can bo
ow money from its bank at 5% annual interest. All bo
owing must be done at the beginning of a month, and repayments must be made at the end of a month. Bo
owings and repayments of principal must be in round $1,000 amounts. Interest is computed and paid when any repayments occur. Round all interest payments to the nearest whole dollar. Compute interest using whole months, not days. The company wishes to use any excess cash to pay loans off as rapidly as possible.
REQUIRED:
Prepare the following for the company for the quarter ending July 31st:
0. Sales Budget and Schedule of Expected Cash Collections
0. Production Budget
0. Direct Materials Budget and Schedule of Expected Cash Disbursements
0. Direct Labour Budget (inclusive of expected labour disbursements)
0. Manufacturing Overhead Budget (inclusive of expected overhead disbursements)
0. Ending Finished Goods Inventory Budget
0. Selling and Administrative Budget
0. Cash Budget
0. Schedule of Cost of Goods Manufactured
0. Budgeted Income Statement
(with the cost of goods sold computation included in the income statement)
You may ignore income taxes for the purposes of this budget
0. Budgeted Retained Earnings Statement
0. Budgeted Balance Sheet at July 31st, 2021
NOTES
Preparing the schedules
For items (a) through (h) show each of the 3 months and a total column for the whole quarter (ended July 31). It may be helpful to prepare (a) through (g) on one individual worksheet in Excel, to make building and referencing easier.
For items (i) through (k), show only for the quarter ended July 31st, 2021.
All schedule and statements should be formatted professionally.
Use Excel to do the work. Use cell references and formulas in your schedules – you will thank yourself when/if you need to make any changes. You will be able to update certain numbers and have the changes instantly flow through the rest of the schedules. Think about this – have you already entered that number once before, or was it calculated somewhere else? If it was calculated elsewhere, use a cell reference to the first place it shows up. Don’t just type it in.
Submission
Upload your excel file to the dropbox in the Master Budget Project folder on Moodle, prior to 11:55 PM on Saturday, June 12th, 2021.
Check figures:
Check figure
Amount
Schedule
Total cash collections for the quarte
$ 717,000
(Expected cash collections)
Total required production for the quarter (units)
20,275
(Production)
Raw materials to purchase for the quarter (total yards)
1,581.25
(Direct materials)
Ending cash balance
$176,424
(Cash budget)
Total cost of goods manufactured for the quarte
$394,331
(Schedule of COGM)
Total assets
$536,000
(Balance sheet)