CASE STUDY:
Bale Electric Company is a small, rapidly growing wholesaler of scientific calculators.
Carlo Ancellotti, Bale’s general manager of marketing, predicts sales of 100,000 units in January 2016, increasing by 10% each month during 2016. Sales revenue in January 2016 is forecast to be $800,000. The sales price will remain constant in 2016.Bale’s projected balance sheet as at 31 December 2015 is provided as follows:
$
Cash 50,000
Accounts receivable 472,500
Inventory 280,000
Buildings and equipment (net of accumulated depreciation) 307,500
Total assets $1,110,000
Accounts payable 315,000
Interest payable 12,500
Long-term borrowings (10%, due in 6 years time) 300,000
Share capital 361,400
Retained earnings 121,100
Total liabilities and shareholders’ equity $1,110,000
Cristiano Ronaldo (the assistant accountant) is now preparing a monthly budget for the first quarter of 2016, and he needs your help. So far, the following information has been accumulated:
Projected sales for December 2015 are $700,000. Credit sales typically are 75 per cent of total sales, with the remainder being cash sales. Bale’s credit experience indicates that 10 per cent of the credit sales are collected during the month of sale, and the remainder are collected during the following month.·
Bale’s cost of goods sold generally runs at 70 per cent of sales. Inventory is purchased on credit, and 40 per cent of each month’s purchases are paid during the month of purchase. The remainder is paid during the following month. In order to have adequate stocks of inventory on hand, the firm attempts to have inventory at the end of each month equal to one-half of the next month’s projected cost of goods sold.·
Ronaldo has already prepared the purchases budget for the quarter to save you some work:
Assessment 2 – Excel Assignment and Self Reflection on Teamwork
In addition, sales commissions run at the rate of 1 per cent of sales revenue.
On January 1, a one-off payment of $175,000 is required to purchase new equipment. No depreciation is required to be calculated for this new equipment.
Moyes believes that Bale needs to keep a minimum cash balance of $25,000.
A short-term credit facility for $100,000 has been arranged with a local bank. The bank has given Bale an introductory offer of 0% interest for the first three months of 2016, although interest of 12% per annum will be charged for amounts borrowed after 31 March 2016.
Moyes has decided that Bale should re-pay any short term loan required as soon as possible.
Interest on Bale’s long-term borrowings is paid semi-annually, on 31 January and 31 July, for the preceding six-month period.
REQUIRED:
You are required to prepare Bale’s budget for the first quarter of 2016 by completing the following schedules and statements using an Excel spreadsheet:
1. Sales budget in units and revenue (1 mark)
2. Cash receipts budget (3 marks)
3. Cash payments budget (4 marks)
4. Summary cash budget (4 marks)
5. Prepare Bale Electric’s budgeted Income Statement for the first quarter using a standard absorption costing approach (Ignore income taxes)
(4 marks)
6. Prepare Bale Electric’s budgeted Balance Sheet as at 31 March (Hint: On 31 March 2016, interest payable on long-term borrowings is $5,000) (4 marks)
4 marks are allocated for presentation and use of formulas in excel – please automate the spreadsheet so it can easily be used for scenario planning or ‘what-if’ analysis (simulation).
For questions 7 – 9 combined, the number of words should NOT exceed 1,000 in total.
7. Explain the main purposes of budgeting within an organisation
(5 marks)
8. Explain why it is important to prepare a cash budget in particular and what the problems are with having too little and too much cash
(5 marks)
9. Advise Bale’s senior management team how they can manage their cash position more effectively to minimise the problems identified in question 8 above
(5 marks)
4 marks are allocated for presentation, grammar, spelling and appropriate Harvard referencing where needed.
2 marks are allocated for appropriate teamwork as evidenced by your minutes and any other observations made by your lecturer.
Total Technical Mark = 45 marks, weighted to 15% overall
Assessment 2 – Excel Assignment and Self Reflection on Teamwork
Points to follow when creating your team excel workbook:
1. Create your excel workbook for the assignment - It is best to create one workbook and name each worksheet in the workbook with the question number, instead of creating many different files for each question. This will create consistency in submission and only one file per team per assignment will need to be submitted
2. Naming the workbook and worksheets - Name the workbook with a student name in the group and their number and save under this name. Name the worksheet in the workbook with the question number.
3. Each question must begin on a fresh worksheet not continued from the previous question.
4. Minutes of the meeting must be attached for group assignment separate on a word document.
5. Cover page for assignment – make sure you have a cover page with a list of the student names and numbers in the team.
6. Use the excel formulas in your worksheet. Do not use calculators to arrive at the answer.
Team Meetings
The team should have a minimum of three meetings (the team may have as many meetings as they need to) and should maintain minutes of those meetings which should be included with the team assignment as an attachment.
The minutes of meetings should (at the very least) contain -
1) Where and when the meeting was held
2) People present; absent; apologies
3) What was discussed, any disagreements/conflicts, points that were agreed on;
3) The follow up action relating to each member of the team;
4) Any other relevant matters, anything else that is significant to the successful completion of the assignment.
An agenda template and a template for minutes of meeting are attached (after Part 2 of the assignment information) for your team to use.
PART 2: Professional Competency Skills (Assessable Value 10%)
Individual Reflection on Team work
Word count: 750 words
Aim: The content task in this assignment is completed in teams. It is important that you reflect on this experience to learn about the ways in which teams work and the ways that you work in teams.
Refer to the content in the power point presentation on team work and check management and communication text books for further detail on team communication. Your reflection should incorporate the information from your reading as you will use relevant theory to critically reflect on your team experience.