Practical Assessment: Written Assessment (Excel spreadsheet) and Presentation
30% of the total marks
Due on Week 6 Monday 15 December at 5 PM AEST
Group Work Note
NOTE: Complete this assignment in groups of three students. Please nominate one group member to submit the assignment. The names of all three group members and student IDs must be added to the cover sheet.
Cover page information: Copy and paste the information below on the first page of your Excel file.
- Group No.
- S. No. | Student ID | Student Name (in the order of video presentation)
1.
2.
3. - Link to Zoom presentation:
Assessment Structure
The Practical Assessment has two parts:
- Part A: Requires students to prepare a worksheet with adjustments, financial statements, closing entries, and ratio analysis in an Excel spreadsheet.
- Part B: An individual Zoom presentation by all group members.
Important note: "This assignment, designed with industry and the university's Learning Design Team, emphasises Excel and AI skills for accounting graduates, benefiting students by enhancing their job readiness and competitiveness. It will also help students develop communication skills via Zoom, teamwork through group work, leadership skills through individual video presentations, innovativeness in designing dashboards, and self-learning through recommended resources."
PART A – Preparation of accounting records using Gen AI and formula-embedded spreadsheets.
Assignment Questions:
Required:
- Using any Gen AI platform, create the unadjusted trial balance and formulate five adjustment entries for a sole-proprietor business in the third year of operations. You can use any figures/dates/months, and other details. Adjustment entries must cover a variety of transactions, for example, entries relating to Depreciation, unearned revenue or an error requiring a correction. Enter or copy-paste data into a data sheet in Excel to answer points 2-6 below.
- Using Excel formulae, prepare and complete the 10-column Worksheet.
- Using Excel formulae, prepare the income statement, the statement of changes in owners' equity, and the classified balance sheet.
- Using Excel formulae, prepare the closing entries.
- Calculate any two ratios for the business using Excel formulas and interpret them. Refer to week 11 resources for guidance.
- Use Excel formulas to prepare an interactive dashboard showing the ratios used in point 5 above and other financial data. These marks are individual, not group marks. If everyone in the group is doing a dashboard, there will be three different dashboards in three separate sheets in the Excel workbook. Students must name their individual Excel sheets with their first and family names. The video links given below will help you learn about dashboards. Please check ALC resources on Moodle for excel help.
- How to improve your formatting in Excel
- Make an Awesome Excel Dashboard in Just 15 Minutes
- Beginner's Guide to Excel Dashboards
PART B: To be attempted individually
Maximum video recording duration per student is 3 Minutes. Make sure you do not exceed the time limit. We will mark only the first 3 Minutes of the video for every student.
Only one Zoom recording needs to be shared for the group (not three recordings). Do not waste time by saying the course name, university, student ID or teacher's name. Please disregard any Turnitin uploading error message Moodle sends during the submission.
Each student in the group needs to explain below by sharing the screen:
(a) How is the dashboard interactive? Show how it updates automatically when data in the data section is revised.
(b) What problem your business is facing and how do you think the dashboard information under Q6 above (part A) helps the business make decisions?
(c) Which Gen AI did you use to create unadjusted Trial balance and adjustment entries? Share logic for selecting a particular Gen AI and prompts via screen sharing.
(d) What aspects of your assignment could not be completed by Gen AI? Share your experience. For example, were you able to make dashboards or embed formulae into Excel sheets using the Gen AI of your choice?
(e) What did you learn from this assignment regarding the ethical and responsible use of AI as an accountant?
Student Survey
Please complete a 5-10 minute survey on the link below about your experience of using Gen Ai in this assessment.
https://cqu.syd1.qualtrics.com/jfe/form/SV_8kahm8g5PTm30Zo
or use this QR code.
Video Submission Requirements
You must ensure that:
(a) Markers can see every student completing their part of the live Zoom video recording.
(b) Every student needs to identify themselves at the beginning of their video recording and write their name in the order of their presentation with the submission. Group members must present in the sequence as per the cover page.
(c) You are not required to submit any PPT or any written response.
(d) Students must demonstrate understanding by sharing the screen with Excel worksheets while recording their videos on Zoom.
Assignment Instructions
- This is a group assignment for all, including on-campus and Flex/DST students, and each group consists of no more than three students.
- Late submissions will be penalised as per CQU policies.
- Your lecturer has/will place all students in groups.
- All group members are responsible for their group's submission, as no exceptions will be provided for late or incomplete submissions in case of difficulties managing group members in different time zones or with varied work/life commitments. If a team member has not contributed relatively to the assignment, please inform the lecturer ASAP. We will keep your name confidential.
- Although this is a group assignment, I strongly recommend that every group member understands and contributes all the worksheet elements, adjustment entries and financial reports.
- You must ensure the academic integrity of your submission.
- I encourage students to use Pivot functions/Vlook up.
Excel Workbook Structure
Name the worksheet tab(s) with the question number at the bottom of the Excel screen. Use a separate Excel worksheet page for each question that needs to be completed in Excel.
Sheet no.
Name the worksheets
Details
1
Cover page
Cover page information (group members' names and ID numbers) and snapshot of Gen AI chat prompts used in the assignment and link to the video presentation.
2
Data section
Data section and details of adjustments. e.g., Depreciation for the month was $2000. These need to be linked to the Worksheet in Q2.
3
Q1
Solution for Question 1 (Journals of Adjusting entries). These need to be linked to the Worksheet in Q2.
4
Q2
Solution for Question 2 (Formula embedded Worksheet).
5
Q3
Solution for Question 3 (Formula embedded Income statement, statement of changes in equity and classified balance sheet).
6
Q4
Solution for Question 4 (Formula embedded Closing entries).
7
Q5
Solution for Question 5 (Formula embedded two ratios).
8
Q6 Name of student no. 1
Formula embedded dashboard application visualising two ratios and the business's financial position or financial performance.
9
Q6 Name of student no. 2
Formula embedded dashboard application visualising two ratios and the business's financial position or financial performance.
10
Q6 Name of student no. 3
Formula embedded dashboard application visualising two ratios and the business's financial position or financial performance.
I advise that you do not wait until the last minute to make this recording, as it might take one or two trial runs. Do not upload the video directly to Moodle. Follow the instructions in the links below:
- Click here to learn how to produce a Zoom video recording.
- How to Submit a Video Assessment Using Echo360 in Moodle.
- Watch a video on how to submit a video recording.
Marking Rubrics
Category
Excellent
Good
Poor
1. Excel worksheets with appropriate formulas
Worksheet and F/S linked
Spreadsheets are professional in appearance. Formulae used quite extensively.
Spreadsheets are presented appropriately. Formulae used reasonably but not all linked.
No use of formula and 'messy' and disorganised spreadsheet.
Frame and Journalise five adjusting entries (Group mark)
2-3 marks
1-<2 marks
0-<1 mark
10-column worksheet (Group mark)
2-3 marks
1-<2 marks
0<1 mark
The income statement, the statement of changes in equity, and the classified balance sheet (Group mark)
2-3 marks
1-<2 marks
0-<1 mark
Closing entries and current Ratio (Group mark)
2-3 marks
1-<2 marks
0-<1 mark
Dashboard (Individual Mark)
An interactive dashboard with accurate, relevant and meaningful ratios and financial data. 3.5-5 marks
A functional dashboard with relevant ratios and financial data. 2-<3.5 marks
An incomplete or non-functional dashboard with missing or non-relevant ratios and financial data. 0-<2 mark
2. Zoom presentation (Individual Mark)
Explanation of Gen AI use
Clearly identifies Gen AI, logical selection reasoning, relevant prompts, and comprehensive ethical reflection. 3.5-5 marks
Identifies Gen AI, reasonable selection reasoning, some relevant prompts, and general ethical reflection. 2-<3.5 marks
Fails to identify Gen AI, lacks logical reasoning, unclear prompts, superficial ethical reflection. 0-<2 mark
Language, Communication ability and Audience engagement
Excellent use of professionalism, language skills and communication that fully engages with the audience. 3.5-5 marks
Language use is of acceptable, acceptable use of communication and effort to engage the audience. 2-<3.5 marks
Lack of logical flow in the presentation. Mistakes in language used, or no audience engagement. 0-<2 mark
Explanation of dashboard functionality and how it helps the owner in decision-making (it should be based on accounting concepts/principles/assumptions)
Provides a clear explanation of dashboard functionality, linking it to specific accounting concepts. Demonstrates how dashboards aid in decision-making with relevant examples and insights. 2-3 marks
Offers a reasonable explanation of dashboard functionality, mentioning some accounting concepts, principles, and assumptions. Shows how dashboards help in decision-making with reasonable insights. 1-<2 marks
Gives a vague or incomplete explanation of dashboard functionality, with little to no reference to accounting concepts, principles, or assumptions. Lacks clear discussion of how dashboards assist in decision-making. 0-<1 mark
Overall Score
Out of 30
Note: The rubrics are tentative guides only and are used as such.
Final Checklist
Please make sure you have addressed the following before submission.
- The completed cover page appears on the first page of Part A and contains all group members' names and IDs, as shown above.
- I have seen the marking criteria from the marking rubric (above).
- Profit/Loss calculation and both sides of the Balance sheet match using the "IF function".
- Formulas have been embedded in the solution and interlinked the solution with the datasheet.
- Video has been produced using screen sharing.
Excel Tips
- To see the formula view in Excel, press: control + Tilde button (ctrl + `). The Tilde button is the key below the Esc key and above the Tab key.
- Need to display negative numbers in brackets: How to display negative numbers (if any) in Excel in brackets.
- Type a few nos. with negative values and positive values.
- Highlight the values.
- Right click.
- Click on "Format cells."
- Choose Number and then custom.
- In the right box- choose 0.00.
- Under type: 0.00;(0.00).
- and then click OK.
- You will now be able to display a negative number in brackets.
- Basic formula for IF function is:
- IF(I33>H33,"Profit","Loss")
- IF(I33=H33,"Balance","Not Balance")
AI Declarations
Declaration regarding using ChatGPT or any other AI-assisted technologies in the assessment (see declaration example below). This declaration must be pasted on the cover page.
Declaration example:
This submission is an appropriate representation of group’s skills and abilities to meet the requirements of the task/s. I/we declare that I/we have used Grammarly to improve the language quality of the submission. I /we have used ChatGPT ( https://chat.openai.com/chat) to assist with …….(fill the details here).