Natural Care Corporation
Natural Care Corporation
Natural Care Corporation a distributor of natural cosmetics is ready to begin its third quarter, in which peak sales occur. The company has requested a $60,000, 90-day loan from its bank to help meet cash requirements during the quarter. Because Natural Care has experienced difficulty in paying off its loans in the past, the bank’s loan officer asked the company to prepare a cash budget for the quarter. In response to this request, the following data have been assembled:
A. On July 1, the beginning of the third quarter, the company will have a cash balance of $43,000.
B. Actual sales for last two months and budgeted sales for the third quarter follow ( all sales are on account):
A. On July 1, the beginning of the third quarter, the company will have a cash balance of $43,000.
B. Actual sales for last two months and budgeted sales for the third quarter follow ( all sales are on account):
May (Actual) $360 000
June (Actual) $280 000
July (Budgeted) $350 000
August (Budgeted) $420 000
September (Budgeted) $360 000
Past experience shows that 25% of a month’s sales are collected in the month of sale, 70% in the month following sale, and 2% in the second month following sale. The remainder is uncollectible.
C. Budgeted merchandise purchases and budgeted expenses for the third quarter are given below:
July August September
Merchandise purchases $170,000 $155,000 $165000
Salaries and wages $70,000 $70,000 $65,000
Advertising $80,000 $90,000 $100,000
Rent Payments $30,000 $30,000 $30,000
Depreciation $40,000 $40,000 $40,000
Merchandise purchases are paid in full during the month following purchase. Accounts payable for merchandise purchases on June 30, which will be paid during July, total $160,000.
D. Equipment costing $25,000 will be purchased for cash during July.
E. In preparing the cash budget, assume that the $60,000 loan will be made in July and repaid in September. Interest on the loan will total $2,000.
Required:
1. Using an excel sheet that shows the relevant formulas; prepare a schedule of expected cash collections for July, August and September and for the quarter in total.
(2 Marks)
2. Using an excel sheet that shows the relevant formulas; prepare a schedule of cash payments for July, August and September and for the quarter in total.
(2 Marks)
3. Using an excel sheet that shows the relevant formulas; prepare a cash budget, by month and in total, for the third quarter.
(4 Marks)
4. If the company needs a minimum cash balance of $20,000 to start each month, can the loan be repaid as planned? Explain. Present your answer in a word document file.
(2 Marks)
(Total Marks: 10)
IMPORTANT REMARKS
Submission deadline is Sunday 22ed December 2013. Each member (student) should join a group on the pre-set groups on the blackboard until forming a group of maximum 3 members. When submitting your final project report you MUST state your group number that you joined on the blackboard. It is assumed and expected that each member of the group should do a fair share of the work. The report is a joint responsibility, and each member is responsible for numerical accuracy, presentation, and standard of writing: marks awarded for the report will be the same for ALL members. If there are problems within the group which you cannot resolve, please discuss with your instructor IMMEDIATELY as no late complaints will be considered. If the problems are not resolved and it is felt there has been an unequal contribution, then, exceptionally, you should inform your instructor of the proportion of the work which has been contributed by each member.
ACADEMIC INTEGRITY:
You should use your own words, not quote phrases from textbooks and lecture handouts. It is expected that you will use textbooks to obtain an understanding of Budgeting (chapter 8 in the core text book is relevant). If you use textbooks as the source of ideas for your report, you must provide a reference. Not to do so would be plagiarism – a breach of regulations with serious penalties.
SUBMISSION OF REPORT:
ALL THREE MEMBERS should submit the project online by the due date; SUNDAY 22ed December 2013. Each submitted project report should include a front page stating your group number on the blackboard and the full names and student ID numbers of all the members who joined you. Please note that no extension to this date is permitted, so make sure you plan your time so there is no last minute rush. Assignments submitted after this date will not be marked. However, if you experienced a serious and unforeseeable problem which prevented you from submitting your work on time, you should consult the instructor IMMEDIATELY.
INSTRUCTIONS:
The answers provided MUST be in the form of an excel file to represent all the required budgets plus a word document to address the point mentioned in the fourth requirement above. Word count for the word document is between 400-600 words. The report will count for 10% of the final course mark.
FORMAT OF WORD DOCUMENT (REPORT) WHICH YOU MUST ADHERE TO:
The report should have a title page (showing the project’s title as above, students’ names, students’ IDs), followed by an appropriate answer relevant to this requirement being addressed. You should also cite any references that you may have used to defend your argument.
Use TIMES NEW ROMAN as the font, size 12, and lines are to be 1.5 spaced NOT SINGLE SPACED, also ADJUST THE LINES IN ALL THE PARAGRAPHS. CLICK HERE FOR MORE ON THIS TOPIC
June (Actual) $280 000
July (Budgeted) $350 000
August (Budgeted) $420 000
September (Budgeted) $360 000
Past experience shows that 25% of a month’s sales are collected in the month of sale, 70% in the month following sale, and 2% in the second month following sale. The remainder is uncollectible.
C. Budgeted merchandise purchases and budgeted expenses for the third quarter are given below:
July August September
Merchandise purchases $170,000 $155,000 $165000
Salaries and wages $70,000 $70,000 $65,000
Advertising $80,000 $90,000 $100,000
Rent Payments $30,000 $30,000 $30,000
Depreciation $40,000 $40,000 $40,000
Merchandise purchases are paid in full during the month following purchase. Accounts payable for merchandise purchases on June 30, which will be paid during July, total $160,000.
D. Equipment costing $25,000 will be purchased for cash during July.
E. In preparing the cash budget, assume that the $60,000 loan will be made in July and repaid in September. Interest on the loan will total $2,000.
Required:
1. Using an excel sheet that shows the relevant formulas; prepare a schedule of expected cash collections for July, August and September and for the quarter in total.
(2 Marks)
2. Using an excel sheet that shows the relevant formulas; prepare a schedule of cash payments for July, August and September and for the quarter in total.
(2 Marks)
3. Using an excel sheet that shows the relevant formulas; prepare a cash budget, by month and in total, for the third quarter.
(4 Marks)
4. If the company needs a minimum cash balance of $20,000 to start each month, can the loan be repaid as planned? Explain. Present your answer in a word document file.
(2 Marks)
(Total Marks: 10)
IMPORTANT REMARKS
Submission deadline is Sunday 22ed December 2013. Each member (student) should join a group on the pre-set groups on the blackboard until forming a group of maximum 3 members. When submitting your final project report you MUST state your group number that you joined on the blackboard. It is assumed and expected that each member of the group should do a fair share of the work. The report is a joint responsibility, and each member is responsible for numerical accuracy, presentation, and standard of writing: marks awarded for the report will be the same for ALL members. If there are problems within the group which you cannot resolve, please discuss with your instructor IMMEDIATELY as no late complaints will be considered. If the problems are not resolved and it is felt there has been an unequal contribution, then, exceptionally, you should inform your instructor of the proportion of the work which has been contributed by each member.
ACADEMIC INTEGRITY:
You should use your own words, not quote phrases from textbooks and lecture handouts. It is expected that you will use textbooks to obtain an understanding of Budgeting (chapter 8 in the core text book is relevant). If you use textbooks as the source of ideas for your report, you must provide a reference. Not to do so would be plagiarism – a breach of regulations with serious penalties.
SUBMISSION OF REPORT:
ALL THREE MEMBERS should submit the project online by the due date; SUNDAY 22ed December 2013. Each submitted project report should include a front page stating your group number on the blackboard and the full names and student ID numbers of all the members who joined you. Please note that no extension to this date is permitted, so make sure you plan your time so there is no last minute rush. Assignments submitted after this date will not be marked. However, if you experienced a serious and unforeseeable problem which prevented you from submitting your work on time, you should consult the instructor IMMEDIATELY.
INSTRUCTIONS:
The answers provided MUST be in the form of an excel file to represent all the required budgets plus a word document to address the point mentioned in the fourth requirement above. Word count for the word document is between 400-600 words. The report will count for 10% of the final course mark.
FORMAT OF WORD DOCUMENT (REPORT) WHICH YOU MUST ADHERE TO:
The report should have a title page (showing the project’s title as above, students’ names, students’ IDs), followed by an appropriate answer relevant to this requirement being addressed. You should also cite any references that you may have used to defend your argument.
Use TIMES NEW ROMAN as the font, size 12, and lines are to be 1.5 spaced NOT SINGLE SPACED, also ADJUST THE LINES IN ALL THE PARAGRAPHS. CLICK HERE FOR MORE ON THIS TOPIC
No comments:
Post a Comment