The photo that Engineer posted is accurate.
With respect to the powertain, Sport mode activation switches the transmission shift schedule to Sport mode and also changed the pedal mapping to Sport mode. So when you open the throttle beyond say 20%, the actual opening request is greater than 20%. If you have an Explorer with the Terrain Management System, it does the same thing as Sand mode and Sport mode (makes the throttle very sensitive). The Sport mode shift schedule sets the WOT shift points a bit higher or easier to reach based on throttle position and also keeps the RPMs a bit higher during downshifts. Sport mode also engages rev matched automatic downshifts - so as you are braking into a corner hot, the engine automatically rev matches your downshifts to keep the RPMs higher so you can punch out of the apex in the right powerband without having to shift around. I haven't found a way to access this portion of the tune - it must be in a reserved area. My SHO and Explorer don't have this function in Sport mode. Driver Demand, Spark, Fueling, etc... all remain the same whether you're in Drive or Sport mode - which means you don't get more power in Sport mode.
In the Explorer's Snow mode, the shift schedule is changed to upshift as early as possible and the throttle mapping is less sensitive. This is great if you're driving on a road in covered snow. If you're trying to launch in the snow, or merge onto a slushy road from a stop and need to change 3 or 4 lanes right away with slush in between the lanes, Sand mode actually comes in handy as it makes the throttle ultra sensitive, disables traction control, and uses essentially the Sport mode shift schedule. You can dig yourself out right away and let the AWD mechanically do the work. But Ford conveniently did not equip the Fusion Sport or SHO with the Terrain Management System - although Ford left the lines of code in the SHO if I recall correctly. Ford even used a Sport/Terrain for driver demand on the SHO - so when you shifted into Sport mode, it actually switched Driver demand tables so you could run two "tunes" in essence.
With my SHO, I ran a less-than-stock boost level tune for Drive to maximize fuel economy, and then ran the "just sending it" tune in Sport mode. This isn't directly available/possible for the Fusion Sport or my Explorer, but one of my current experiments is to tweak the pedal mapping so that in Drive, it doesn't use the full driver demand table. Then in Sport mode pedal mapping, it would access the full driver demand table for greater-than-stock boost
Just be glad we have a Sport mode, because the GM cars/SUVs with the same 6F55/8F55 equivalent transmission (6T70/6T80/9Txx) transmissions don't even have Sport mode. Instead they have manual mode which just lets you shift around with the button or paddles. There is a Performance Shift mode that in order to activate, you must drive aggressively, brake hard/enter a corner hot, and then it engages mysteriously. To me, the end result feels like Ford's sport mode where it keeps the RPMs high and the throttle is more sensitive. It sounds like I am making this up but that's what happens on the Malibu and Camaro (not sure if they changed this for later MYs or different trims).