Elizabeth Styles commenced a new interior styling business, called Coastal Styling. For the purposes of this activity, the business is not registered for GST. 

The business commenced on 1 June 2021, and Elizabeth operates the business as a sole trader. The business provides interior styling services to clients for a fee. The business did not initially sell styling items, such as wall art, linen and cushions - however, as the business has been growing very successfully in the first year of operation, Elizabeth has decided to expand the business from 1 July 2022. The business will now sell a range of styling items, in addition to providing interior styling services. The business will firstly stock two products lines: (1) cushions and (2) artworks. Elizabeth plans to expand the product range slowly over the coming months.

The adjusted trial balance at 1 July 2022, the start of the financial year, contained the following balances:

Coastal Styling
Adjusted Trial Balance
as at 1 July 2022
	Debit	Credit
	$	$
Bank	28,200
Prepaid insurance	3,300
Prepaid rent	5,000
Office equipment	7,300
Less: Accumulated depreciation		520
Elizabeth Styles, Capital - 1 July 2022		43,280
Total	43,800	43,800

 

The following transactions occurred during July 2022. 

Note: all payments were made from the business bank account, and all money received was deposited into the business bank account.

Date:	Details:
1 July	The business purchased and paid for 40 cushions at $12 each from its supplier, Coosh Ltd.
5 July	Elizabeth provided interior styling services to Mrs Jones, and received $600 cash, which was banked into the business bank account.
6 July	Elizabeth provided interior styling services to Mrs Smith for $400, and sold 8 cushions to her at $25 each. The sale was made on 7-day credit terms
9 July	Mrs Smith paid $600 into the business account, in relation to the money owing from the sale made to her on 6 July.
10 July	The business purchased 25 artworks at $160 each from its supplier, Abstract Designs Ltd, purchased on 14-day credit terms.
11 July	The business purchased (and paid for) stationery from Officeworks for $60 (all of the stationery was used during July).
12 July	The business sold 10 cushions at $25 each to Mrs Katz, sold on 7-day credit terms.
13 July	The business purchased 20 cushions at $14 each from its supplier, Coosh Ltd, purchased on 7-day credit terms.
14 July	The business paid Abstract Designs Ltd for the purchases made on 10 July 2022, by electronic funds transfer.
15 July	The business paid Letterbox Ads Ltd $850 for some advertising flyers to be delivered to homes that are nearby to the business.
18 July	The business sold 4 artworks at $260 each, to Mrs Firth. The sale was made on 30-day credit terms.
20 July	The business received the $500 from Mrs Firth, being part-payment for the artworks that she purchased on 18 July.
21 July	Abstract Designs Ltd offered Coastal Styling a special price of $150 for each artwork purchased (save $10 per artwork), with a minimum order of 100 artworks. The business purchased 100 artworks at $150 each from Abstract Designs Ltd, purchased on 14-day credit terms.
25 July	The business sold 6 artworks at $260 each, to Mr Jackson. The sale was made on 3-day credit terms.
27 July	The business provided interior styling services to Mrs Bliss, for $700, on 14-day credit terms.
28 July	The business received the $1,560 from Mr Jackson, being payment for the artworks that he purchased on 25 July.
28 July	The business purchased 10 cushions at $15 each from its supplier, Coosh Ltd, purchased on 7-day credit terms.
29 July	The business sold 20 cushions to Stanley Homes at $24 each, 2 artworks at $260 each, and provided interior styling services for $300. The sale was made on 30-day credit terms.
30 July	The business received an invoice from Telstra, for business telephone and internet costs for the month of July 2022. The amount payable is $200, and the due date for payment is 20 August 2022.
31 July	The business paid Coosh Ltd $280 that was owing in relation to the cushions that were purchased on 13 July.

 

Additional information:

• Elizabeth Styles would like to prepare monthly financial statements, and hence would like to prepare journal entries at the end of each month to recognise the monthly expense.
• In relation to the balance of $3,300 in the 'Prepaid insurance' account at 1 July 2022, this relates to prepaid insurance for the period 1 July 2022 - 30 September 2022.
• In relation to the balance of $5,000 in the 'Prepaid rent' account at 1 July 2022, this relates to prepaid rent for the period 1 July 2022 - 30 November 2022.
• The business will use the perpetual inventory system and average-cost method. For the purposes of the inventory records in this scenario, the cushions that the business purchases from Coosh Ltd are treated as one product line, and the artworks purchased from Abstract Designs Ltd are treated as another product line.
• Depreciation expense for the office equipment for July 2022 is $45.

Elizabeth Styles has come to you for assistance, as she is considering whether she should continue using Excel to prepare her accounting records. As you prepare to meet with Elizabeth, you need to complete the following tasks.

 

Questions Need to Answer

Qus1) Prepare Excel worksheet for each of the product lines for July 2022 (one worksheet for the cushions, and one for the artworks) using the perpetual inventory system and the average-cost method, to keep track of the number and dollar values of units purchased, units sold, units on hand, cost of goods sold and gross profit made. 

 

Qus2) Elizabeth has asked you to prepare the following, which would be needed for the business's manual accounting records in Excel:

• In an Excel worksheet, prepare the journal entries for the July 2022 transactions (including any adjusting entries needed as at 31 July 2022). Include dates, references (journal numbers) and narrations.
• Prepare T-accounts in an Excel worksheet and enter the opening balances from the adjusted trial balance as at 1 July 2022. Post all of the above journal entries to the T-accounts. Include the date and a journal reference (journal numbers) for each entry. Total all of the T-accounts to determine their balances at the end of July 2022. You are not required to prepare closing entries.
• Prepare the 'Adjusted Trial Balance' in an Excel worksheet as at 31 July 2022. Use formulas to generate all of the figures in the 'Adjusted Trial Balance' from the balances in the T-Accounts.

Qus3) Elizabeth has also asked you to set up and prepare the accounting records for the business in Xero, so that she can compare the manual system that she has been using in Excel with Xero. When you are setting up the Xero file, some points to note include:

• The business is not registered for GST.
• When adding the business bank account in Xero, use the account name "Bank Account" Bank Account No- 117 739 36". You can select from any of the available banks, and make up your own BSB and Account number.
• The business will sell two different inventory items initially (cushions and artworks).  The standard selling price of each item is: $25 for a cushion and $260 for an artwork.  Elizabeth Styles would like Xero to be able to track (or manage) inventory.
• You will need to enter the opening ledger account balances as at 1 July 2022 into Xero, from the Adjusted Trial Balance provided. To enter opening balances in Xero, go to 'Accounting', then 'Advanced', then 'Conversion Balances'.
• If required, new 'accounts' may be added in the 'Chart of Accounts', based on the needs of the business.

After setting up the Xero file, record all of the transactions for the month of July 2022 in the Xero file, including any adjusting entries that may be needed as at 31 July 2022. 

When recording transactions in Xero:

• use the 'invoicing' function when selling goods to customers;
• use the 'bills' function for transactions involving purchases and business expenses incurred;
• use the 'receive money' and 'spend money' functions when recording any owner contributions ('receive money') and owner withdrawals ('spend money') and/or recording any interest revenue/interest expense.
• use 'journal entries' to record any adjusting entries needed (for example, in relation to depreciation, unearned revenue, prepaid expenses).

Generate the following reports for July 2022.  

• General Ledger (detailed) (note: when you run the General Ledger report, you will have an option to 'Export Detailed General Ledger to Excel'. Export that file in Excel.
• Trial Balance
• Profit and Loss
• Balance Sheet
• Aged Payables (detailed)
• Aged Receivables (detailed)
• Inventory Item Summary

Qus4) Review other reports that are available in Xero. Select two additional reports (other than those in part (ii) above) that you think Elizabeth should review regularly as she continues to operate and grow her business. Include a screenshot of each of these two reports. Underneath each screenshot, explain how/why you think that this report will be helpful to Elizabeth. 

 

Qus5) During your meeting with Elizabeth Styles (discussing all of the above), Elizabeth expressed hesitancy to use Xero. She has asked you for your thoughts about the pros and cons of using a manual accounting system (such as in Excel) compared to a cloud-based system like Xero. Given your experience preparing accounting records and preparing reports manually in this task and your experience preparing accounting records and reports in Xero in this task, discuss the pros and cons of each.