TASK 3 - Case Studies: Prepare financial statements and reports for Simpsons Pty Ltd Purpose You will demonstrate your skills and knowledge by...

TASK 3 - Case Studies: Prepare financial statements

and reports for Simpsons Pty Ltd

Purpose You will demonstrate your skills and knowledge by completing scenariobased

case studies.

Case Study

context and

Scenario

Simpsons Pty Ltd

Simpsons Pty Ltd is a small take away restaurant located in the Sydney

CBD. The restaurant is owned by Mr & Mrs John Spice. The restaurant

started its activities in January 2015. In December 2015, the following

information was collected:

Sales and COGS activities for 2015

The restaurant sold 54,600 take away meals, and the price per meal was

$10.50. The food cost per meal sold was $4.60. Also 20,000 units of soft

drinks were sold during the year. Soft drinks are sold for $2.50 and the

cost price was $0.80.

Budget forecasting for 2016

The owners estimated that for the next year the price per take away

meal could be increased to $13.00. They have also predicted that sales

(in units) would increase by 10% in 2016. The owners have entered into

a monthly contract with the soft drinks supplier. According to the

contract the restaurant must purchase a minimum of 2,000 units of soft

drinks per month and the cost price will be reduced to $0.50 per unit.

The restaurant owners decided to reduce the selling price of soft drinks

to $1.50 in order to increase sales (in units) by 50% and are expecting to

meet the required selling demand. The owners have anticipated the

following expenses for 2016:

Anticipated yearly expenses for 2016

COGS (Food + Beverage)

$295,267 =

Food ($283,267.00) / Beverage

($12,000.00)

Rent $ 40,000 Utilities $10,000

Wages $130,000 Miscellaneous $ 6,000

Instructions You will be completing two (2) tasks for this case study assessment.

Task 1: Complete all three (3) activities in the section that follows.

To complete the activities, you will be preparing spreadsheets to

monitor and review the 'Budgets' and 'Profit & Loss statements' using

Microsoft Excel.

You are encouraged to display your answers by clearly calculating a

simple formula in each cell. You will upload your spreadsheet workbook

on Moodle as part of your evidence.

Your assessor will give you clear instructions on how to do this.

ILSC Business College: BSBFIM501 Assessment Version 4.0518

Page 16 of 19

Task 2: To complete task 2, you will be writing a 'Financial Report' to

identify options for improved 'budget performance' and 'contingency

planning' using word processing software. Refer to the task in the

section that follows.

You will write your report using appropriate headings with your name

and ID in the header section of your document and upload your report

on Moodle with your assessment.

Assessment

conditions

Assessment will be conducted in a simulated environment where

evidence gathered demonstrates consistent performance of typical

activities to undertake financial management within a work team and

include access to:

§ Office equipment and resources

§ relevant legislation, regulations, standards and codes

§ interaction with others.

Reasonable

adjustment

If you are unable to undertake the project assessment as designed,

please speak to your assessor.

ILSC Business College: BSBFIM501 Assessment Version 4.0518

Page 17 of 19

Case Studies - Tasks and Instructions

TASK 1: Preparing spreadsheets to monitor and review Budgets and

Profit & Loss Statements

PLEASE NOTE: spreadsheets must be prepared using Microsoft Excel

Activity 1

— Based on the above Sales and COGS activities for 2015:

Prepare a spreadsheet that shows in 12 monthly columns the average monthly sales for food

and drinks and the average monthly COGS for meals and drinks.

In addition, include end columns for the annual totals and another with the average quarterly

totals.

Using the prepared template from Activity 1; continue with the following:

Activity 2

— Based on the above Budget forecasting for 2016:

Prepare a 2nd spreadsheet similar to above (i.e. 12 monthly columns) for the yearly Profit

and Loss Budget. Don't forget to also include end columns for the annual totals and the

average quarterly totals.

Activity 3

— Based on the following assumptions prepare a 3rd spreadsheet with the actual

average monthly trading activities for the 1st quarter 2016.

Assume that during the 1st quarter of 2016:

§ Food revenue was 20% higher than the budget forecasted

§ Food Cost was 30% higher than the budget forecasted

§ Beverage sales was 15,000 units

§ Utilities Expense was $8,000

§ Wages expense $25,000

§ Rent expense was $10,000

You are also required to monitor the trading activities for Simpsons Pty Ltd for the first quarter

of 2016, and identify and record in an additional column all dollar (AUD$) variances and as a

percentage % figure from the forecasted budget.

ILSC Business College: BSBFIM501 Assessment Version 4.0518

Page 18 of 19

TASK 2: Develop a Financial Report

PLEASE NOTE: Up load your report on Moodle with this assessment

Activity 1

Write a Financial Report (in the appropriate format) to the restaurant owners; include the

following:

In Activity 3 you have identified variance between the budget and actual results for the first

quarter of 2016. Explain different contingency planning strategies and adjustments that you can

make to your budgets for the rest of 2016. Be sure to address any possible causes for the variance.

Ensure you include each of the following main areas in your report:

§ Meals

§ Drinks

§ COGS

§ Rent

§ Utilities

§ Wages

§ Miscellaneous

Activity 2

You must include your recommendations and a conclusion in this section of the report you need

to discuss:

§ How you will implement and monitor your changes to the business to control costs

§ How you plan on making new contingency plans for further budget variances

§ How you can collect and use information from work teams to use with your budgets

§ Comment on how effective the financial information is that you collected.

§ Explain how you will negotiate and communicate budget information with team

members.

§ Explain how you can support staff to make budget changes

ILSC Business College: BSBFIM501 Assessment Version 4.0518

Page 19 of 19

Report Writing Guidelines

Assessment Task 2:

The report should have an introduction like the below example:

Please find below the performance report of your business for the year 2016. This report is

mainly for the purpose of providing a financial analysis of your business' progress during the

first quarter of 2016 and towards the end of the year. Additionally, this report will provide

budget projections and propose viable solutions for the rest of the year based on the outcome

of expenses in 2016.

Moving forward, you will be receiving quarterly reports of this kind which will identify the costs

of operating your company, your budget and variations between your actual performance and

budgeted performance. The highlighted variances will make it easier for you to quickly identify

aspects of your operations that require immediate attention, as indicated in the

recommendations below.

You must note from the report that your budget from actual income is significantly higher due

to increased costs in meals and drinks. Remedial actions are stipulated in the recommendations

section of this report so you can find ways to meet targets by end of the year.

Main Points of Interest that you should include within the report:

§ Financial analysis of income

§ Meals

§ Drinks

§ Financial analysis of expenses

§ COGS

§ Rent

§ Utilities

§ Wages

§ Miscellaneous

Recommendations for Report Assessment 2 Task 2:

This section refers to the possible contingency plans required based on the variances between

budget and actual results for the first quarter of 2016.