ACTG5210P Assignment-
Sofia's Furniture ("SF") makes residential wet bar equipment from real wood and sells them through three sales outlets. The product line consists of two styles of bar stools, three styles of bar countertops and 4 styles of cabinets.
The accounting group at Sofia's Furniture prepares a master budget for each fiscal year. The fiscal year corresponds to the calendar year. It is December 2014, and the accountants are completing the budget for 2015.
The furniture sells for $200, $900, and $1,800 for the bar stools, bar countertops, and cabinets respectively. Customers pay one of three ways:
1. By cash, and receive a 5% discount
2. By credit card (the credit card company takes 3% of the revenue as its fee and remits the balance in the month following the month of the sale)
3. On account (only exporters buy on account)
The distribution of cash, credit card, and exporter sales is 25%, 35%, and 40% respectively. Of the credit sales to exporters, Sofia's Furniture collects 30% in the month following the sale, 50% in the second month following the sale, and 17% in the third month following the sale; with 3% going uncollected. SF recognizes the expense of the cash discounts, credit card fees, and bad debt in the month when the sale occurred.
SF employees 40 people: 15 in administration, sales, shipping; 2 in manufacturing supervision, 9 in manufacturing fabrication and assembly (carpenters); and 14 in manufacturing, finishing, and other areas (helpers, cleaners, and maintenance crew).
The carpenter hours required to manufacture a single unit are 0.4, 2.5, and 6 hours for a bar stool, countertop, and cabinet, respectively. In addition, each carpenter hour requires 1.5 helper hours. SF pays carpenters and helpers $24 and $14 per hour, respectively. The company also guarantees all employees 172 hours per month regardless of the hours of work available. When the employees are not doing their regular work, they undertake maintenance, training, and customer service activities. The company pays 150% of the normal hourly wage for every hour over 172 worked by the employee in the month. Management adds new carpenters if the projected total monthly overtime is more than 5% of the total regular carpenter hours available. SF has a policy of no employee layoffs, and any required hiring is done on the first day of each month.
SF's factory costs $600,000 per year in rent. The company pays rent quarterly beginning January 1 of each year. SF pays other fixed manufacturing costs, which include manufacturing supervision salaries, totalling $480,000 per year, paid in equal monthly amounts.
The capital investment policy is to purchase, each January and July, $5,000 of machinery and equipment per carpenter employed during that month. SF recognizes depreciation at the rate of 10% of the year-end balance of the machinery and equipment account. Furthermore - supplies, variable support, and maintenance costs vary with the number of carpenter hours worked and are $5, $29, and $15 per hour, respectively.
The units of wood required manufacturing bar stools, bar countertops and cabinets are 1, 8, and 15, respectively. Each unit of wood costs $30. The inventory policy is to make products in the month they will be sold. Inventory is received on a just-in- time basis, and the company pays for all materials, supplies, variable support, and maintenance items on receipt.
Annual administrative salaries, fixed selling costs, and planned advertising expenditures are $300,000, $360,000, and $600,000, respectively. SF makes these payments in equal monthly instalments. Packaging and shipping costs for bar stools, bar countertops and cabinets are $15, $65, $135, respectively. Variable selling costs are 6% of each product's list price. All these costs are paid as incurred.
SF maintains a minimum cash balance of $50,000 at all time. It does so by utilizing its line-of-credit if required. All line-of-credit transactions occur on the first day of each month. The bank charges interest on the line-of-credit account balance at the rate of 10% per year. SF pays interest on the first day of each month on the line-of-credit balance outstanding at the end of the previous month. On the first of each month, the bank pays interest at the rate of 3% per year on funds exceeding $50,000 in the company's cash account at the end of the previous month.
Actual sales for October and November, and expected sales for December 2014 appear in the following table:
|
Item
|
October
|
November
|
December
|
|
Bar Stools
|
900
|
975
|
950
|
|
Countertops
|
175
|
188
|
201
|
|
Cabinets
|
90
|
102
|
95
|
The accounting department has also come up with the following schedule, showing the demand forecast and sales plan for 2015:
|
Month
|
Bar Stools
|
Countertops
|
Cabinets
|
|
January
|
1020
|
200
|
109
|
|
February
|
1191
|
237
|
120
|
|
March
|
1179
|
243
|
119
|
|
April
|
1195
|
250
|
126
|
|
May
|
1200
|
252
|
122
|
|
June
|
1204
|
255
|
125
|
|
July
|
1194
|
242
|
123
|
|
August
|
1199
|
253
|
121
|
|
September
|
1222
|
243
|
127
|
|
October
|
1219
|
248
|
126
|
|
November
|
1207
|
244
|
126
|
|
December
|
1192
|
255
|
119
|
Lastly, the accounting department projects SF's balance sheet at January 1, 2015 to be as follows:
|
Sofia's Furniture Balance Sheet January 1, 2015
|
|
Cash
|
$ 50,000
|
Bank Loan
|
$ -
|
|
Accounts Receivable
|
$ 575,008
|
|
|
|
Machinery (net book value)
|
$360,000
|
Shareholders' Equity
|
$985,008
|
|
Total
|
$ 985,008
|
|
$985,008
|
REQUIRED:
1. Prepare a Monthly Sales Forecast, include units and dollar figures, as well as capture all the various discounts that reduce the gross revenue.
2. Prepare a Monthly Staffing Plan, showing both hours, dollars and the overtime impact.
3. Prepare an estimated Monthly Cash Flow Statement, separating cash inflows from cash outflows, and including interest paid or received from the bank.
4. Prepare a Pro Forma Income Statement for the year ended December 31, 2015. Separate Manufacturing Variable expenses from Fixed expenses and Other expenses.
5. Prepare a Pro Forma Balance Sheet as at December 31, 2015.