Weight and Balance is off based on Fokker Documentation

I did some digging and found the Fokker training manual for the MK1000 and 2000. I noticed the Basic MAC appears to be too far fwd in MSFS. Here is the analysis
Figure 1 (Fokker F28 training manual)
The aircraft arm can be expressed in either percent mean aerodynamic chord (MAC) (as shown in the EFB) or the index value.The equation to compute MAC from Arm is
1) MAC(%) = (Arm(mm)  Leading edge MAC(mm)) * (ΔMAC(%) / ΔArm(mm))
conversely converting the MAC into arm is done by the following
2) Arm(mm) = (MAC(%) * ΔArm(mm)/ΔMAC(%)) + Leading Edge MAC(mm)
Finally, The index equation for the Fokker 28 MK1000&2000 is as follows
3) Index(units) = C  {[Weight(kg) * (A(mm)  Arm(mm))] / B} where A, B, and C are specific figures derived for the purposes of the calculation or from the airframe.
Figure 2 W&B Manifest
Here we see the values for A,B, & C are 200, 12200, and 115214 respectively. In this case the 200 and 115214 are notional figures for the purposes of aligning the chart as seen in figure 3Figure 3
The unsolved part for equations 1 and 2 is the location of the LE MAC. This is solved by Figure 1 wherein the LE MAC is identified at a location of +425.1" (10797.54mm) aft of the datum. since this would be the 0% location the ΔMAC(%) / ΔArm(mm) = 1% / 35.06mm or
4) ΔArm(mm) = ΔMAC(%) 12200mm  10797.54mm = 40% MAC  0% MAC 1402.26mm = 40%MAC 35.06mm = 1%MAC
Now according to Fokker the following OEW configurations are possible
Figure 4
Note that Version 1 is the most operationally pertinent. Solving equation 3 for Arm(mm) and substituting the appropriate values for A, B, & C we arrive at the conversion from Index to arm3) Index(units) = C  {[Weight(kg) * (A(mm)  Arm(mm))] / B} 5) Arm(mm) = 12,200(mm)  {[115214 * (200  Index)] / wt(kg)}
Substituting in the values found in Figure 4 version 1, the Arm is 12,094.0mm. Substituting the Arm(mm) to equation 1:
MAC(%) = (12094.0(mm)  10797.54(mm)) * (1%/35.06MM) = 36.97% MAC
This value can be independently validated on Figure 5 below.
Figure 5
Figure 6
Currently the OEW has the F28 with a MAC of 25% which is extremely fwd for this aircraft.
A question arises from this observation. Is this why the EFB limits the FWD cargo load to 567kg rather than the 1640kg total from areas 1 + 2 + 3?I will be continuing the investigation of the fuel arm next.

Based on a visual analysis of Figure 7 below
Figure 7
The following delta index table can be constructed
Pantry: 17.5/100kg
Baggage: 6.27/100kg
 4.77/100kg
 3.15/100kg
 +1.64/100kg
 +3.00/100kg
Passengers:
A) 5.23/100kg
B) 3.65/100kg
C) 1.23/100kg
D) +1.43/100kg
E) +3.77/100kg

The fuel moment can be evaluated to be a piecewise function:
https://www.desmos.com/calculator/yxtwsr6lt2
based on the fuel index chart above from the first post 
I created a weight and balance document that computes the index, ARM, and MAC for the aircraft. It appears the fuel moments and full passenger moments are off as well. Attached is the excel created from analyzing the weight and balance manifest. There is one correction to the post above, the fuel Index change is positive not negative.
Here is the excel weight and balance form, if you open it in google sheets or the graph wont work, open it in open office or excel for the functional graph: https://docs.google.com/spreadsheets/d/171R0fiC2tg4yrIjAwfMAbQ5XbPr4g1SW/edit?usp=drive_link&ouid=109230590357269413630&rtpof=true&sd=true
This is the graph in excel, the bottom dot is the OEW, the Middle dot is the ZFW, and the top dot is the straight line interpolation from ZFW to Ramp weight and the line between the ZFW and RAMP does not represent where the fuel burn cg would actually be.
EFB comparison at the same load

Updated Google Drive link for spreadsheet: https://docs.google.com/spreadsheets/d/1ea1243YmJdUMR05tJUXkQrdaCLyiDgur/edit?usp=drive_link&ouid=109230590357269413630&rtpof=true&sd=true

Turns out I misread the form. The fuel index is actually negative. I missed this arrow earlier. Additionally the Mk. 1000 spreadsheet has been fixed to correct some errors in the index values for the payload stations (this does not affect the analysis from above)

NOT my flight model, and I actually don't have this aircraft (different teams) so cannot make anything other than general observations. However:

You refer to the MAC as being 'too far forward'  Mean Aerodynamic Chord is a fixed figure based on the geometry of the wing. I will understand that you mean CG as a % of MAC.

The documentation you have presented is usually a starting point. Unfortunately, given the state of the MSFS core flight model that sort of data often ends up having to be adjusted to accommodate known flight behaviours. I have seen correct EWCG to the mm with correct load figures all forwards of this point and CPs correct leading to an aircraft sitting on its tail fully laden.

To feed into the above, MSFS does a lot of its own calculations. There is no way that they can be accurate for all aircraft, they are a generic calculation and the tools we used to use to balance that have been removed. So often, the 'primary points' such as the CG % MAC you mention may have to be adjusted (I have had an aircraft where I initially placed the weights at the correct position visually and they created an extreme behaviour. I then adjusted to the correct, calculated arm and the situation was worsened). Since much earlier versions of FS (I started dev work in FS9 and it was present then), the arms have created extreme effects. It has been particularly noticeable in the lateral sense, where having P1 and P2 weights in a twin seat sidebyside setup leaves the aircraft nicely balanced but remove P2 and there's a constant roll to the left . . . )

I have fought for precision and accuracy in flight models for a couple of decades now, and really appreciate your input but for the time being (i.e. until MSFS2024, where apparently we will be given more control of the flight model) we have to accept that the current game we are playing has weaknesses which cannot be overcome unless we aim for behaviours rather than numbers.
The game defaults to 25% MAC for the aero centre, I am not sure if it set up to allow this to be adjusted or if it is being dealt with by the game. That does make a difference and again, as I do not have this aircraft I cannot really comment in detail.
Again, your detail is great but may not account for problems caused by MSFS. I'll have to let one of the team that developed this one respond to that but I would not be at all surprised if it was just another occasion where the figures have had to be shifted because the game cannot cope with / deals incorrectly with accurate figures. It is a generic core flight model, and as such cannot be expected to deal accurately with every aircraft shape and when our primary tools to make precision adjustments (coefficients, tables) are removed we may have to shift some of the basics to get a reasonable facsimile.


@Delta558 Thanks for the reply. It looks like, based on the documentation that the center of pressure may actually be at 40% mac (12,200mm), this makes sense because the CG needs to be ahead of the center of pressure as the elevator applies an equal moment to hold up the cg moment. I haven't worked in the FSX/MSFS flight model for a couple years now but looking into the flight model I find the current equation:
from appendix A) https://www.fsdeveloper.com/forum/resources/flightdynamicsinmsfsv10.169/download
This seems to be at odds with your statement of a linear interpolation of between 2 fixed points. Was there a change in the SDK or other information you have that makes that equation invalid?This equation looks daunting but simplifies nicely into it being the sum of the reference_pos+empty_arm+station_arm(cm/cl)*MAC, all the weights cancel out in the denominator if the nominal station load is 0 which I am assuming it would be for an empty aircraft.
For the F28 I obviously dont have the Cm or Cl curves but I have all the other data
Reference datum = 0mm (nose)
Empty_weight_cg_pos = 12125.3mm
Empty weight = 16418kg
station load = 0
station arm = 0
cm/cl = linear interpolation of slope for Cl & Cm from 0°>10° AOA
MAC = 3506.15mm
so solving for CM/CL > 12,200mm = 0mm + 12,125.5mm (Cm/Cl) * 3506.15mm
(12,200mm  12,125mm) / 3506.15mm = Cm/ClSo that is an easy validation to see if you are in the ball park, You would need to account for the visual model offset in the ref_datum_pos. So provided you have accurate airfoil data (https://www.icas.org/ICAS_ARCHIVE/ICAS1988/ICAS881.6.2.pdf Page 11+) AND you have good location data then you should be able to set an aero moment of 40%. If you need a more recent and detailed analysis of the wing lift, drag, and pitch moments I can easily do that in about a day or two.
In the case of a 27411kg aircraft with a CG MAC of 28.0% (11779.3mm arm) that would make the CG moment 11,531.8kgm or 83,409.7lbft for which the hstab has to counteract. This would also align with the aft CG MAC being at 35% which is a very standard 5% buffer from the hypothetical center of pressure.

Thanks to my development colleague for his input, which in terms of MSFS I fully agree with.
I appreciate the trouble that the OP has gone to in walking through the realworld logic of this topic. However, trying to apply realworld physics and load diagrams to MSFS and its previous iterations is something of a 'devil's errand' since we are dealing with a flight dynamics system, the core of which  love it or hate it  is more than forty years old and was never designed to cope with the detailed development that all virtual aircraft designers have striven for in the last twenty years; certainly in my case, and like Delta558, I started developing flight dynamics many years ago, for FS8/FS2002. It's long been clear to FDE developers that trying to adhere to theoretical figures will only get us so far because at the end of the day, we are dealing with a performance specification (which is what the FDE is; it offers no active control during flight) which is then interpreted by the MSFS black box at run time using a generic set of calculations over which we have no control and to which we are hostage.
So, figures only provide a starting point and the reality is that when trying to incorporate as much of the figurework as possible early on in development, it soon becomes clear that what works in real life does not necessarily work in the simulator. This is where the practicality of how the aircraft actually flies influences how the flight model is developed. Yes, the figures (dimensions, weights, CG etc) are maintained in such a way that performance is optimised within those constraints, but it should always be remembered that the core flight model (and I emphasise again that although it's laid out differently to FSX and its predecessors, it is essentially more than four decades old) is an enormous simplification of aerodynamic principles for incorporation into what in the 1980s was referred to as a 'fiftydollar game'. There is so much simplification that the detail of what the OP describes in the calculations doesn't align with what's feasible in the performance specification/flight model. It's not only this kind of detail that is missing, and needs to be inserted somehow (which is where lateral creativity comes in), there are simple concepts such as zero fuel weight and engine bypass ratio that can't be directly specified. It goes way beyond this actually, because while in the real world, many parameters such as coefficient of lift, thrustspecfic fuel consumption and many others are continuously variable throughout the flight, in FS they are represented by a single value, such that they can only represent a single point within a specific phase of flight. There then needs to be an amount of educated manipulation during development to get the aircraft to do what it needs to in order to reflect realworld behaviour, which is after all what we're looking for. Even the basic parameters of weight have to be arranged in such a way that CG is realistic during operation.
In the real plane, the CG varies with the load and fuel status. The OP notes that according to their calculation, the empty weight CG is too far back. In the F28, this value is set at 25% MAC as far as the EFB is concerned since as far as my research indicates (and there was no testing pilot feedback indicating that it was incorrect), aircraft will give their best stability, performance and fuel economy with this value around 2526%. It is more or less central in the acceptable CG range for the F28, around 1834%, loaddependent  although again, this is represented in the flight model by a single range rather than varying depending on load. A figure of almost 37% would be outside the typical acceptable CG range, and like my colleague, I have seen fully loaded aircraft sitting on their tails in FS. An aircraft never actually flies in the empty condition as there is no fuel on board, so the loading of the aircraft has to be such that the CG is maintained within the acceptable envelope with the payload and fuel present. There are other limitations on loading in MSFS. In order to represent an aircraft like the F281000, there would ideally need to be 65 load stations for the passengers, one each, and multiple load stations for the cargo. Then, the EFB would need to evaluate the required number of passengers and cargo amount, and allocate the weights accordingly to give the best balance profile. The reality is that no developer does that (imagine 400+ load stations for a 747), and so in the F28 there are eight zones for passengers and two for cargo. The location of those zones is organised within the visual model in the best way to get optimal balance and CG. This gives a feel for the optimising that needs to go on.
Again I thank the OP for the input, but unless the flight dynamics engine of MSFS is radically upgraded and made more realistic regarding realworld physics (and I don't think MSFS 2024 will be it, since Microsoft has indicated that aircraft developed for it will need to be backwardscompatible with the current version) then we are where we are.

@Microlight Hey Thanks for your update. I respectfully disagree regarding the load station amount. Even a simplified model of the 10 load stations would be better than the current implementation. The aircraft weight and balance documents allow for zone loading with the included change in moment as seen in figure 7 in the analysis? Would 10 loading stations exceed the technical capabilities of calculating the CG location for the simulation?
Additionally the aircraft is fully certificated to fly at the boundaries of the CG envelope as a normal operation with no additional procedures or consideration. For example in the CRJ200 the aircraft is routinely operated at the maximum fwd cg, this is primarily a consequence of someone at bombardier thinking it was a good idea to certify the baggage hold to 3500lbs which necessitating taking the CL600 airframe and adding fuselage plugs infront of the wing. Sometimes the aircraft was only within FWD cg limit because through the application of ballast in the aft cargo bin, sometimes in excess of 1000lbs.
So I disagree that a) just because the operation is close to the limit that it isn't realistic and b) that the current system is the best possible system because otherwise you would need to account for every seat, that is a straw man fallacy. Is there a fundamental technical limitation the CG cannot be made accurate to align with the weight and balance of the aircraft?
Thanks,
CrashEdit: Additionally simply saying, "no we aren't planning on taking the time to update the code base" is also a justifiable reason. Cost benefit analysis are a driving force in decision making and planning.