Accounting Business Assignment Help Australia Background Jason and Marie are new clients who have recently set up their own home renovation business which they have called J& M Home Improvements Pty Ltd. They want you to provide ongoing accounting/bookkeeping services to them but they are concerned that they have not yet prepared a budget for their proposed business. In order to assist them you ask them for forecast of revenue and expenses for the first year of operation. The data you are provided with is as follows: Monthly data Rent for workshop:
$ 3,000
Contractors (carpenters, electricians, plumbers, etc.):
$18,000
Motor Vehicle - petrol-parking registration etc.): Advertising:
$ 300 $ 1,500
Postage, printing & stationery:
$
200
Insurance:
$
275
Utilities:
$
50
Telephone:
$
175
They anticipate that revenue will be $15,000 in their first month of operations and this will grow at the rate of 10% each month. Please round all numbers to whole dollars. Required 1. For this simulation exercise, you are required to prepare a budget using Excel. Include the following: 
the name of the business

month name for each of 12 columns

forecast revenue each month
forecast expenses each month
Profit or loss each month (ignore GST and tax for this exercise)
Accumulated profit or loss each month
2. Create a pie graph for the year to show each type of expense as a proportion of the total expenses. Insert this graph on the same page as the data. 3. Prepare a column graph which includes both revenue each month and total expenses each month. (Tip: you may find it helpful to have the sales revenue row listed first, by month, followed by the total expenses, by month, on the row beneath). Insert this graph of total revenue and total expense on the same page as the data. 4. In which month is J & M Home Improvements Pty Ltd expected to break even? 5. Change the rate of growth for revenue to 12% and state what is the month in which breakeven is achieved. 6. Include a title for each graph (google this if you are unsure). 7. Include a legend for each graph (google this if you are unsure). Background Gymkids commenced business in January. They specialise in the supply of sporting equipment for primary schools. The sales forecast, expected debtors payment schedule, forecast purchases and expected credit payment schedule details are as follows: Forecast Credit Sales April 24,000
May 22,000
Payment History of Debtors When Debtors Pay: Within the month of sale In the month after sale In the second month after sale
Forecast Credit Purchases Month 1
June 30,000
How much is paid: 50% 25% 25%
Month 2
Month 3
20,000
21,250
22,000
Payment History to Creditors When Gymkids Pay Creditors: Within the month of purchase In the month after purchase In the second month after purchase
How much is paid: 60% 30% 10%
Question 1 - Using the table below, fill in the Debtors (Accounts Receivable) Budget.
Debtors Budget
Actual
Actual
Actual
Forecast
Forecast
Forecast
sales
sales
sales
Sales
Sales
Sales
January
February
March
April
May
June
18 000
20 000
23 000
24 000
22 000
30 000
Credit sales Cash from Jan sales Cash from Feb sales Cash from Mar sales Cash from Apr sales Cash from May sales Cash from Jun sales Total receipts from debtors
9 000
4 500
4 500
Question 2 - Using the table below, fill in the Creditors (Accounts Payable) Budget.
Creditors Budget
Credit Purchases
Actual
Actual
Actual
Forecast
Forecast
Forecast
purchases
purchases
purchases
purchases
purchases
purchases
January
February
March
April
May
June
21 250 21 000 Cash from Jan purchases Cash from Feb purchases Cash from Mar purchases Cash from Apr purchases Cash from May purchases Cash from Jun purchases Total payments to creditors
25 000
12,600
24 000
6, 300
20 000
22 000
2 100
Question 3 Shane Robinson is the supervisor of a university canteen with a monthly wages budget of $4,000. She receives a budget control report that tells her that the expenditure in her canteen for January, February and March has been $4,500 for each month. Here are the reasons for overspending.
January: extra staff employed to cover sickness.
February: staff overtime to meet rearranged schedules during the university examination period
March: implementation of a nationally agreed bonus scheme, which was not built into the budget.
Required: Decide whether the adverse variance in each month has been caused by controllable or noncontrollable wages costs, and note briefly the reason for your decision. Controllable Yes/no
Non-controllable Yes/no
Reason
January __________ ____________ _________________________________ _________________________________ February
__________
____________
_________________________________
March
__________
____________
_________________________________
Question 4 As a manager of an event management company, you are required to complete a cash flow forecast using the template provided. Your cash flow forecast will predict the bank balance at the end of each month for every month of the year. The information you have at your disposal to complete this task is as follows: 1. The event management company has on its books four (4) major events to run which will take place in March, May, August, and October. 2. The total of $285,000 of ticket sales will be earned in the months that the events are held. The March event will earn $65,000, and the other events in May, August and October will earn $70,000, 85,000 and $65,000 respectively. 3. Expenditure on casual salaries, travel and transport, and venue hire will occur only in the months in which the four events take place. These expenditures will be the same for each event. 4. Salaries and Office and administration expenses will occur evenly every month. 5. The company's opening bank balance on 1 January is $5,000 6. The company will receive a government grant of $50,000 in July. 7. The company will make a small income from Merchandising during the months in which the four events are staged. The event in March will earn $1,300 merchandising income, and the other events in May, August and October will earn $1,600, $1,800 and $1,300 respectively. Required Complete the Cashflow Forecast Template below to determine the closing balance of cash each month. Cashflow Forecast Template Total Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Opening Balance 5,000 5,000
Income Ticket Sales 285,000 Merchandising 6,000 Government Grant 50,000
Total Income Expenditure Salaries Casual Wages Office & Admin Expenses Travel & Transport Venue Hire
341,000
120,000 10,000 36,000 56,000 24,000
Total Expenditure
246,000
Closing Balance
100,000
Question 5 At the end of the year, the following actual figures for cashflow together with the forecast figures have been provided to management.
Opening Balance
Forecast 5,000
Actual 5,000
Income Ticket Sales Merchandising Government Grant
285,000 6,000 50,000
277,000 7,000 50,000
Total Income
341,000
334,000
Expenditure
Variance $ Variance %
Salaries Casual Wages Office & Admin Expenses Travel & Transport Venue Hire
120,000 10,000
124,000 12000
36,000
36000
56,000 24,000
52000 24000
Total Expenditure
246,000
248,000
Closing Balance
100,000
91,000
Required 1. Complete the columns for the variance in dollars and percentages for each row.Provide an explanation of why the actual cashflow was $9,000 less than what had been Forecast.