You will see that some of the cells are colour yellow and others blue. The yellow ones are where you enter your data. The blue ones contain formulae and I have locked these so that you don't accidentally overtype them. They can be unlocked in the usual way if you like. You will see that lots of the yellow cells have data already added, that is sample data from Theodora's set up. You will also notice the little red triangle at the top right hand of some of the cells. I have added a comment to these cells. Hover the mouse pointer over the cell to read the comment.
The convention that I have used in labelling the quantities is a scientific one:
P/W means "power in Watts"
I/A means "current in Ampères"
Running/day (to replace daily consumption)/h means "Running per day to replace daily consumption; this is measured in hours."
Starting at the top of the sheet in B4 and B5 I have detailed the fridge and the freezer. The data here is the energy consumption in kWh/yr as detailed by the manufacturer. This is converted to Ah/day in F4 and F5.
I have dedicated block A8:F22 for the 12V appliances. Only the lights have a number and power attached to them because that the the data available. You will see that Theodora has 5 12V lights powered at 10W each. You can use the blank lines for your own appliances.
Block A24:F31 is for the 230V equipment. Most 230V appliances are given power ratings so that is what you input. The calculation allows for the inverter efficiency which is entered in H1.
The total charge used per day is found in F32.
The next area down the spreadsheet deals with generators. Dealing with the engine alternator and a portable generator is reasonably straightforward because you know how long they run for. There is no separate section for a portable generator but you will be able to incorporate this in with the engine alternator. Solar panels are less straightforward, and I have not dealt with wind turbines at all. I have made some assumptions based on research from the internet.
In detail: Enter the current that you expect to generate from the alternator. Actually take measurements if you can, with the engine running at cruising speed and with the batteries partially discharged. Don't use the nominal delivery current of the alternator these are stated for alternators on vehicles whizzing down the motorway.
The daily output from a solar PV panel will vary with weather and with how it is positioned on the roof. I have made the assumption that it is mounted flat. If you angle up according to your latitude and the time of year and make if follow the sun round with the passage of the hours then you will get more charge from it. Don't forget that it you shade any part of a solar panel it will drastically reduce the output of the whole panel. Sweep the leaves off it in the autumn!
Block G1:H3 contains some global inputs. The values that I have suggested can be altered to suit your own situation.
You should look up the efficiency of your inverter in the manual. Pure sine wave inverters are generally less efficient than quasi or modified sine wave versions.
The battery manufacturer may be able to come up with a value for the battery efficiency, by which I mean the ratio of the energy that you can get out of it to the energy that you put into it when charging. The minimum charge for the batteries is stated to be 50% because that it what has been mathematically found to be the most economical way to use batteries. Discharge them further and you can use fewer but you wear them out more quickly. The battery capacity as stated by the manufacturer will reduce as they wear. You will need to make a guess as to their actual capacity.
The Solar panel efficiencies should be viewed with caution. I have not done any measurements myself. The efficiency is the ratio of the nominal power to the output power that you might expect with the panels lying flat on the roof. The winter efficiency is calculated as one fifth of the summer owing to lower light levels. Summer and winter daylight hours will vary with your latitude.
All of this leads to a set of answers:
B39:B42 give the times that for which you will need to run your alternator (propulsion engine or petrol generator. It depends on your situation). The summer and winter rates will be different if you have solar panels. There has been no allowance made for the seasonal difference in, especially, the consumption of the fridge and freezer or on the use of the lights.
There are some further answers in the block A50:B52. The total storage required (B50) is used to calculate the number of batteries required to achieve this. The "batteries required" figure in B51 is a whole number and will take the fraction of a battery as one whole extra battery.
B52 and B53 give a figures for how long you could leave the boat with the engine off if only the fridge and freezer are left on. Two different answers for summer and winter if you have solar panels. It will show "Long Term" if the solar panels can supply all of the energy required. This is useful if you are cruising and are called away to a wedding. If the Batteries Installed cell (B49) is other than zero then this will be the number used for calculation. If B49 is zero or blank then the figure calculated in B51 will be used.
A word of caution. Do try to make actual measurements of current. To do this use a clamp meter. It will save having to disconnect appliances. If you have a sufficiently sensitive ammeter on your instrument panel you might be able to use that. There are so many unknowables in this that the spreadsheet can only give a rough idea but it will be a much better idea than just a guess!