Budgeting project

Sweet Home Inc.

Sweet Home Inc. (SWI) manufactures one product, area rugs, which it sells for US$120. Because of the large size of the flag, this product is not sold in stores; rather it is sold through a relatively small number of online retailers. Each quarter, retailers estimate sales for the upcoming five months, revising proximate sales as necessary. In general, the retailers are reasonably good at estimating their sales needs, but some variation in demand does occur, and the retailers expect to be able to adjust orders as needed. Sales estimates are 2,500 units in April, 6,000 units in May, 3,000 units in June, 2,500 units in July, and 2,000 units in August. Customers historically have paid 40% of their purchases in the month of the sale, 55% in the following month, and the remaining 5% is uncollectible. The flags are made in one plant, which has a capacity of 6,200 units per month. SWI budgets have 20% of next month’s sales in finished goods inventory at the end of each month. There is plenty of storage space for finished goods.

Fabric is the only direct material and each flag requires five pounds of fabric at US$7 per pound. SWI plans to have 40% of next month’s fabric needs on hand at the end of the month. Fabric is purchased on credit with 40% paid in the month of purchase and 60% paid the next month. The standard direct labor hours to manufacture one flag is 0.50 hours at US$20 per hour. For simplicity, direct labor costs are budgeted as if they were paid when incurred. Manufacturing overhead rates are computed quarterly and applied based on direct labor hours. Fixed manufacturing overhead costs are estimated to be US$57,950 per month, of which US$20,000 is property, plant, and equipment (PPE) depreciation. Variable manufacturing overhead, including indirect materials, indirect labor, and other costs, is estimated at US$5 per direct labor hour. The selling and administrative expenses include variable selling costs (primarily shipping) of US$1.25 per unit and fixed costs of US$63,550 per month, of which US$10,000 is depreciation of the administrative office building and equipment.

SWI uses first in, first out (FIFO) inventory valuation. As of March 31, the expected finished goods inventory is 410 units, valued at US$75 per unit. The company expects to have 4,600 pounds of fabric on hand, valued at US$7 per pound. Other expected account balances include accounts payable at US$55,000, accounts receivable at 132,000, cash at US$37,745, land at US$520,000, and building and equipment at US$1,800,000 with accumulated depreciation of US$750,000. SWI has no long-term debt; common stock is valued at US$500,000 and is not expected to change during the quarter; expected retained earnings as of March 31 are US$1,247,695. SWI budgets for US$30,000 ending cash balance each month and is requesting a line of credit that will allow it to adjust for its cash needs.

SWI has requested a line of credit of US$60,000 to cover production costs during the seasonal increase in business. Kent Bank uses the following terms on its lines of credit. All borrowing is done at the beginning of the month in whole dollar increments. All repayments are made at the end of the month in whole dollar increments. The full line of credit is expected to be paid off by the end of the quarter with all the interest repaid at the end of the quarter. The interest rate on this loan is 16% per year.

Using the data input provided, prepare SWI’s master budgets in Excel.

Sweet Home Inc.
Sweet Home Inc. (SWI) manufactures one product, area rugs, which it sells for US$120.
Because of the large size of the flag, this product is not sold in stores; rather it is sold through a
relatively small number of online retailers. Each quarter, retailers estimate sales for the
upcoming five months, revising proximate sales as necessary. In general, the retailers are
reasonably good at estimating their sales needs, but some variation in demand does occur, and
the retailers expect to be able to adjust orders as needed. Sales estimates are 2,500 units in April,
6,000 units in May, 3,000 units in June, 2,500 units in July, and 2,000 units in August. Customers
historically have paid 40% of their purchases in the month of the sale, 55% in the following month,
and the remaining 5% is uncollectible. The flags are made in one plant, which has a capacity of
6,200 units per month. SWI budgets have 20% of next month’s sales in finished goods inventory
at the end of each month. There is plenty of storage space for finished goods.
Fabric is the only direct material and each flag requires five pounds of fabric at US$7 per
pound. SWI plans to have 40% of next month’s fabric needs on hand at the end of the month.
Fabric is purchased on credit with 40% paid in the month of purchase and 60% paid the next
month. The standard direct labor hours to manufacture one flag is 0.50 hours at US$20 per hour.
For simplicity, direct labor costs are budgeted as if they were paid when incurred. Manufacturing
overhead rates are computed quarterly and applied based on direct labor hours. Fixed
manufacturing overhead costs are estimated to be US$57,950 per month, of which US$20,000 is
property, plant, and equipment (PPE) depreciation. Variable manufacturing overhead, including
indirect materials, indirect labor, and other costs, is estimated at US$5 per direct labor hour. The
selling and administrative expenses include variable selling costs (primarily shipping) of US$1.25
per unit and fixed costs of US$63,550 per month, of which US$10,000 is depreciation of the
administrative office building and equipment.
SWI uses first in, first out (FIFO) inventory valuation. As of March 31, the expected
finished goods inventory is 410 units, valued at US$75 per unit. The company expects to have
4,600 pounds of fabric on hand, valued at US$7 per pound. Other expected account balances
include accounts payable at US$55,000, accounts receivable at 132,000, cash at US$37,745, land
at US$520,000, and building and equipment at US$1,800,000 with accumulated depreciation of
US$750,000. SWI has no long-term debt; common stock is valued at US$500,000 and is not
expected to change during the quarter; expected retained earnings as of March 31 are
US$1,247,695. SWI budgets for US$30,000 ending cash balance each month and is requesting a
line of credit that will allow it to adjust for its cash needs.
SWI has requested a line of credit of US$60,000 to cover production costs during the
seasonal increase in business. Kent Bank uses the following terms on its lines of credit. All
borrowing is done at the beginning of the month in whole dollar increments. All repayments are
made at the end of the month in whole dollar increments. The full line of credit is expected to be
paid off by the end of the quarter with all the interest repaid at the end of the quarter. The interest
rate on this loan is 16% per year.
Using the data input provided, prepare SWI’s master budgets in Excel.