Given the case study below, please write briefly about the following questions,

1. A brief for your manager is needed. What issues, if any, should she raise with the clients? Identify and discuss areas of concern.

2. Briefly explain why your suggested model is better than the one your manager requested.

3. Identify limitations and weaknesses of the cash budget the manager asked you to develop.

• Using the 2022 house occupancy rate is not appropriate for 2024'sweaknesses.
  • 1. Covid recovery +ve COVID will end and 2024 might have more people travel
  • 2. Renovation and design might be +ve - after renewal, the house might attract more clients
  • High, shoulder and low season might slightly be different in term of week in 2024 compared to 2022.
• Manager asked to use 2022 Jan and Feb as reference for 2024 Jan and Feb in budget income statement for 12 months from March 2024
  • No assumption for Jan and Feb in 2025
• Manager did not consider tax rate and other possible expense or unexpected changes in budget,

Case Study:

You just began work at a small consulting firm. You have been asked by your manager to develop dean Excel workbook, so she can provide advice to Kristy and Mel, a pair of clients. Unless instructed otherwise, the workbook is to be designed so that changes in variables are automatically incorporated in the outputs, such as a budget, or income statement.

Mel and Kristy are a pair of former public servants in their late 50s who are really keen to buy a particular bed and breakfast business located on the South Coast of New South Wales. They took a holiday at that bed and breakfast business and fell in love with it. They were particularly smitten by the beautiful views of the ocean through the eucalypt forest surrounding the property and were delighted to find the business is for sale. The place is a bit run down. Mel and Kristy expect it will take about 10 days to repaint the rooms, replace floor coverings update the kitchen equipment etc. Mel and Kristy will do the work themselves. They estimate the cost of these renovations will come to $22,000. These expenditures will be classified as an asset, because it will be part of the cost to get the asset to the condition intended by the new owners. This figure will be added to the cost of the building and will be depreciated over the same time as the building (see below). Their cash balance, after the purchase is expected to be $72,000. Please note, this is before the cost of renovations is taken into account.

The business is scheduled to open for customers in the week ending 1/3/2024. The business will cater to couples who wish to take a short break. No children are allowed. Each room caters for one couple per booking.

Due to seasonal changes in demand, the business uses a variable pricing structure as shown in the table below. The first table shows the rates the previous owners were charging.

Revenue per room per night
High season	$450
Shoulder season	$315
Low season	$221

Your clients think the previous owners were under-charging, due to the condition of the place before the refurbishment. Kristy and Mel intend to charge the following rates. They are both very gregarious and think people will be comfortable paying the new rates, due to their conversation skills and the quality of the experience. For example, Mel wants to get some beehives, in order to sell locally produced honey.

Revenue per room per night	Room 1	Room 2
High season	$600	$600
shoulder season	$420	$420
low season	$294	$294

Your manager asked Kristy how they worked out their pricing structure. She replied that they compared their business with competitors in the local market and in other markets offering similar levels of service and ambiance in high season. She said she used the same percentage differential between high and shoulder as used by the previous owners. She did the same with the pricing for the low season.

High season runs for all weeks that end in December and January. Shoulder runs for all weeks that end in February to April, and September to November. Low season is the remaining weeks.

In the low and shoulder seasons, the business is open for three nights per week, Friday to Sunday inclusive. Guests can stay for 3 nights if they wish, but the minimum stay is for 2 consecutive nights. In the high season, the business accepts guests 7 nights a week, with a minimum stay of 2 consecutive nights. The former owners indicated that on average, two new groups occupied each room per week during the high-demand season.

The previous owners closed the business for 3 to 4 weeks every winter, so they could take a holiday. Mel and Kristy will follow this practice.

Mel and Kristy provided the following information.

When guests arrive, they will receive a welcome pack, consisting of a basket of goodies, costing $30 each and a small bottle of champagne costing $25.

The only meals prepared at the B&B will be breakfast. The cost of the ingredients is expected to be $15 per day per room.

Soap and shampoos will be provided, expected to cost $5 per visit per room.

The clients indicated they will not keep the cleaner used by the previous owners, as Kristy would do by herself. Mel will look after the grounds.

Mel will also carry out basic maintenance of the building, excluding tasks relating to electrical, plumbing and gas issues. The previous owners indicated that they spent about $5,000 per year on maintenance relating to these three issues, in total.

Rates and insurance are expected to total $21,000 per year. These will be paid on the 6^th of March 2024 by Mel and Kristy. Utilities are expected to cost $4,000 for the year. These are paid at the start of each quarter, commencing in January. The former owners paid the bill for the first quarter.

The clients paid $900,000 for the building and land. You have been advised that the fair value of the building was $400,000 at the date of purchase. The building is depreciated over 40 years using a straight-line basis and a residual value of $30,000.

Mel and Kristy indicated they want to withdraw $1,000 cash per month from the business and Kristy mentioned that they expect to earn a profit of $90,000 per year, as they are investing a lot of money in this business.

In the discussions, Mel indicated that Kristy's brother is a builder. He suggested the renovations are quite complex and are likely to take at least a month for the completion and are likely to cost about $60,000 if they are lucky. Kristy and Mel thanked him for his input and agreed to disagree. They are convinced their estimates are reasonably accurate.

The previous owners provided a schedule that shows how many nights each room was booked each week for the calendar year ending December 31, 2022 (see the attachment below). Add this to the workbook in a separate tab labelled Data - Room occupancy - budget). Your clients want to use this as the basis for the expected occupancy levels for 2024.

Copy and paste this data to another tab - called Budgeted Contribution. Your manager explained that due to the nature of the business, there is no reason to develop a formal sales budget, purchases budget, etc. All transactions (revenues and expenses) use credit cards. Kristy and Mel indicated that they pay off their credit cards at the end of each month.

Your manager wants you to add relevant columns required to calculate the budgeted contribution margin per week for 2024, using the occupancy figures for 2022 and the relevant figures for 2024. Even though Mel and Kristy are not expected to take over until early March 2024, you do not need to rearrange the data in this worksheet. That is, calculate the contribution per week from the first week in January until the last week in December.

Use formulas and relevant techniques to populate the columns and calculate the relevant figures which will be used to calculate the budgeted contribution per week. Please name the relevant variables. Your manager wants to give this workbook to Kristy and Mel, so they can play around with key variables and observe the impact on total contribution margin.

In the same workbook, open a new worksheet (a new tab) and label it Cash Flow Budget. Based on instructions below, you will use this tab to develop cash flow budget for 12 months, commencing from March 2024. (This time she wants you to change the data. The first month will be March. Use the figures for Jan and Feb 2022 for Jan and Feb 2024. This is needed only for cash flow budget. Don't disturb the figures for the contribution margin worksheet.)

To make it easier to read and navigate the cash flow budget, your manager wants to aggregate data from the Budgeted Contribution sheet so that monthly totals are shown, rather than weekly totals. She suggests you insert a column (in the Budgeted Contribution sheet) which will show the month a given week falls in. She suggested you use a text function to convert the date for each week into a month. When you gave her a blank look, she suggested Googling it. She then wants you to use the subtotal function to aggregate the relevant figures for all weeks belonging to a given month.

Run the subtotal function. Copy and paste the visible cells (only the visible cells) to the Cash Flow Budget sheet. After this, copy and transpose, so that the months are the column headings. A cash flow budget based on the contribution margin and other relevant material is required.

			Nights booked	Nights booked.
Season	Week number	Week ending	Room 1	Room 2
H	1	3/01/2022	7	7
H	2	10/01/2022	7	7
H	3	17/01/2022	7	7
H	4	24/01/2022	6	5
S	5	31/01/2022	2	2
S	6	7/02/2022	2	3
S	7	14/02/2022	2	3
S	8	21/02/2022	3	2
S	9	28/02/2022	3	2
S	10	7/03/2022	2	3
S	11	14/03/2022	2	3
S	12	21/03/2022	3	2
S	13	28/03/2022	2	3
S	14	4/04/2022	2	2
S	15	11/04/2022	3	3
S	16	18/04/2022	3	2
S	17	25/04/2022	2	3
L	18	2/05/2022	2	0
L	19	9/05/2022	0	3
L	20	16/05/2022	2	2
L	21	23/05/2022	2	0
L	22	30/05/2022	0	2
L	23	6/06/2022	0	2
L	24	13/06/2022	2	0
L	25	20/06/2022	0	0
L	26	27/06/2022
L	27	4/07/2022
L	28	11/07/2022
L	29	18/07/2022
L	30	25/07/2022	0	0
L	31	1/08/2022	2	0
L	32	8/08/2022	0	0
L	33	15/08/2022	0	0
L	34	22/08/2022	0	2
S	35	29/08/2022	0	2
S	36	5/09/2022	2	2
S	37	12/09/2022	0	2
S	38	19/09/2022	0	2
S	39	26/09/2022	0	3
S	40	3/10/2022	2	3
S	41	10/10/2022	0	3
S	42	17/10/2022	2	2
S	43	24/10/2022	3	3
S	44	31/10/2022	3	2
S	45	7/11/2022	2	2
S	46	14/11/2022	3	2
S	47	21/11/2022	3	2
H	48	28/11/2022	4	5
H	49	5/12/2022	5	5
H	50	12/12/2022	5	6
H	51	19/12/2022	7	7
H	52	26/12/2022	7	7