Accounting
AC101– Accounting 1
Assessment item 2— Practical & Written Assignment
|
Due date: |
11:45pm, Friday (13.5.2022) |
|
Weighting: |
30% |
|
Format: |
Submit one EXCEL file |
Assessment-2 Instructions
Assessment-2 is a group assignment, and each group consists of not more than 5 students.
Nominate just one group member to submit the assignment however the name of all group member names must be on the cover sheet.
Cover page information: Need to provide the following details on the first page of your Excel file.
-
S. No.
Student ID
Student Name
1.
2.
3.
4.
5.
All group members are responsible for their groups submission as no exceptions will be provided for late or incomplete submission.
Please ensure the academic integrity of your submission.
If a student in a group has not contributed, other group members have the right to inform the lecturer.
Failure to submit your assessment by the due date constitutes non-submission and late penalties will apply.
Name the worksheet tag(s) at the bottom of the Excel screen with the question number. Use a separate excel worksheet page for each question that needs to be completed in Excel.
-
Excel sheet page
1
Cover page information (Pls provide name and student ID number of your group members)
2
Data section
3
Solution for Question 1 (Adjusting entries)
4
Solution for Question 2 (Worksheet)
5
Solution for Question 3 (Income statement, statement of changes in equity and classified balance sheet)
6
Solution for Question 4 current ratio and debt ratio
7
Part 2 (Case study) Answers
Part 1 (EXCEL application Questions):
Laroche Cleaning Services commenced operations on 1 July 2021. The unadjusted trial balance columns of the worksheet for Laroche Cleaning Services are as follows.
-
Laroche Cleaning Services
Unadjusted Trial Balance
as at 31 July 2021
Balance
Debit
Credit
Account title
Cash at bank
$24,100
Supplies
$2,400
Prepaid insurance
$3,600
Equipment
$4,800
Motor vehicles
$18,000
Bank loan
$20,000
Accounts payable
$400
Unearned service revenue
$5,500
Laroche, Capital
$30,000
Service revenue
$500
Wages expense
$1,600
Interest expense
$300
Advertising expense
$1,600
TOTAL
$56,400
$56,400
Additional information as at 31 July 2021:
Laroche Cleaning Services owes wages of $2,200 to employees, for services to 31 July.
Laroche Cleaning Services has invoiced a number of customers for cleanings services provided during July and needs to accrue service revenue of $12,600.
Accrued expenses as at 31 July include: fuel ($190) and telephone ($100) for the month of July.
Supplies on hand at 31 July 2018 amount to $2,100.
Laroche Cleaning Services provided cleaning services to ABC Public School on 31 July 2021, earning $500 of the $5,500 that was paid to Flash Cleaning Services on 31 July 2021.
1 July 2021 Laroche Cleaning Services purchased a motor vehicle – a van that will be used for the cleaning business. The motor vehicle cost $15,000, and the motor vehicle has an estimated useful life of six years.
1 July 2021 Laroche Cleaning Services paid $3,600 for insurance for the period 1 July 2021 –30 June 2022.
Required:
Prepare and journalise all of the adjusting entries for Laroche Cleaning Services as at 31 July 2021. (15 marks)
Using your ‘Unadjusted Trial Balance’ and adjusting entries prepared above, prepare the ten-column worksheet in Excel for the month ended 31 July 2021 (refer to page 162 – 174 of the prescribed text for an example of worksheet and financial statements). In the spreadsheet, you are required to have a ‘data’ section separated from the ‘worksheet’ section. Use formulas to generate all of the figures in the ‘worksheet’ from the ‘data’ section. Use the IF function for the ‘Profit’ or ‘Loss’ and also to check that the debit and credit columns balance. (20 marks)
Using the ten-column worksheet prepared above as your ‘data’, prepare the income statement, statement of changes in equity and classified balance sheet in Excel in a separate page. Use formulas to generate all of the figures in the financial statement reports. (14 marks)
AssignmentTutorOnline
Calculate and evaluate the business’s current ratio and debt ratio. If Laroche Cleaning Services repays the bank loan, how would these ratios change? Do you recommend that the bank loan is repaid? Why or why not. (10 marks)
Spreadsheet requirements
For spreadsheet (EXCEL) questions, ensure that you adhere to the spreadsheet requirements.
For good spreadsheet design, it is very important that you separate data from solution areas. A good spreadsheet solution allows the solution to be completely formula driven. There should be no data in the solution area.
Where appropriate, use the ‘IF function’ to provide built in checks of balances, net profit/net loss etc.
Part 2 – Case study (12 Marks)
You are the assistant controller in charge of general ledger accounting at Linbarger Bottling Company. Your company has a large loan from an insurance company. The loan agreement requires that the company’s cash account balance be maintained at $200,000 or more, as reported monthly.
At June 30, the cash balance is $80,000, which you report to Lisa Infante, the financial vice president. Lisa excitedly instructs you to keep the cash receipts book open for one additional day for purposes of the June 30 report to the insurance company. Lisa says, “If we don’t get that cash balance over $200,000, we’ll default on our loan agreement. They could close us down, put us all out of our jobs!”
Lisa continues, “I talked to Oconto Distributors (one of Linbarger’s largest customers) this morning. They said they sent us a cheque for $150,000 yesterday. We should receive it tomorrow. If we include just that one cheque in our cash balance, we’ll be in the clear. It’s in the mail!”
Required:
Who will suffer negative effects if you do not comply with Lisa Infante’s instructions? (3 marks)
Who will suffer if you do comply? (3 marks)
What are the ethical considerations in this case? (3 marks)
What alternatives do you have? (3 marks)
Additional marks (4 marks)
Overall presentation of work (Structure, adhering to the spreadsheet requirements, formatting, text boxes, and appropriate application of formulas)
Marksheet