## Basic Modelling Case

Wall Labour Costs: a mini case

By Duncan Williamson

During the construction of a perimeter wall around a private house, five men were employed. The daily rates of pay were

• Jack = Team leader B400
• John = Second in command B330
• Joe, Jeb and Jim = Team members B300

The house owner has agreed these rates of pay and has agreed to pay the men at the end of each week and at the end of the job, if it comes before the end of a week.

Task: there is no file attached to this page BUT if you want it, send me an email and I will send it to you … info@excelmaster.co.uk

• Construct a table in Excel that serves the following purposes
• Provides a record of attendance of everyone who works on the job
• Provides the times they worked on the job: whole days and half days
• Shows the total money earned
• per day
• cumulatively
• Shows the payments made by the house owner
• Has a final column that shows the cumulative amount owing at any time, net of payments made.
• In this exercise you should use the =SUMPRODUCT() function in addition to any other functions and formulas you feel appropriate.

Your final table might look like the following:

 Day No B300 B300 B300 B330 B400 Total CumTotal Paid Owing 1 2 3

However, if you prefer a different layout for your table, that is fine providing it following the nature of the task set here.

Specimen Data: who worked when …

• Day 1 Jack, Joe, Jeb and Jim all worked a full day
• Day 2 Joe and Jeb worked a full day
• Day 3 John, Joe and Jeb worked a full day
• Day 4 Jack, John, Joe, Jeb and Jim worked a full day
• Day 5 Jack, John, Joe, Jeb and Jim worked a full day
• Day 6 Joe and Jeb worked a half day
• Day 7 Jack, John, Joe and Jeb worked a full day

At the end of day 7 the house owner paid the B5,000 the team leader requested, to be shared among the men.