Balance Sheet Information for Lextra Office Supplies

The questions are requires that all supporting figures are prepared using a spreadsheet and the Formula spreadsheet in all instances where you have used the spreadsheet package.
Part A - Lextra Office Supplies
The following information is provided for Lextra Office Supplies:
a. Balance sheet information as of 30 June, 2008:
Assets
Cash $ 24 000
Accounts receivable 54 000
Allowance for doubtful Debts (7 500)
Inventory 69120
Equipment (net) 200 000
Liabilities 0
b. Recent and predicted sales in 2008:
June $80 000
July $96 000
August $120 000
September $160 000
October $72 000

c. Sales: Sales are 30% for cash and 70% on credit. All credit sales are collected as follows:
• 50% in the month of the sale
• 30% in the month following the sale
• 15% two months following the sale
• 5% is declared as doubtful
Note: Lextra only started using an Allowance for Doubtful Debts at the end of May. The accounts receivable at the end of June represents amounts outstanding from May and June sales.
d. Gross profit averages 40% of revenues. Lextra treats cash discounts on purchases in the income statement as a contra account against purchases.
e. Operating costs: salaries and wages average 20% of the monthly revenues; rent 5%; other operating costs, excluding depreciation 3%. Assume that these costs are dispersed each month. Depreciation each month is $2 000.
f. Purchases: Lextra's policy is to have enough inventory on hand at the end of each month to cover 120% of the following months sales. Terms on purchases are 2/10, n/30. All payments are made to take full advantage of any discounts. No purchases are made in the last half of any month.
g. Furniture and Fittings: In July, $3 000 is spent on purchasing new Furniture and Fittings andin September $8 000 is spent on new Equipment.
h. Minimum Cash balance: The firm assumes a minimum cash balance of $16 000 is to be maintained at the end of each month.
i. Bank arrangements: All borrowings are assumed to be effective from the start of each month and all repayments are made at the end of the month. The bank will only lend in multiples of $1 000. Repayments of capital are also to be made in multiples of $1 000. Repayments can only be made when the firm has more money than the required balance in cash at bank. Interest is paid monthly at a rate of 15% per annum.
Required:
(a) Using a spreadsheet, prepare the following schedules and budgets for the first quarter of the 2008-2009 financial year.
i. Sales budget showing a breakdown of cash and credit sales. ii. Budgeted monthly cash collection schedule
iii. Budgeted monthly cash disbursements for purchases. You will need to do a purchases schedule first.
iv. Budgeted cash disbursements for operating costs v. Cash budget showing the financing required

(b) How do you think Lextra should finance any shortfalls they have with cash? Explain your reasoning.
(c) Prepare a budgeted Income Statement for the first quarter and a budgeted Balance Sheet as at 30 September for Lextra. Ignore taxes.
(d) Someone once said 'budgets are only as good as the assumptions made'.
i. What does this mean?
ii. Do you agree with this statement?
iii. What assumptions would you have made when preparing the budget above? Explain why you would have made these assumptions.
(e) Often in participative budgeting, those participating will build some 'budgetary slack' into the figures.
i. What is 'budgetary' slack?
ii. What steps can be taken to prevent budgetary slack?
iii. Is there an ethical issue if staff build in budgetary slack?
Note: Make sure all answers have full explanations.
Part B - Pascall Corporation
Pascall Corporation manufactures and sells metal sheeting. Operations began on the 1 January, 2009
Costs incurred for 2009 are as follows:
Direct material costs $280 000 V
Direct manufacturing labour costs 60 000 V
Plant electricity costs 10 000 V
Indirect manufacturing labour costs 20 000 V
Indirect manufacturing labour costs 32 000 F
Other indirect manufacturing cost 16 000 V
Other indirect manufacturing costs 48 000 F
Marketing, distribution, and customer -service costs 245 700 V
Marketing, distribution, and customer -service costs 80 000 F
Administrative costs 100 000 F
V = variable F = fixed
Note: Variable manufacturing costs are variable with respect to units produced. Variable marketing, distribution, and customer-service costs are variable with respect to units sold.
Data relating to inventory is as follows:
1 January, 2009 31 December, 2009
Direct materials 0 metres 4 000 metres
Work in process 0 units 0 units
Finished goods 0 units To be determined
Production in 2009 was 200 000 units. 4 metres of direct material are used to make one unit of finished product.
Revenues in 2009 were $873 600. The selling price per unit and the purchase price per metre of direct materials were stable throughout the years. The company's ending inventory of finished goods is carried at the average unit manufacturing costs for 2009. Finished goods at 31 December was $41 940.
Required: Calculate the direct material inventory, total cost, 31 December, 2009.
(a) Calculate the direct material inventory total cost , 31 December 2009 and calculate the finished goods inventory total units,
(b) Calculate the selling price per unit, 2009.
(c) Calculate the operating income for 2009. Prepare an Income statement using the contribution margin approach.
(d) Calculate the break-even point for 2009.
(e) Comment on whether you believe there is a healthy margin-of-safety.
(f) You have been asked to prepare a budgeted Income Statement for 2010. Management predicts the following will occur in 2010:
i. Selling price per unit will rise by 5%
ii. Variable costs will rise by 10%
iii. Fixed marketing, distribution and customer-service costs will remain the same iv. Sales are forecast to be 244 000 units
v. The desired ending inventory of finished goods on 31 December is 24 000 units
vi. The ending inventories for direct material and work in process will be the same as the ending inventories in 2009
vii. The company's ending inventory is carried at the average unit manufacturing costs for 2010
viii. Do you think it is worth while carrying out a sensitivity analysis on this budgeted Income Statement? Why? Write a brief memo to management commenting on the type of sensitivity analysis you might carry out and why you would do this. Be specific with the variables you may want to change and why.

General assignment query answers
Q: There are no units so we cannot do the sales budget and production schedule (budgets). What do we do?
A: Do you really need units? Do you have enough information to do it without units?
Q: We are not given a purchase price so what do we do?
A: Are you given additional information that allows you do calculate the cost value? You may need to think of a different angle or a different relationship that involves the cost price of inventory.
Q: Can you explain some of the terminology?
A: A sales budget looks at sales only nothing else. Another name for a sales budget is a revenue budget. This budget does not look at how much money is collected and when.
A cash collections budget looks at how much money is collected and when it is collected.
Q: Is there something I can follow for setting the budget out.
A: There really is no absolute hard and fast way to set anything out. If you look at the comprehensive example in your text on pages 256-260 it basically shows you the setting out but remember this example is for one month only. When it comes to setting out as long as you have all the information in the budget and it can be followed then you are probably OK.
Personally when I do budgets my set out goes along these lines [NOTE: These are examples only]:
Sales Budget:
Units
SP/unit
Total sales
or
Cash sales
Credit sales
Total sales
Cash Collections Budget:
Cash sales
% of current month credit sales
% of last months credit sales
% of two months ago credit sales
Total cash collected
Production Budget:
Sales
+ Ending Inventory
= Amount required
- Beginning inventory
= Anmount to be manufactured
This format can also be used for calculating what has to be purchased.
Q: With the Accounts Receivable at the end of June, how do we know what belongs to May and what belongs to June?
A: I can't asnwer this question outright but I can give you some clues:
1. You have been given the June sales so you can calculate exactly what portion of June is owing and anything left over must be from May.
2. You can do the same thing with your Allowance for Bad and Douutful Debts. You can calculate the June portion and the reminder must be May.
3. Remember your Accounts receivable reflects all money owing even if it is deemed to be 'doubtful'.
4. Also remember that they only decided to use an allowance account at the end of May so there may be some differences between May and June so take the May figures as a given rather than try and work out total sales etc.
Q: I don't understand how there seems to be two inventory accounts. Why is this?
A: Retailers have one inventry account only as they buy in goods ready to sell. Manufacturers have three inventory accounts as they buy in materials; convert them to saleable products and then sell them. So they will have an inventory for the materials purchased and not yet used - Material Inventory; an inventory for the jobs they have started but not yet finished - Work in Progress Inventory and an inventory for the goods they have finished but not yet sold - Finished Goods Inventory.
Q: I am unsure of how to set out the financing area of the budget. What should I follow?
A: There is a fairly good example on page 265 of your text. I just do the following:
Year 1 Year 2
Cash Receipts:
details

200000 180000
Cash Payments:
details

190000 150000
Excess/Deficit 10000 30000
Opening Cash Balance 5000 20000
Available 15000 50000
Loan 5000 0
Loan repayments 0 -1000
Interest repayments 0 -9 rounded from 8.33
Ending Cash Balance 20000 48991

Assume minimum balance is $20000
Assume loan is at 10% interest
Q: When is the loan actually taken out for financing?
A: If you get to the end of June and you have a shortage and need to take out a loan, you assume that hwile it is written into your June cash budget, it is taken from the first day in July. This means you will have to pay interest at the end of July.
Q: How do I calculate the interest on the loan?
A: Just straightforward interest on your loan eg 10% of $20 000. Then you can either divide by 12 for a months interest or you can do it on a daily basis and divide by 365 and multiply by the number of days in your month. Either approach will be accepted in the assignment.
Q: What should I do about rounding?
A: In terms of rounding, if you are looking at say a cost per unit then leave it at two decimal places. If you are looking at units to break even then always round up as you can only produce a whole unit. As I recall there was some rounding to do Part B of the assignment. I would also tend to keep my Income statement in whole dollars in this part of the assignment.