BDM 2023 T2

 

You just commenced work at a small consulting firm. You have been asked by your manager to develop an 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 ended 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 ambience 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 needed 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 would have done 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 insurances 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 to complete, 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 which shows how many nights each room was booked each week for the calendar year ended 31/12/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. You 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.

Another tab can be developed called Data and this can be placed before the tab for Budgeted Contribution. Please enter relevant variables in the data tab and name them. 

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 a 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. Only this can be done for the 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 to show monthly tota 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 needed.

Requirement

1. Complete the instructions relating to the Budgeted Contribution tab.

                             

1. Complete the cash flow budget as instructed by the manager.

                            

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

 

1. a) another tab is needed to be called revised cash budget and a better-designed model is preferred to be developed. Feel free to adopt a format that suits this data. Ensure your model can easily present relevant information aggregated by month and by weeks.                                                  
   
   
   
    

b) Briefly explain why your model is better than the one your manager requested. 

 

1. Open another tab and develop a budgeted income statement for the 12 months from March 2024. Ensure it is dynamically linked to the revised cash budget. Your manager indicated that she wants you to use Hlookup (yes, Hlookup, not vlookup).
2. preferbly, a if a brief for the manageris . What issues, if any should she raise with the clients? Identify and discuss areas of concern.

 

Attachment - Room occupancy - 2022 - (the gaps in the table show where the previous owners took holidays).

			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