lOMoARcPSD|41108613

Sitxfin 010 Assessment 2 -Assignment

engineering (Sunshine College of Management)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by Nand Kishore nandu ([email protected])
 lOMoARcPSD|41108613

Student Name
Student Number
Course and Code
Unit(s) of Competency and Code(s) SITXFIN010 Prepare and monitor budgets
Stream/Cluster
Trainer/Assessor

Assessment for this Unit of Competency/Cluster Details

Assessment 1 Short Answer Ques琀椀ons
Assessment 2 Assignment
Assessment 3 Project
Assessment conducted in this instance: Assessment 1 2 3

Reasonable Adjustment
1. Has reasonable adjustment been applied to this assessment?
No No further informa琀椀on required

Yes Complete 2.
2. Provide details for the requirements and provisions for adjustment of assessment:

Student to complete
My assessor has discussed the adjustments with me
I agree to the adjustments applied to this assessment
Signature Date

2nd Assessor to complete

I agree the adjustments applied to this assessment are reasonable
Name

Signature Date

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Assessment Guidelines

What will be assessed

Performance Evidence

The purpose of this assessment is to assess your ability to complete tasks outlined in elements and
performance criteria of this unit in the context of the job role, and:

 prepare a budget for a business that meets the speci昀椀c business’ needs
 demonstrate the following when preparing the above budget:
o consulta琀椀on on components
o analysis of factors that impact on the budget
o comple琀椀on of dra昀琀 and 昀椀nal versions of budget within designated 琀椀melines

Knowledge Evidence

 types of budgets:
o cash budgets
o cash 昀氀ow budgets
o departmental budgets
o event budgets
o project budgets
o purchasing budgets
o sales budgets
o wage budgets
o whole of organisa琀椀on budgets
 budget terminology
 speci昀椀c industry sector and organisa琀椀on:
o role and nature of budgets
o budget formats, budget performance and 昀椀nancial reports
o 昀椀nancial repor琀椀ng procedures and cycles
o features and func琀椀ons of accoun琀椀ng so昀琀ware programs used to prepare and monitor budgets
 internal and external factors that impact on budget development:
o growth or decline in economic condi琀椀ons
o human resource requirements
o new legisla琀椀on or regula琀椀on
o organisa琀椀onal and management restructures
o organisa琀椀onal objec琀椀ves
o scope of the project
o shi昀琀 in market trends
o signi昀椀cant price movement for certain commodi琀椀es or items
o supplier availability and cost
 budget prepara琀椀on and monitoring prac琀椀ces and techniques:
o sources and contents of data required for budget prepara琀椀on:

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

 compe琀椀tor research
 customer or supplier research
 declared commitments in areas of opera琀椀on
 昀椀nancial informa琀椀on from suppliers
 昀椀nancial proposals from key stakeholders
 income and expenditure for previous 琀椀me periods
 departmental, event or project budgets
 grant funding guidelines or limita琀椀ons
 management policies and procedures
 organisa琀椀onal budget prepara琀椀on guidelines
 performance informa琀椀on from previous periods
o techniques for making budget es琀椀mates
o common reasons for devia琀椀ons and budget devia琀椀on management.

Place/Loca琀椀on where assessment will be conducted; Timeframe for assessment tasks

Resource Requirements

Computer with Microso昀琀 excel

Calculator
Spread Sheets:
“Task 1-Greek Hotel Budget_Forecast”
“Task 2-Greek Budget_Restaurant_Bar”

Instruc琀椀ons for assessment including WHS requirements

The assignment for Assessment 2 consists of 3 Tasks

Task 1
Requires you to prepare a dra昀琀 budget following a mee琀椀ng with all heads of department where you have
昀椀nalised the details and requirements to be included for next year’s budget.

Task 2
Requires you to prepare a 昀椀nal budget, based on the changes made to your dra昀琀 budget following a
management mee琀椀ng.

Task 3
Requires you to respond to 3 昀椀nancial scenarios.

All responses must be typed and clearly referenced to each task. Your trainer will provide you with feedback
for each task. You will be provided with the opportunity to rec琀椀fy any shor琀昀alls based on the feedback from
submi琀琀ed work for this assessment.
Each completed spreadsheet must be a琀琀ached to the relevant task and clearly referenced.

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Statement of Authen琀椀city
I acknowledge that I understand the requirements to complete the assessment tasks
The assessment process including the provisions for re-submi琀�ng and academic appeals were
explained to me and I understand these processes
I understand the consequences of plagiarism and con昀椀rm that this is my own work and I have
acknowledged or referenced all sources of informa琀椀on I have used for the purpose of this assessment
Student Signature: Date: / /

This assessment: First A琀琀empt 2nd A琀琀empt Extension – Date: / /

Task 1 Sa琀椀sfactory Not Yet Sa琀椀sfactory
RESULT OF ASSESSMENT Task 2 Sa琀椀sfactory Not Yet Sa琀椀sfactory
Task 3 Sa琀椀sfactory Not Yet Sa琀椀sfactory
Feedback to Student:

Assessor(s) Signature(s): Date: / /

Student Signature Date: / /

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Task 1

Your Tasks:

You are required to complete a dra昀琀 budget based on informa琀椀on and factors that were determined during an
execu琀椀ve mee琀椀ng at Greek Hotel.

A. Access the excel spreadsheet named “Task 1-Greek Hotel Budget_Forecast”. The 昀椀rst tab on this
spreadsheet is labelled “Departments Small” and shows the exis琀椀ng budget 昀椀gures for the 2016
昀椀nancial year.

B. Use the template “Dra昀琀 Budget” on the second tab of the spreadsheet and perform the calcula琀椀ons
below using basic formulas.
Your forecast needs to include the Dollar Figures and the % values for these a昀昀ected by changes outlined
below.

The % values must be listed for each expense item shown in the Expenses Analysis for each department. All
expenses percentages are to be based on the total revenue for their related category.

You have met with the department heads of Greek Hotel and the following details have been discussed to
prepare your dra昀琀 budget for 2017:

1. Rooms Division:

a. Due to renova琀椀ons the rooms available have been reduced to 96%.

b. The forecasted occupancy rate has been adjusted to 80%.
c. The revenue per available room needs to be increased to $150.00
d. The COGS will increase to 15% of total room revenue
e. Sta昀昀 costs need to be increased to 20% of total room revenue to allow for increases in superannua琀椀on
and awards.
f. Other Expenses need to increase to 8% of total room revenue to cover electricity price rises.

2. Catering:

a. The food revenue will be increased by 15% due to a new marke琀椀ng campaign and specialty menus
b. The beverage revenue will increase by 8%.
c. Sta昀昀 costs need to be adjusted to 44% of the total forecasted food budget.
d. Other Expenses will need to be increased to 7%.

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

3. Banquet:

The Banquet Division will be directly a昀昀ected by the new marke琀椀ng campaign which has been directed at
day琀椀me seminars and corporate func琀椀ons. For this purpose the kitchen has received specialised equipment
including mul琀椀ple combi steamers, hold-o-mats and sous-vide equipment.

a. The new food revenue budget was set at $ 2,000,000.00 ($2 Million) and the beverage revenue was
increased by 75%.
b. The COGS will increase to 26%.
c. The sta昀昀 costs have been reduced to 19%.
d. Other Expenses will need to increase to 14%.

4. Room Service:

a. The room service revenue from food needs to be increased by 15%.

b. Due to a di昀昀erent system to clear 昀氀oors and organise delivery, the sta昀昀 costs will be reduced to 34%.

5. Mini Bar:

The mini bar budget remains unchanged and increased sta昀昀 costs are absorbed through di昀昀erent processes.

6. Bar Budget:

a. The food revenue budget has been increased by 25% with the implementa琀椀on of a Tapas Menu.
b. The Beverage Revenue budget has been increased 20% with the introduc琀椀on of a new cocktail bar and
happy hour specials.
c. Sta昀昀 costs will need to be adjusted to 36%.
d. Other Expenses need to be increased to 18%.

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Greek Hotel Budget Forecast

Total Revenue $ 11,213,138.73 $ Forecast

Room Budget Forecast $ Forecast % Expenses Analysis

Rooms Available 63875 100% COGS $ 987,555.00 14.21%
Rooms Occupied 51548 Sta昀昀 costs $ 1,201,980.00 17.30%
Total Occupancy % 80.70 80.70% Other Expenses $ 123,457.00 1.78%
Revenue Per Available Room $ 134.80 Total Expenses $ 2,312,992.00
Total Room Revenue $ 6,948,897.21

Total Catering Revenue $ 4,264,241.52

Food Budget (Restaurants) Expenses Analysis

Total Revenue $ 2,019,370.53 47.36% COGS $ 601,944.02 29.81%
Food Revenue $ 1,740,112.81 86.17% Sta昀昀 costs $ 1,122,433.24 55.58%
Beverage Revenue $ 279,257.72 13.83% Other Expenses $ 121,742.75 6.03%

Banquet Budget Expenses Analysis

Total Revenue $ 1,313,940.16 COGS $ 316,564.27 24.09%
Food Revenue $ 1,130,203.33 86.02% Sta昀昀 costs $ 476,538.08 36.27%
Beverage Revenue $ 183,736.83 13.98% Other Expenses $ 63,009.20 4.80%

Room Service Budget Expenses Analysis

Total Revenue $ 417,442.15 COGS $ 105,345.38 25.24%
Food Revenue $ 335,796.64 80.44% Sta昀昀 costs $ 228,397.17 54.71%
Beverage Revenue $ 34,668.69 8.31% Other Expenses $ 23,233.82 5.57%
Other Revenue $ 46,976.82 11.25%

Mini Bar Budget Expenses Analysis

Total Revenue $ 146,471.02 COGS $ 35,781.74 24.43%
Beverage Revenue / Minibar $ 146,471.02 100% Sta昀昀 costs $ 55,510.55 37.90%
Other Revenue $ - Other Expenses $ 18,897.56 12.90%

Bar Budget Expenses Analysis

Total Revenue $ 367,017.66 COGS $ 114,140.20 31.10%
Food Revenue $ 67,191.88 18.31% Sta昀昀 costs $ 151,731.13 41.34%
Beverage Revenue $ 299,825.78 81.69% Other Expenses $ 21,902.07 5.97%
Other Revenue $ -

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Greek Hotel Budget Forecast

Total Revenue 2016 $ 11,213,138.73 $ - Forecasted 2017

Room Budget Forecast $ Forecast % Expenses Analysis

Rooms Available 63875 100% COGS
Rooms Occupied 51548 Sta昀昀 costs
Total Occupancy % 80.70 80.70% Other Expenses
Revenue Per Available Room $ 134.80 Total Expenses
Total Room Revenue $ 6,948,897.21

Total Catering Revenue $ 4,264,241.52

Food Budget (Restaurants) Expenses Analysis

Total Revenue $ 2,019,370.53 COGS
Food Revenue $ 1,740,112.81 86.17% Sta昀昀 costs
Beverage Revenue $ 279,257.72 13.83% Other Expenses
Total Expenses

Banquet Budget Expenses Analysis

Total Revenue $ 1,313,940.16 COGS
Food Revenue $ 1,130,203.33 86.02% Sta昀昀 costs
Beverage Revenue $ 183,736.83 13.98% Other Expenses

Room Service Budget Expenses Analysis

Total Revenue $ 417,442.15 COGS
Food Revenue $ 335,796.64 80.44% Sta昀昀 costs
Beverage Revenue $ 34,668.69 8.31% Other Expenses
Other Revenue $ 46,976.82 11.25%

Mini Bar Budget Expenses Analysis

Total Revenue $ 146,471.02 COGS
Beverage Revenue / Minibar $ 146,471.02 100% Sta昀昀 costs
Other Revenue $ - Other Expenses

Bar Budget Expenses Analysis

Total Revenue $ 367,017.66 COGS
Food Revenue $ 67,191.88 18.31% Sta昀昀 costs
Beverage Revenue $ 299,825.78 81.69% Other Expenses

Task 2

Your Tasks:

You have provided the chief 昀椀nancial controller with the dra昀琀 budget for 2017. Following the recent execu琀椀ve
mee琀椀ng where the dra昀琀 budget was discussed, you are now required to establish the 昀椀nal budget re昀氀ec琀椀ng
the changes based on the latest actuals and variances as well as major road works which will a昀昀ect Greek
Restaurant and Bar during 2017.

A. Access the excel spread sheet named “Task 2-Budget Greek_Rest&Bar”. The 昀椀rst tab on this spreadsheet is
labelled “Greek Restaurant and Bar” and shows the Dra昀琀 budget 昀椀gures for the 2017 昀椀nancial year.

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

B. Use the template “Revised Budget” on the second tab of the spreadsheet and perform the calcula琀椀ons
below using basic formulas based on the following changes:

Month Customer numbers Average Spend (Food) Average Spend (Beverage)

January 1850 $ 45.00 $ 9.70

February 2000 $ 37.00 $ 9.70
March 700 $ 42.00 $ 9.70
April 1200 $ 48.00 $ 9.70
May 1200 $ 36.50 $ 9.70
June 600 $ 35.00 $ 9.70
July 950 $ 34.00 $ 9.70
August 800 $ 38.00 $ 9.70
September 900 $ 29.00 $ 9.70
October 650 $ 29.50 $ 9.70
November 980 $ 35.50 $ 9.70
December 2200 $ 48.00 $ 9.70

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

1.Calculate the an琀椀cipated Food revenue for each month and the yearly total.

Mon Custome Averag Averag Food

th r e e Revenue
numbers (Food)
Spend (Beverage
Spend
January 1850 $45.00 $83,250
$9 .70
February 2000 $37.00 )
$9.70 $74,000
March 700 $42.00 $9.70 $29,400
April 1200 $48.00 $9.70 $57,600
May 1200 $36.50 $9.70 $43,800
June 600 $35.00 $9.70 $21,000
July 950 $34.00 $9.70 $32,300
August 800 $38.00 $9.70 $30,400
September 900 $29.00 $9.70 $26,100
October 650 $29.50 $9.70 $19,175
November 980 $35.50 $9.70 $347,890
December 2200 $48.00 $9.70 $105,600
total $557,415

2.Calculate the an琀椀cipated Beverage revenue per month and the yearly total.
Month Customer numbers Average spends Beverage Revenue
January 1850 $9.70 $17,945
February 2000 $9.70 $19,400
March 700 $9.70 $6,790
April 1200 $9.70 $11,640
May 1200 $9.70 $11,640
June 600 $9.70 $5,820
July 950 $9.70 $9,215
August 800 $9.70 $7,760
September 900 $9.70 $8,730
October 650 $9.70 $6,305
November 980 $9.70 $9,506
December 2200 $9.70 $21,340
Total $136,091

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

3.Calculate the Total Revenue for each month and the yearly total.
Month Custome Averag Food Beverage Revenue Total
r e Revenu
numbers Spend e
(Food)
January 1850 $45.00 $83,250 $17,945 $101,195
February 2000 $37.00 $74,000 $19,400 $93,400
March 700 $42.00 $29,400 $6,790 $36,190
April 1200 $48.00 $57,600 $11,640 $69,240
May 1200 $36.50 $43,800 $11,640 $55,440
June 600 $35.00 $21,000 $5,820 $26,820
July 950 $34.00 $32,300 $9,215 $41,515
August 800 $38.00 $30,400 $7,760 $38,160
September 900 $29.00 $26,100 $8,730 $34,860
October 650 $29.50 $19,175 $6,305 $25,480
November 980 $35.50 $347,890 $9,506 $44,296
December 2200 $48.00 $105,600 $21,340 $126,940
Total $557,415 $136,091 $101,195.0
0
4. Calculate the overheads total for each month (at 90% of turnover for each for each month with 1000 or more
customers and at 96% for each month with less than 1000 customers) and the yearly total.
Month Total overheads
January $101,19 $91,075.50
5
February $93,400 $84,060.00
March $36,190 $34,742.40
April $69,240 $62,316.00
May $55,440 $49,896.00
June $26,820 $25,747.20
July $41,515 $39,854.40
August $38,160 $36,633.60
September $34,860 $33,436.80

October $25,480 $24,460.80

November $44,296 $42,524.16

December $126,94 $114,246.0

0 0
Total $101,19 $638,992.8
5.00 6

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

5.Calculate the pro昀椀t for each month and the yearly total.
Turnover Total Profit overheads
January $101,195 $10,119.50 $91,075.50
February $93,400 $9,340.00 $84,060.00
March $36,190 $1,447.60 $34,742.40
April $69,240 $6,924.00 $62,316.00
May $55,440 $5,544.00 $49,896.00
June $26,820 $1,072.80 $25,747.20
July $41,515 $1,660.60 $39,854.40
August $38,160 $1,525.40 $36,633.60
September $34,860 $1,383.20 $33,436.80
October $25,480 $1,019.20 $24,460.80
November $44,296 $1,771.84 $42,524.16
December $126,940 $12,694.0 $114,246.0
0 0
Total $101,195.0 $54,513.1 $638,992.8
0 4 6

6.Calculate the Cost of Goods Sold for food and beverages, given a combined percentage of 32%.
Turnover Total COGS - Food and Beverage
January $101,195 $32,382.40
February $93,400 $29,888.00
March $36,190 $11,580.80
April $69,240 $22,156.80
May $55,440 $17,740.80
June $26,820 $8,582.40
July $41,515 $13,284.80
August $38,160 $12,211.20
September $34,860 $11,145.60

October $25,480 $8,153.60

November $44,296 $14,174.72
December $126,940 $40,620.80
Total $101,195.0 $221,922.92
0

7.Calculate the sta昀昀 costs for each month at 31% for each month with 1000 or more customers and at 35% for
each month with less than 1000 customers.
Turnover Total Staff cost
January $101,195 $31,370.45

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

February $93,400 $28,954.00

March $36,190 $12,666.50
April $69,240 $21,464.40
May $55,440 $17,186.40
June $26,820 $9,387.00
July $41,515 $14,530.25
August $38,160 $13,356.00
September $34,860 $12,190.50

October $25,480 $8,918.00

November $44,296 $15,503.60
December $126,940 $39,351.40
Total $101,195.0 $224,879
0

9.Calculate the ‘Other overheads” for the opera琀椀on.

Turnover overheads COGS - Food and Staff cost Other
Beverage Overheads
January $91,075.50 $32,382.40 $31,370.4 $27,322.65
5
February $84,060.00 $29,888.00 $28,954.0 $25,218.00
0
March $34,742.40 $11,580.80 $12,666.5 $10,495.10
0
April $62,316.00 $22,156.80 $21,464.4 $18,694.80
0
May $49,896.00 $17,740.80 $17,186.4 $14,968.80
0
June $25,747.20 $8,582.40 $9,387.00 $7,777.80
July $39,854.40 $13,284.80 $14,530.2 $12,039.35
5
August $36,633.60 $12,211.20 $13,356.0 $11,066.40
0
September $33,436.80 $11,145.60 $12,190.5 $10,100.70
0
October $24,460.80 $8,153.60 $8,918.00 $7,389.20

November $42,524.16 $14,174.72 $15,503.6 $12,856.84

0
December $114,246.0 $40,620.80 $39,351.4 $34,273.80
0 0
Total $638,992.8 $221,922.92 $224,879 $192,192.4
6 4

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Print a copy of the revised budget.

1. Print the revised budget showing all formulae used.

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

Task 3

Your Tasks:

Read the following 3 scenarios and answer the ques琀椀ons a琀琀ached for each scenario.

Scenario 1:

The 昀椀nance team has created budget forecasts for Greek Hotel based on carefully researched factors for the last
3 years and these were always very accurate. The recent budget which included all departments of the hotel was
implemented 3 months ago and the forecasted 昀椀gures for Food Cost and COGS/Beverages in both the Restaurant
and the Bar Opera琀椀ons have blown out by nearly 4.5 percent.

What could be the reasons for this? List 5 examples of areas you would inves琀椀gate and explain why.

The recent budget of the company shows the cost of goods sold for various departments and
there has been noticed a blown out which is approximately 4.5%. the same has been experience
due to high fluctuations in the inflation rate and the corresponding change in cost associated
with various inputs and raw material required for producing the products of the Restaurant and
bar operations. Another reason for such blown out was inaccuracy in making assumptions about
the various cost items increase in percent. The previous year figures year figures of budgets for
the recent three years have been taken into consideration for determining the fluctuations in
rates which seems to be inconsistent with the consistent policy of the company operating in this
sector of industry.
Assumptions made
for cost Previous
year factors
Reliability of
figures Materiality
of figures Trends in
variances

Scenario 2:

Greek Hotel has successfully operated for 7 years. During this period, overall turnover has doubled, and during
the past 3 budget periods annual budgets have been increased by 15% each year which was exceeded each 琀椀me.
During the last 6 months however, management has no琀椀ced that the opposite trend seems to be occurring now.

List 5 external factors which could contribute to this and explain which methods you would use to determine
this.
Consumer
sentiment
Disposable

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

income
Strength of
dollar
Economic policies and government
legislations Competition
This method which will be suitable here will be capital budgeting which will involve calculation
of future results for the budgets prepared by the company. In the determination of these factors
various cost constituents will be taken care of and the external factors will be properly
recognized in order to determine the correct figures of the ratios.

Scenario 3:

You have successfully nego琀椀ated the dra昀琀 budget with each department head of the Hotel which has now been
approved by the director and implemented 6 weeks ago.
You have 昀椀nalised the 昀椀nancial data of the Hotel for the next management mee琀椀ng and no琀椀ced the following:

a.
Department Budget Actual Variance
Kitchen/Food Cost 28% 32% (-)$ 13467

b. The recently appointed F&B Manager has purchased 240 bo琀琀les of Hill of Blessings @ $90 each which
represents a saving of $30 per bo琀琀le. However this exceeds the par stock level by 220 bo琀琀les and has
created a cash昀氀ow problem, given the nega琀椀ve performance of the kitchen during this period as well.

1. Which reports would you need to prepare for these issues?

Variance report
Cash 昀氀ow report

2. Who is it essen琀椀al to involve when these ma琀琀ers need to be discussed?

F & B manager
Cash manager and inventory manager
3. Suggest op琀椀ons to address and rec琀椀fy these issues.
It can be observed that the budgeted expenditure cost associated with the food and kitchen cost has exceeded
the actual cost 4% which is 32%. The same can be rec琀椀昀椀ed with planning and crea琀椀ng appropriate strategies and
accurate plans for the next period and the same should be supported with control policies to be implemented by
various managers in their concerned department. The same will allow the company to obtain a favourable
variance in this aspect and the favourable results will be obtained by the company.

Downloaded by Nand Kishore nandu ([email protected])

 lOMoARcPSD|41108613

4. How could the cash昀氀ow issue be addressed?

It can be observed form the case study that the F&B manager has purchased 240 bo琀琀les of hill of
blessings which is more than the par stock to be maintained by the company. The same has created a
problem of cash 昀氀ow. For rec琀椀fying the d=same the management needs to analyse the appropriate
inventory level to be maintained and for the same purpose economic order quan琀椀ty will be recognized
by the company. Also, the management can adopt Just in Time (JIT) policy for improving the cash 昀氀ow
of the company as the stock can be purchased immediately.

Downloaded by Nand Kishore nandu ([email protected])