The following text is designed to help you interpret some of the data in Table 1. Model 1 will use 65.5 square meters of cladding; Model 2 will use 80 square meters and Model 3 will use 120 square meters. The three different models will use different types of cladding. The cost per square meter for Model 1 is $22, for Model 2 is $80, and for Model 3 is $150. It is expected that Model 1 will require 20 hours of labour time to install the cladding. For Model 2, the installation time is expected to be 30 hours.
Insulation will be placed in the ceiling cavity and external walls. The insulation will be sheep wool batts, which are expected to cost $90 per 12 square meters. It is expected that it will take 8 hours to install insulation in Model 1 houses, 12 hours in Model 2, and 31 hours in Model 3 houses. You estimate Model 1 will require 165.5 square meters of insulation, Model 2 will require 230 square meters, and Model 3 will require 470 square meters.
The company will use Colourbond roofing material for all models. This is expected to cost $20 per square meter.
All three models will be built on concrete slab foundations. Concrete is expected to cost $90 per square meter. All models will use the same type of guttering, which is expected to cost $25 per lineal meter. Gyprock (drywall) for the internal walls and ceiling is expected to cost $15 for boards that cover 2.88 square meters (1.2 meters by 2.4 meters). Paint for internal walls and the ceiling is expected to cost $170 per 10 litre can and this is expected to provide 160 square meters of coverage (16 square meters per litre). Lee wants to buy paint in 10-litre tins, as the cost per litre is cheaper, compared to buying paint in smaller cans. For example, it is expected to cost $80 to buy a 4 litre can of the same paint, which works out at $20 per litre.
Please note, the internal walls and ceilings will take 2 coats. The measures above are for two coats of paint. The ceilings will be painted white. The owners will be able to select other colours for the internal walls.
Kelly and Lee are comfortable their workers can complete many of the tasks, such as erecting the metal frames, installing guttering, cladding, roofing, insulation, internal walls made of Gyprock (also known as drywall), finishing and painting internal walls and ceilings. However, Lee and Kelly have decided they will buy a number of components and have them installed by specialists. In Table 1, these are all shown in brackets as (purchased and installed). This includes wardrobes, doors, windows, kitchens, plumbing and electrical work.
Kelly is concerned that they do not accidentally underpay their employees, so you need to check they are being paid at least the minimum wage. To make it easier, Kelly advises you to follow the link and guidance below.
https://calculate.fairwork.gov.au/findyouraward
Click on the button ‘I’m an employee looking for pay rates. Then click on next. Then click on next again, when you see the radio button “No, help me find my award” has been selected.
In the dialogue box, type building and construction tradesperson. Press the radio button that shows the same term under the text you just entered. Click Next. Select no for the following.
Are you a trainee? Are you eligible for a supported wage? Are you an apprentice? Click Next.
Select Level 5 (CW/ECW 5). Click next.
Select weekly hire – full time. Click next.
Select General building and construction or metal and engineering construction. Click next.
Select roof tiler, roof fixer, slate-ridger or tradesperson in the metals and engineering construction sector. Click next.
Staff do not operate mobile cranes. Staff will work in the residential building and construction industry. Staff are not required to work underground. Staff are not in charge of plant. None of them are leading hands.
This will tell you the hourly pay rate. You can assume no penalty rates will apply, as no work will be done on weekends, public holidays, Saturdays after Easter Friday, etc.
Use the hourly rate in your calculation of labour costs for the different jobs in building each house type. Each worker will be paid for 38 hours per week.
Kelly remembered that you should also include superannuation in the cost of labour, as the company has to put aside some money, based on the workers’ ordinary time earnings. You can assume the ordinary time earnings is based on 38 hours per week. You can assume all workers qualify for superannuation contributions by the company. Research the appropriate rate that the company has to use.
Table 2 below presents data relating to other costs. Please note that this is incomplete. Certain costs have not been included here. However, you have been provided with specific information which will let you identify those costs and calculate the relevant amounts.
Table 2
Office staff (per year) $420,000
ICT costs - cloud (per year) $3,500
Software licenses (per year) $1,500
Compliance costs (tax, superannuation, local government approvals) $7,800
Insurances $15,000
Architect fees for initial drawings (one off) $8,000
Utilities $7,000
Required
Prepare responses to the issues below. Some responses will require you to use Microsoft Excel, others will be completed using Microsoft Word. These are indicated below.
Please note, heavy penalties will apply for inappropriate spreadsheet design. You will need to use named cells, linking, and formulas. Minimize the use of hard coded data in your models.
1) (Excel) name a sheet Data and place relevant inputs there. Name these inputs. You will use these named cells in formulas and models below. (5 marks)
2) Name a sheet Budget 2018. Build a model which will show the contribution margin of each model of kit home, assuming the frames are fabricated by FB at their factory. The column headings will be Model 1, Model 2, Model 3. (30 marks)
a. On the same sheet and under the contribution margin, list the annual budgeted fixed costs of the business. (5 marks)
b. Under the fixed costs, calculate the budgeted profit or loss the entity expects to make for 2018. You can assume that the company expects to sell 7 units of Model 1, 10 units of Model 2, and 3 units of Model 3 in 2019. (5 marks)
3) (Word document) identify limitations and weaknesses of the model you have created. (15 marks)
4) (Word document) advise Kelly whether Lee is correct to focus on the cost per litre of paint, or whether the company should buy paint in smaller tins. State any assumptions you make. (5 marks)