Renovation Business Assignment Help

Renovation Business Assignment Help . 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.

Renovation Business Assignment HelpThey 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.):                 $ 300

Advertising:                                                                  $ 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
  1. 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.
  2. 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.
  3. In which month is J & M Home Improvements Pty Ltd expected to break even?
  1. Change the rate of growth for revenue to 12% and state what is the month in which breakeven is achieved.
  1. Include a title for each graph (google this if you are unsure).
  1. Include a legend for each graph (google this if you are unsure).

Background

Gymkids commenced business in January.  They specialize 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
AprilMayJune
24,00022,00030,000
Payment History of Debtors
When Debtors Pay:How much is paid:
Within the month of sale50%
In the month after sale25%
In the second month after sale25%

Renovation Business Assignment Help

Forecast Credit Purchases
Month 1Month 2Month 3
20,00021,25022,000

 

Payment History to Creditors
When Gymkids Pay Creditors:How much is paid:
Within the month of purchase60%
In the month after purchase30%
In the second month after purchase10%

Question 1 – Using the table below, fill in the Debtors (Accounts Receivable) Budget.

 

Debtors  Budget

 

Actual

sales

January

Actual

sales

February

Actual

sales

March

Forecast

Sales

April

Forecast

Sales

May

Forecast

Sales

June

         Credit sales 

18 000

 

20 000

 

23 000

 

24 000

 

22 000

 

30 000

Cash from Jan sales      9 000       4 500       4 500
Cash from Feb sales
Cash from Mar sales
Cash from Apr sales
Cash from May sales
Cash from Jun sales
Total receipts from debtors

Question 2 – Using the table below, fill in the Creditors (Accounts Payable) Budget.

 

Creditors Budget

 

Actual

purchases

January

Actual

purchases

February

Actual

purchases

March

Forecast

purchases

April

Forecast

purchases

May

Forecast

purchases

June

Credit Purchases 

21 000

 

25 000

 

24 000

 

20 000

 

21 250

 

22 000

Cash from Jan purchases      12,600       6, 300       2 100
Cash from Feb purchases
Cash from Mar purchases
Cash from Apr purchases
Cash from May purchases
Cash from Jun purchases
Total payments to creditors

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 non-controllable wages costs, and note briefly the reason for your decision.

Controllable     Non-controllable                 Reason

Yes/no                 Yes/no

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
 TotalJanFebMarAprMayJunJulAugSepOctNovDec
Opening Balance5,0005,000           
              
Income             
Ticket Sales285,000            
Merchandising    6,000            
Government Grant  50,000            
 

 

 

             
Total Income341,000            
              
Expenditure             
Salaries120,000            
Casual Wages  10,000            
Office & Admin Expenses  36,000            
Travel & Transport  56,000            
Venue Hire  24,000            
              
Total Expenditure246,000            
              
Closing Balance100,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.

ForecastActualVariance $Variance %
Opening Balance5,0005,000
Income
Ticket Sales285,000277,000
Merchandising6,0007,000
Government Grant50,00050,000
Total Income341,000334,000
Expenditure
Salaries120,000124,000
Casual Wages10,00012000
Office & Admin Expenses36,00036000
Travel & Transport56,00052000
Venue Hire24,00024000
Total Expenditure246,000248,000
Closing Balance100,00091,000

Required

  1. Complete the columns for the variance in dollars and percentages for each row.
  2. Provide an explanation of why the actual cashflow was $9,000 less than what had been

Forecast.