In this problem, you will be modelling a honey bee evolution. However the main goal is for you to try some introductory programming and plotting using Excel.

A honey bee colony is a population of related and closely interacting individuals that form a highly complex society. The population dynamics of this group is complicated, because the fates of individuals within it are not independent, and an individual's lifespan is strongly influenced by their role in the colony. To aid exploration of honey bee population dynamics here, a simple mathematical representation is described of how the social regulation of worker division of labour can influence the longevity of individual bees, and colony growth. You can find more details here

Consider the following difference equations used to describe the model.

Let’s assume that this model describes the change in the honey bee population with successive days {1, 2, … , t, t+1, t+2 etc.} indicated by the subscript. The parameter H represents the number of bees working in the hive and F represents the number of bees working outside the hive. Hence, the total number of bees is defined by N=H+F. The parameter L represents the queen bee’s egg laying while bees die at the rate m. The parameter w represent the rate at L(N/(w+N)) approaches L as N gets large. Note the initial state of bee population is represented at t=0.

Task 1: (1 mark)

Create an Excel sheet with the following at the top of the spreadsheet.

Here “Initial Population” corresponds to the initial population of both types of bees either working inside the hive or outside the hive i.e. H₀=1, F₀=1

Note the exact colour matching is not required but you should just demonstrate that you know how to use colours for highlighting cells of particular significance.

Note: After completing your assignment be sure to save your work with the above numbers entered into the spreadsheet.

Task 2: (5 marks)

Use Excel to solve Equation (1) and (2) for time (t) from 0 to 120 days. Remember the population of the bees cannot be in decimal numbers. So round off the bees’ population at each iteration. Use array formula to calculate N.

Please note your solution to Task 2 should be such that a user (or tutor) only has to change the parameters where they are defined and appear at the top of the spreadsheet (as in Figure 2), for all calculations throughout the spreadsheet to be updated.

Task 3: (4 marks)

Modify your spreadsheet so that for any day mentioned by subscript t, if H_t-1= H_t-2 and F_t-1= F_t-2 then H_t and F_t are assumed to reach saturation and is set equal to zero.

This ensures that if the bee population reaches saturation then data is not required to be repeated. The last two rows are expected to have identical populations.

Task 4: (5 marks)

On a single plot, graph H_t and F_t versus time for all days.

Remember the good graph rules from lectures in Week 1 and 2.

Task 5: (2 marks)

Automatically calculate the maximum total number of bees (N) and display or return the result in to cell H1.

Note: Your method should still work correctly if a user (tutor) changes the parameter values.

Task 6: (3 marks)

Automatically display or return to cell H2 the first day at which the saturation in bees population is reached (maximum N is reached).

Submit your excel file as a solution.